×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Currently I have a form that record

Currently I have a form that record

Currently I have a form that record

(OP)
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?

RE: Currently I have a form that record

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

RE: Currently I have a form that record

(OP)
yes, the violation table houses all 58 violations

RE: Currently I have a form that record

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

RE: Currently I have a form that record

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

RE: Currently I have a form that record

(OP)
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

RE: Currently I have a form that record

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

RE: Currently I have a form that record

> INNER JOIN

Start by making this a LEFT JOIN ...

RE: Currently I have a form that record

(OP)
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)

RE: Currently I have a form that record

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

RE: Currently I have a form that record

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close