• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

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?
0
gigifarrow
Asked:
gigifarrow
2 Solutions
 
Helen FeddemaCommented:
Which table has the SerialNumber field?  How is SerialNumber related to MODKit (if at all)?
0
 
mbizupCommented:
Try this:


SELECT tblMODHistory.*
FROM tblMODHistory LEFT JOIN tblMODKits ON tblMODKits.[MOD Kit] = tblMODHistory.[MOD Kit]
WHERE tblMODKits.[MOD Kit] Is Null
ORDER BY tblMODHistory.[Serial Number];

Open in new window

0
 
gigifarrowAuthor Commented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gigifarrowAuthor Commented:
okay thanks for the code but is comparing what is not on the mod kit form I need it to do the opposite.
0
 
als315Commented:
@gigifarrow: you can save a lot of time if upload DB with these tables and some sample data.
0
 
awking00Commented:
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.
0
 
Jeffrey CoachmanCommented:
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.
0
 
gigifarrowAuthor Commented:
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
0
 
als315Commented:
I'm not sure what fields are interesting for you, but try this query (in uploaded example all MOD kits from history are in tblMODKits):
SELECT tblMODHistory.[Vehicle Serial Number], tblMODHistory.[MOD Kit], tblMODKits.[Part Number], tblMODHistory.[Part Number], tblMODKits.MWO, tblMODKits.[MOD Kit]
FROM tblMODHistory LEFT JOIN tblMODKits ON tblMODHistory.[MOD Kit] = tblMODKits.[MOD Kit]
WHERE (((tblMODKits.[Part Number]) Is Null Or (tblMODKits.[Part Number])="")) OR (((tblMODKits.MWO) Is Null Or (tblMODKits.MWO)="")) OR (((tblMODKits.[MOD Kit]) Is Null));

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now