Combine 2 tables, results from null values??? 1

[bddewell408]

I have been tasked with tracking firearm data, inspections, and repairs. I am 99% finished with this database except for the following report because I cannot figure out how to build a query. I need to combine the following information with a query to get the report I want:

Table - WeaponInformation
PrimaryKeyField - SerialNumber
Field 2 - WeaponType
Field 3 - Issuedto (socialsecuritynumber)
Field 4 - WeaponInService (yes/no field)


Table - AnnualInspection
PrimaryKeyField- AnnualInspectionID (autonumber)
Field 2 - SerialNumber (linked to above )
Field 3 - InspectionDone (yes/no field)
Field 4 - DateInspected

My Goal : is to have have three unbound text boxes on a form that will allow the user to select the type of weapon from Table WeaponInformation (ie, pistol, shotgun, rifle, etc) and then a begin and end date range to query everyone that has already gotten his/her weapon inspected. But the results I want are those that have not gotten their weapon inspected.

Unfortunately, I am not the weapon inspector, because I know that could cheat each year and just copy all of the active weapons in service, paste them in the annualinspection table and then query the serialnumbers by date that are marked no. I am trying to make this as automatic and not need anymaintenance,

Thanks for any help
Bryan

 

[glalsop]

This is a basic select statement that would return all serial numbers from the WeaponInformation table that DO NOT have records in the AnnualInspection table. Just add the dates and type information to the WHERE clause and I think this is what you need.

SELECT WeaponInformation.SerialNumber, AnnualInspection.AnnualInspectionID
FROM WeaponInformation LEFT JOIN AnnualInspection ON WeaponInformation.SerialNumber = AnnualInspection.SerialNumber
WHERE (((AnnualInspection.AnnualInspectionID) Is Null));


-Gary

 

[bddewell408]

Glalsop, thank you very very much, that did most of what I wanted.

I am a very inexperienced user, and have never written SQL, so I copied what you did and it worked. Maybe you could answer the following question for me as well:

Some weapons are going to be very old, and every weapon needs to be inspected every year, so, based on the statement above, as long as the weapon is inspected once, it will not show up in the query you wrote for me. What I would like to be able to do is reference the below unbound text boxes which are on the Form- "Main Menu" (I know there should be no spaces in the name, but I did it without realizing and then I had based a lot things on the form before I realized I had done that.)

InspectionBeginDate (sample data 01/01/2003)
InspectionEndDate (sample data 01/01/2004)

and query all weapons that have not been inspected between the two periods of time.

I also have a third unbound combo box (WeaponTypeInspectionLookup) which will allow me to select the "type" of weapon, from the WeaponInformation tabble, so I can query uninspected weapons by type as well.

Thanks again for the response

 

[glalsop]

To take the dates into consideration it would probably be best too create 2 queries.

The first one will give us all of the inspections that meet the criteria on your form, i.e. all inspections for type 'A' between Start Date and End Date:

SELECT AnnualInspection.AnnualInspectionID, AnnualInspection.SerialNumber, AnnualInspection.DateInspected, WeaponInformation.WeaponType
FROM WeaponInformation INNER JOIN AnnualInspection ON WeaponInformation.SerialNumber = AnnualInspection.SerialNumber
WHERE (((AnnualInspection.DateInspected) Between [forms]![Main Menu]![InspectionBeginDate] And [forms]![Main Menu]![InspectionEndDate]) AND ((WeaponInformation.WeaponType)=[forms]![Main Menu]![WeaponTypeInspectionLookup]));

So we need to create that query and call it qsubInspections. But that gives us the opposite of what we want. So then we need to create a second query that lists everone not included in qsubInspections:

SELECT WeaponInformation.SerialNumber, qsubInspections.AnnualInspectionID
FROM WeaponInformation LEFT JOIN qsubInspections ON WeaponInformation.SerialNumber = qsubInspections.SerialNumber
WHERE (((qsubInspections.AnnualInspectionID) Is Null));

I think that the second query gives us the results that we want.

Good luck.


-Gary

 

[bddewell408]

That almost got it done. I deleted the portion about TypeofWeapon. I also messed up with the field names, it was not "WeaponType" rather "TypeofWeapon" and it was not "DateInspected" rather "DatePerformed." It was not querying just "Pistol", it was querying all pistols that had not been inspected between the date range, and then all other weapon types whether they had been inspected or not.

I modified the statement and got rid of the TypeofWeapon lookup, and now it is doing what it is supposed to do, returning all weapons that have not been inspected during the period of time specified in the unbound date begin and end boxes.

I guess I can create a third simple select query that will pull weapon types reference the WeapontypeLookup combo box from the Main Menu for individual weapon type inspection reports, and then create a master report with all weapon types grouped by weapon type from the two queries you wrote for me.

I really cannot thank you enough. THANK YOU, THANK YOU, THANK YOU!!! I really need to learn to write Code, although it is not exactly a requirement of being a police officer VBG Who knows, maybe if I create enough databases I will eventually get sergeant stripes??