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 ...
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] ...
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')
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'...
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 ...
You could rephrase it like this:
Return every row for which another row exists with the same Name and a different LastName
which is very easy to translate to SQL if you are familiar with the EXISTS predicate:
YourTable AS t1
YourTable AS t2
t2.Name = t1.Name
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 ...
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
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 = ...
(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 ...
There are some columns you don't want to pull through for each table
Column names are different for each table
You only have 40 tables
This is a one-time migration (this is safe to assume, correct?)
Then I would just copy and paste the core dataflow and make per-table changes as appropriate. For a non-repeating process that is a small amount of ...
If they have access to the tables directly with insert, update and/or delete rights then they can corrupt your data. If you prevent this by limiting the access-rights or by an MS-Access application then they can/should not be able to corrupt your data.
About business rules. According to Wikipedia: A business rule is a rule that defines or constrains some ...
You need UPDATE, not INSERT:
SET F = f / (SELECT SUM(f) AS fSum FROM MyTable) ;
MyTable AS my,
(SELECT SUM(f) AS fSum FROM MyTable) AS total
SET my.F = my.f / total.fSum ;
As mendosi commented, having two columns with same name in different (upper/lower) case, like f and F is not recommended in any DBMS and (not sure) ...
1.Open MS SQL Server Import and Export Wizard.
2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”.
3.Click “Properties” enter the location of the .accdb file in the “Data Source” field. That is the physical location of the Access database file, like C:\Temp.
4.Under login information, choose a blank username and be ...