Running a parameter query across multiple tables in an non-normalized database can cause problems, particularly when you are searching for records that must meet more than one criterion. Unless those criteria are met for each and every table, no records will return, even if the criteria are met for four out of five tables.
The solution is to to use nested queries linked with Left Outer Joins.
1. Create a new query (Query A) and Left Join Table 1 to Table 2 on the common field(s). Set the parameters for the query on the Left Table (Table 1), and run the query to check it.
2. Create a second new query (Query B), nesting all the data from Query A and left joining the common field from Query A to Table 3. It is important here to note that this join and all subsequent joins must be made on the common field that came from Table 1.
3. Using these steps (creating a new query, nesting the previous query, and linking it to the next Table), you will eventually have nested all the tables. Because you have used a Left Outer Join (selection 2 under “Join Type”), records will be returned, regardless of whether or not such information exists in all fields.
My specific problem was a little more complicated.
My database had seven related tables: Probate, Property, Births, Baptisms, Marriages, Deaths, and Member Roster. My job was to create a parameter query that would take the names from Member Roster and search for that name in all the other tables.
The tables in the database were not normalized. In each of the tables, there were fields for name and parents’ names. In order to search for a person from Member Roster, a separate query must search for records where $Member is Birth Subject, Birth Father, Baptism Subject, Baptism Father, Groom, Father of the Groom, and Father of the Bride.
If you wish to query the same table multiple times, you have to nest queries differently than you would if you only were searching each table once. If you set up a single nested query to find for each of the above fields, the second and all subsequent searches of, for instance, the Birth table will not return data if it is not the very last part of the entire nested query.
Therefore, separate “chains” of queries must be set up using the process outlined in the General Solution section. The parameters must be set on the first query of each chain, in my case, [Last Name?] and [First Name?]. Chain 1 queried Member Roster, Probate, Property, Death, Birth Subject, Baptism Subject, and Groom. Chain 2 queried Member Roster, Birth Father, Baptism Father, and Father of the Groom. Chain 3 queried Member Roster, and Father of the Bride. Finally, one final query took the Chains 1, 2, and 3 and combined them.
If you get this error:
Microsoft Access: You don't have the necessary permissions to use the 'DatabasePath\DatabaseName.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.
Go here http://www.prd-software.com.au/prd/support/kb/kbarticle.asp?id=90
OR
Firstly ensure that you have adequate permissions to both the database file and the directory it is located in.
The database file used our database is a secured Microsoft Access 97 database (Note that Microsoft Access 2000 can still be used to open the database and offers an excellent compact and repair feature). Registered users of the database are entitled to view the database; evaluation users are not. In order to access the database, you will need to connect to the “system.mdw” security file that is in the same directory as the database file.
When opening a Microsoft Access database (or any office suite programs) file from shared drives you get the Windows warning:
You need to add the shared drives FQDN (Fully Qualified Domain Name) to your Internet settings ‘Trusted Sites’ list
When opening a Microsoft Access database file from shared drives you get the warning:
Open the database in Microsoft Access. Goto the menu Tools>Macro>Security.... Change the security level to Low, or figure out how trusted publishers works. I just turned it to low, and there are no more annoyances.
When opening a Microsoft Access database file you get the warning:
You need to join the machine to the workgroup for that particular file. Workgroup settings are held in the System.mdw files. There is one local system.mdw and also sometimes each database has its own System.mdw file in the same directory as the actual database file.
Open Microsoft Access without any database open. Goto the menu Tools>Security>Workgroup Administrator.... Click Join. Browse to the databases own System.mdw file. Hit OK. You should receive a pop-up saying that you successfully joined the workgroup. Now you can go open the database file.
A small pop-up window that says “Disk or Network Error” “Ok” “Help”, comes up randomly while using or even just letting the progressions database sit in the background. If the database and errors have been left to sit, many of the above windows may exist.
This microsoft KB article explains something similar to the above symptoms: http://support.microsoft.com/kb/251254
The stopgap solution is: