gigifarrow
asked on
Comparing two different tables with a query and display what doesnt match based on serial number.
I have a table called tblMODKits it has a field called MODKit.
I have a table called tblMODHistory it also has a field called MODKit. They are bothed Joined by the field MODKit.
This query goes through all the records and shows what all the records dont have.
I need a query that is based on the serial number. And shows what each serial number doesn't have. This takes the group as a whole and shows what the whole table doesnt have .
When I put based on the serial number it shows nothing. I am thinking because one table the MODKits doesnt have a field called Serial Numbers.
SELECT tblMODKits.[MOD Kit], tblMODKits.MWO, tblMODKits.[Part Number]
FROM tblMODKits LEFT JOIN tblMODHistory ON tblMODKits.[MOD Kit] = tblMODHistory.[MOD Kit]
WHERE (((tblMODHistory.[MOD Kit]) Is Null));
How can I show what each mod kit based on the serial number doesnt have?
I have a table called tblMODHistory it also has a field called MODKit. They are bothed Joined by the field MODKit.
This query goes through all the records and shows what all the records dont have.
I need a query that is based on the serial number. And shows what each serial number doesn't have. This takes the group as a whole and shows what the whole table doesnt have .
When I put based on the serial number it shows nothing. I am thinking because one table the MODKits doesnt have a field called Serial Numbers.
SELECT tblMODKits.[MOD Kit], tblMODKits.MWO, tblMODKits.[Part Number]
FROM tblMODKits LEFT JOIN tblMODHistory ON tblMODKits.[MOD Kit] = tblMODHistory.[MOD Kit]
WHERE (((tblMODHistory.[MOD Kit]) Is Null));
How can I show what each mod kit based on the serial number doesnt have?
Helen Feddema
Which table has the SerialNumber field? How is SerialNumber related to MODKit (if at all)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for taking the time replying.
The table called MODHistoy has the serial Numbers in it. Every serial number references a vehicle and the mod kit shows what has been installed.
Based on what is in the MOD KITs table (this has all the mod kits that can be installed)
and Mod History shows what was installed.
I am trying to find out what hasnt been installed on each serial number based on what shows on the MOD KITs table.
The serial numbers are only located in the mod history table.
The table called MODHistoy has the serial Numbers in it. Every serial number references a vehicle and the mod kit shows what has been installed.
Based on what is in the MOD KITs table (this has all the mod kits that can be installed)
and Mod History shows what was installed.
I am trying to find out what hasnt been installed on each serial number based on what shows on the MOD KITs table.
The serial numbers are only located in the mod history table.
ASKER
okay thanks for the code but is comparing what is not on the mod kit form I need it to do the opposite.
@gigifarrow: you can save a lot of time if upload DB with these tables and some sample data.
Is there a serial_no in the history table for every modkit in the modkits table, though not necessarily for the same serial_no? Sample data and the expected output as als315 suggests would be of great help.
gigifarrow
You know the drill:
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.
You know the drill:
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.
ASKER
ok, your right I do know the drill.lol
Show what mod history doesnt have based on what mod kits are available
(Mod Kits what are available to install)
ACS-A3MOD Kit
ACS-ODS
AFES
AFES CEP Protective Switch Guard
AFES CEP Retrofit
AOA
BASS M2
BASS M3
BASS-D M2
BASS-D M3
BASS-D M7
BFCS
BFCS ONLY
BFIST Chiller
BFT M2/M3
MOD Kit
ACS-ODS
AFES
AFES CEP Protective Switch Guard
AFES CEP Retrofit
(MOD HISTORY what vehicles have been installed)
BASS M2
BASS M3
BASS-D M2
BASS-D M3
BASS-D M7
BFCS
BFCS ONLY
BFIST Chiller
BFT M2/M3
helopwithprodcutionquery.zip
Show what mod history doesnt have based on what mod kits are available
(Mod Kits what are available to install)
ACS-A3MOD Kit
ACS-ODS
AFES
AFES CEP Protective Switch Guard
AFES CEP Retrofit
AOA
BASS M2
BASS M3
BASS-D M2
BASS-D M3
BASS-D M7
BFCS
BFCS ONLY
BFIST Chiller
BFT M2/M3
MOD Kit
ACS-ODS
AFES
AFES CEP Protective Switch Guard
AFES CEP Retrofit
(MOD HISTORY what vehicles have been installed)
BASS M2
BASS M3
BASS-D M2
BASS-D M3
BASS-D M7
BFCS
BFCS ONLY
BFIST Chiller
BFT M2/M3
helopwithprodcutionquery.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.