Display Query Results Sensible (Filter in LstBox?)

[mactheknife]

Hi All,

Been scratching my head on this one, hopefully one of you smart chappies can help me.

Basically I have a database that inspects pieces of machinery. All of my results are collected in a table called, tbl_Inspections.

I have created a couple of queries that will:

a) Filter on Max Date (Show me only the latest inspection information for that specific piece of machinery.

b) Filter out only the latest records that show a problem on one of the fields (i.e. If "Drive Chain" = 3 (unsatisfactory),) OR (if "Drive Bearings" = 3)... etc etc.

So Now I have a query that displays only one (the latest) record for each specific machine, and another that takes those results and shows only still the records where atleast one field =3 (unsatisfactory).

Now what I am stuck on is how to sensibly display this data in my form? I would ideally like to have a list box with a list:

 [I][B]Machine No[/B]:| [B]Needing Replace[/B][/I]

 "Machine 1" | Drive Chain
"Machine 1" | Drive Bearings
"Machine 2" | Motor

Just now my query might look something like

Machine No: | Drive Chain | Drive Bearings | Motor | Guide Condition | Gearbox Condition | +15 other criteria

      1     |     1       |      2         |   2   |      3          |       1           |    +15 other criteria 
      2     |     2       |      3         |   1   |      3          |       2           |    +15 other criteria 
.
.
.

So Basically I just want to pick out and list only the variables for which the value = 3 and display these in a list.

If Anyone can help that would be greatly appreciated

 

[DavidAtWork]

This is not something you can do in a straightforward manner because basically you need to examine the latest record for each machine, this itself is easy enough using a GroupBy query on the Inspections table, group by MachineID using DMax on the date or DMax on the Inspections ID if this is an auto number, better on the Inspections ID if multiple inspections per day can be carried out on a machine.
If you have the InspectionsID, you can now query the other fields for problems, but to do what you're suggesting you'd have to inspect each value in each field and collect the details as a row source for your list.
The easiest way I can think of is to work through a recordset using a recordset method, appending records to a table that will be your recordsource/rowsource for your list

David