Compare actual records to expected

[Forrest]

Not even sure where to start so if this isn't a query question, please point me in the right direction.

I work in a plant that has a number of cranes. Each crane should be inspected each shift (three shifts per day), each day (less Sunday).

I have exactly what I need to capture the inspections being done. Inspections are identified by crane #, shift, and date.

How do I determine if an inspection hasn't been done? How do I compare the inspections that have been entered against the standard of inspections for each crane, each shift, each day?

I am below average with Access on my best day so please type slowly and be specific. Thanks for any help you can give.

Forrest

 

[CraigDolphin]

FYI, it would be advantageous if you could provide a diagram showing your tables and relationships so we can better assist you.

I imagine that you have a table called 'Inspections' that looks something like:

InspectionID (auto, pk)
CraneID (Long Int, FK)
ShiftNumber (Long Int?)
InspectionDate (date/time)

In this scheme, shift number values could be 1 or 2 or 3.

How do I determine if an inspection hasn't been done?

There's a number of ways. Which one is best really depends on how/where you plan to use the information.

One way would be to write a public function that returns 'ok' if all inspections for a crane were done on a particular date, or 'Stuffed It Up' if an inspection was missing

Public Function CheckInspection(MyCrane as Integer, MyDate as Date) as String
Dim i as integer
Dim badflag as boolean
i = 1
For i = 1 to 3
   If Isnull(Dlookup("[InspectionID]","[Inspections]","[CraneID]=" & MyCrane & " AND [InspectionDate]=#" & MyDate & "# AND [ShiftNumber]=" & i)) then badflag = true
Next i
If badflag = true then 
   CheckInspection = "Stuffed It Up!"
Else
   CheckInspection = "OK"
End If
End Function

Then you could call this function, providing it with the CraneID and Date you want to test, and see the result. For example, in a query, you could Call it as a field.

The recordsource for the query would have to contain a CraneID field and another field with a list of dates you want to check.

InspectionsDone: CheckInspection([CraneID],[FieldWithDatestoCheck])

Or you could rewrite the function to test a range of dates instead of a single date like this one does.

Or you could create a parameter query that limits the results to one crane and one or more dates, then use dcount to count how many records are returned by the query and use math to test the result against however many records you would expect to see. Of course, you should create your query to exclude any 'double-up' inspection that were done in the same shift.

How do I compare the inspections that have been entered against the standard of inspections for each crane, each shift, each day?

I don't know. You haven't told us anything at all about this topic. Again, there's a lot of ways to compare x versus y, but since we don't know what either x or y is, it is hard to comment.

What information about the inspection 'result' do you store?
What is a 'standard of inspection' and how does the db know about this? (ie, do you have the information stored in a table or what?)

 

[Forrest]

CraigDolphin,
Thanks so much for the quick response and sorry I wasn't more specific but now that I know I have a guru helping...

Attached are snapshots of my DB, the form where crane inspection results are entered, the table, and a second form for recalling the results.

What I would like to do is to add a couple of check boxes to the second form that say something like "Check here to see inspections completed" (this part already works) and "Check here to see inspections not completed" (this is where you come in). The query will take the shift (and I'd like the option of telling it 'all shifts') and either today or a range of dates from the form and tell me what cranes have not been inspected, what shift, what day.

I hope I've given you enough info. What do you recommend as the easiest way to handle this?

Thanks a ton for the help.
Forrest

 

[CraigDolphin]

The easiest way would be to hire someone to do it for you

Short of that, there's a couple of approaches you could take and it really depends on how familiar you are with vba as to which I would recommend for you.

Essentially the problem boils down to this: there are up to four different queries that might need to respond to the search options your user might choose.
1. All shifts - one date (Select ... Where Date=Whatever)
2.one shift-one date (Select...Where Shift=<whatever> AND Date = <whatever>)
3.All shifts - range of dates (Select...WHERE Date Between StartDate and EndDate)
4.One shift-range of dates (Select...WHERE Shift = <whatever> AND Date Between StartDate and Enddate)

Also, consider whether you might want to allow the user to select 2 out of the three available shifts?
There's another 2 queries to add if so:
5. 2 shifts, OneDate (Select...WHERE Shift In (<whatever>,<another>) and Date =<whatever>)
6. 2 shifts, Range of dates (Select...WHERE Shift In (<whatever>,<another>) and Date Between Startdate AND Enddate)

As you can see....the where clause of the query changes radically depending on exactly what you want to see.

You could handle this situation in two ways:
___________________________________________________
A. Build a saved query for each possible permutation that you want to allow for and then use vba in the on_click event of your 'Get Results' button to choose which query/report to open.

___________________________________________________

B. You can build a SQL query dynamically using vba in the on_click event of the 'Get Results' button. Then you open this single query or use it as the basis for the report that the user sees. If you go down this path you will need to learn about concatenation of strings (especially note the difference in using the '&' versus using the '+' operators when adding items where one side might be a null). You'll also need to learn about using querydefs.

Also, if you plan to allow the user to choose 'All' shifts, or any combination of shifts that they like, you'll need to learn about using unbound multiselect listboxes. There are sample databases on this forum that illustrate how to add an 'All' option to the list of available shifts. If you search, there are also samples of code showing how to use the multiselect
listbox to build a dynamic query based on which items are selected. Post 2 in this thread shows some code that does exactly that (Remembering the your query would be a Select query, not an Append query as shown in that example). You would need to modify this code to deal with your situation but this shows the general principles that you will need to use. (There's also a sample db here that produces a similar result by building a where clause to use as an argument in the docmd.openreport command).
_________________________________________________

Although you may be tempted to take the first option since it probably looks simpler consider how your db is going to start getting cluttered up with all the queries and reports that you'll need to make. If you ever need to make changes to the output, you're going to have to make those changes in every variation on the queries. The alternative requires changes in just one place. You also won't learn as much

I've bolded a few of the key words you should search for to learn more about various topics that are relevant to your problem.

Why don't you do some searching/reading and decide what approach you want to take. Then post back with your best effort. If you have trouble sorting it all out, then post a zipped copy of your db (strip out any sensitive data but leave enough to work with (or some dummy data) for testing purposes). I'd be happy to have a look and help you in the right direction.

 

[CraigDolphin]

I also should mention that your table is not properly normalized. Tables should not contain repeating groups within columns like your does.

For example, for People, you should have a People table that has an autonumber primary key field, and other fields to capture first name, last name etc. Then you store the pk value as a foreign key field in your inspections table, not the name itself. This vastly reduces the amount of space that piece of information takes up in your db.

The same goes for the shift, crane, and team fields.

Remember that Access has a maximum theoretical filesize of around 2Gb but in reality the operational limit is much lower. When you've used up all the space, your db won't work any more without converting it to a different db program like SQL server, mysql, oracle etc. Access can store a heck of a lot of data, but you should be smart about your table design to minimize wasteful use of space. Read up on table normaliztion to learn more about the correct way to design tables in a relational database like Access.

 

[Forrest]

CD, Sorry for the late reply. Crazy week, but here they are all crazy. I did normalize the table. Thanks for the reminder. It has been about a million years since my DB class. I haven't gotten to the other stuff yet but will try to make time this week.

Thanks for all your help.
Forrest