I must first compliment you on your courage to do something like this with an Access DB, which from my experience is very difficult to do anything SQL-like. Anyways, on to the review.
Your IIF field selections might benefit from using a Switch statement instead. It seems to be sometimes the case, especially with things SQL, that a SWITCH (more ...
Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free:
alter database [...] set read_committed_snapshot on;
No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of ...
Instead of rewriting the whole query, I'll give you some hints of how to do it:
IIf(condition, a, b) becomes: CASE WHEN condition THEN a ELSE b END
IsNull(x) becomes: x IS NULL
The combined IIf(IsNull(x), y, x)) can then be written as:
CASE WHEN x IS NULL THEN y ELSE x END and simplified further to COALESCE(x, y).
The table-column expressions: [tab]![col] ...
You mentioned MS Excel in your comment so it's pretty much safe to assume you're in a Microsoft environment. You definitely have much power if you know how to mess with a database management system.
If you're doing some serious data analysis, I'd say go for enterprise databases like Oracle, SQL Server, MySQL, DB2, etc., which are Relational ...
MS-Access is rather picky at how it wants the joins to be formed. Add parentheses:
SELECT A.Name, B.Quantity, C.Quantity
( A LEFT OUTER JOIN B ON A.ID = B.ID )
LEFT OUTER JOIN C ON B.No = C.No ;
Standard SQL syntax - and most other DBMS - do not need require parentheses in the FROM clause, although you are allowed to use them for clarity.
From Nz Function in the documentation:
You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.
Or you can use:
MS Access is rather limited.
I assume that it is possible to have more than one invoice for the same date.
In this case I'll pick an invoice with the highest ID.
At first we'll find maximum Invoice Date for each Food Item.
FPD1.[Food item ID] AS ItemID
,MAX(I1.[Invoice Date]) AS MaxDate
[Food purchase data] AS FPD1
INNER JOIN ...
As mentioned in the comments, Oracle doesn't like the square brackets around identifiers, and it also doesn't support TOP n clauses, however, you can work around that with a couple of simple changes:
, row_number() over (ORDER BY ...
What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the columns and tables, "Table1", "Table2", "Field1" say nothing about the table or the column and are very confusing).
I see two options depending on what your ...
Yes you can use MS Access as a front end to SQL Server. Its quite common. I have done this myself in the past.
No, Access itself won't corrupt data. However your Access application needs to be designed so that it respects SQL Server constraints and rules. Also how will your Application stop users from making simple mistakes, like deleting records if they'...
In your query left join is performed only using only the condition a.ID=b.ID and then results are filtered based on condition b.lang='de'. You need to add both conditions on the left join:
SELECT a.ID, a.job, b.job
FROM a LEFT JOIN b
ON (a.ID=b.ID and b.lang='de')
A foreign key relates to the primary key of another table. When the record with the foreign is entered, the corresponding record with that primary key must be present. Therefore you must always start by inserting in the tables on the 1-side of the relation (the master or parent or primary key table). Then only you can enter a record on the n-side of the ...
You need to use an outer join, otherwise you won't get those students back that did not borrow a book.
Then you need to count() on the students table, not "the group" (which is done when you use (*))
Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it. The two fundamental benefits to normalization are:
The simple approach to normalization is to create a table for each person, place, thing, concept, or event. By doing this, you have each characteristic ...
Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you:
Sample tables and data
CREATE TABLE SupplierCountry
SupplierName varchar(50) NOT NULL,
CountryName varchar(50) NOT NULL
('Supplier A', 'USA'),
('Supplier A', '...
Your query is using the pre-ANSI (and possibly original) JOIN syntax. Back in "the day", joining tables was done by specifying all of the tables, separated by commas, in the FROM clause, and then specifying the join condition(s) in the WHERE clause. Equating the current ANSI style with the old style gives you the following chart of options:
[INNER] JOIN: ...
The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets.
User-Actions taken during a given Period ( defined by the composite key of Year-Week )
User-Actions taken during an unknown Period
My initial impression is that the unknown period must ...
I'll just summarize all my comments here as an answer.
You should read this:
The SQL Server Service is the SQL Server engine and runs under an account specified for the service and linked servers which use a file share will necessarily use those permissions, since ...
You can either build your own monitoring tool or look to a 3rd party solution that can provide one for you. If you're interested in building your own, it depends on what version of SQL Server you're working with. If it's 2005, you can use the Blocked Process Report trace event. If you're running 2008 or above, I'd suggest using the equivalent extended event, ...
First you will have to use query language.
All the flavors of SQL use the almost same SQL query syntax. So you will first have to learn SQL query language. For eg. you can start with tutorial at www.w3schools.com/sql/default.asp
Stanford University had offered public course on databases last Fall. They had nice tutorial on how to set up SQLite.
Once you ...
You have a working query, but you are selecting:
FROM CF30, EC01, OC02, OM01U1, RS2101F
With no explicit joins and only one implicit join:
WHERE OM01U1.OM01015 = RS2101F.OUTNUM
This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say:
INNER JOIN RS2101F
ON OM01U1.OM01015 = ...
When you have an aggregate function, you need a GROUP BY statement. In your case, it would be
INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
After translation of table names i think that this will work:
LEFT OUTER JOIN UITLENINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
UITLENINGEN.LLNR IS NULL;
You are looking for LEERLINGEN ...
(Borrowing from my earlier answer here...)
re: actually performing the backup
Backing up a native Access database is simply a matter of copying the entire database file (.mdb for Access_2003 and earlier, .accdb for Access_2007 and later). Any scripting language would work, even a simple Windows batch file that does something like
copy /Y d:\apps\databases\...
You can write a sub to backup the database to a specified folder and run this sub when the database is launched. There are a lot of ways to do this, but I personally use the following:
Function fMakeBackup() As Boolean
Dim Source As String
Dim Target As String
Dim retval As Integer
Source = CurrentDb.Name
Target = "Z:\My Apps\Backups\...
The only issue here is that MS Access has no idea what a CASE expression is. Instead, you need to use IIF, e.g.:
IIF(Credit-Debit > 0, Credit-Debit, NULL) AS Credit,
IIF(Credit-Debit < 0, Credit-Debit, NULL) AS Debit,
BTW IIF is now supported in SQL Server 2012.
This sounds like an enormous project.
My advice is to go back and look again for an open source, off-the-shelf product that meets your main needs.
It is difficult to believe that your school is alone in its needs and that no-one else has similar requirements.
Even if you the system does not do everything you require, as long as it is built in an open way, ...
If the SQL server table is modified will it be automatically reflected in the [linked] table and when? Yes, on next refresh or requery.
If I edit the linked Access table will it be reflected in the SQL server table?
Yes; as before, these updates will be visible on the other end on next refresh or requery.
Trusted Connection only controls how the connection ...
Added additional attributes and filter conditions.
Any form of cross join is eliminated by using min and max nested queries. This is the biggest performance gain.
The min and max flank values returned by the inner most nested query are primary key values (scans) that are used to retrieve additional flank attributes (lat and lon) using a seek for final ...