Currently I have a form that record 1

[monkeysee]

Currently I have a form that records outcomes from a restaurant inspection. A list of violations is used that reside in a table. Obviously, most restaurants do not have a lot of issues and only those that are found are recorded in the 'Inspection' However, even though only a couple violations have been recorded; The Inspector must record that he addressed every single issue that the State requires which there are 58. He addressed them as 'In' Compliance or 'out' of compliance.

To save the Inspector time, I made the default of the compliance as 'In'. So the Inspector only needs to record those that are 'out'.
This is were the 'snag' comes in. A report must be filed that shows the "in's and all the 'out's. When running the report, it will only show the 'out's since these are the only ones recorded in DB

Two tables:
1) Inspection with fields: InspectionID; FacilityID; inspDaate; InspectorID;
2) Violation with fields; ViolationID; Description; Compliance(in/out) InspectionID
(These are just the main fields in the tables)

The table data is displayed in a subform on the InspectionForm

query for the subform: SELECT InspectionDetails.InspectionID, InspectionDetails.ViolationID, Violation.[Violation#], Violation.ComplianceFood, Violation.Description, Violation.Inactive
FROM Violation INNER JOIN InspectionDetails ON Violation.ViolationID = InspectionDetails.ViolationID
WHERE (((Violation.Inactive)=No));


Is it possible to have all the 58 items display on the report, without having the Inspector enter the compliance on each violation?

 

[dhookom]

Do you have a table with 58 records, one for each single issue? I would think this would be a must in your application.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

yes, the violation table houses all 58 violations

 

[monkeysee]

Are you suggesting that each of the 58 violation have individual tables?

 

[dhookom]

It looks like your Violation table has inspection ID and other fields. I would expect to see a table with 58 records, one record for each type of violation. There would be a primary key field the violation title. There might also be a bit of a description as well as a "report me in this order field" and a status field.

Your violation table looks like a junction table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

I think I am trying to over think this.... but here is a sample of what my table looks like:

ViolationID: Violation#: Description: Compliance: Wt
151 21 Wiping cloths; clean, use restricted 1 1
158 28C Sewage and wastewater disposal 1 4
145 15 Non-Food contact surfaces: designed, constructed, maintained, installed, located 1 1
146 16 Dishwashing facilities: designed, constructed, maintained, installed, located, operated 1 2
147 17 Accurate thermometers, chemical test kits provided, gauge cock 1 1
148 18 Pre-flushed scraped, soaked 1 1
143 13 Clean clothes, hair restraints 1 1
150 20C Sanitization rinse; clean, temperature, concentration 1 4
142 12C Hands washed and cleaned, good hygenic practices 1 5
152 22 Food-contact surfaces of equipment and utensils clean, free of abrasives and detergents 1 2
153 23 NON FOOD CONTACT SURFACES OF EQUIPMENT AND UTENSILS CLEAN 1 1
154 24 Storage, handling of clean equipment-utensils 1 1
155 25 Single-service articles, storage, dispensing 1 1
156 26 No re-use of single-service articles 1 2
157 27C Water source, safe: hot and cold under pressure 1 5
149 19 Wash, rinse water, clean proper temperature 1 2
133 3C Potentially hazardous food meets temperature requirements during storage, preparation, display, service and transportation; date-marking 1 5

 

[dhookom]

Please take the time to figure out and use TGML.

What is the Compliance field? Do you have a table with 58 records?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

> INNER JOIN

Start by making this a LEFT JOIN ...

 

[monkeysee]

dhookom

I'm sorry, but what is TGML?

Yes, I have a table with all 58 records
The compliance field is the lookup field that inspector will choose for each violation to show if the facility is "in" or "out" of compliance (in & out are the choices for the lookup field)

 

[monkeysee]

never mind, figured out what is TGML - a sort of html language

 

[dhookom]

If you create and save a query with the primary keys of the "
table with all 58 records" and the Inspection table you can use this in an outer join query with the violations table.

If you can't figure this out, please reply with all significant tables and fields with relationships.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016