Problem selecting records

[KCA]

I have a small problem, probably the solution will again be simple, but I can't get it sorted.

I have created a table with some 20 fields in it, of which 1 is called maintenance type, a combo box and a second field being the date a specific type type has been performed. This results in a bunch of records, let's say 4 dates with type A, 5 dates with type B and 3 dates with type C.

I've created a query which selects the most recent records for each maintenance type. So far, so good, but now I have created a new form, a summary screen, where I want 3 fields, showing in the first field the date last A maintenance performed, field 2 showing last date B maintenance performed, and field 3 showing last date C maintenance performed. As new records are entered in the main table, these fields value will automatically change to the most recent one. It has to be a form, since this one is also the main form when opening the database and more functions are stored here.

I tried all kinds of things, created separate queries for the 3 types each with its date, but then I can only get 1 field working, corresponding with the query I base the form on. When I make an expression for the other field to get the data from the other query, it just shows #NAME?.

Thanks in advance for any help
Koen

 

[jeremie_ingram]

If I understand this properly, you just want to show a display of the last dates entered into the table. If my assumption is correct, then you could enter "=DMax([tblName]![FieldName]" into a text box on the form. The DMax functiuon should return the greatest value in a field of a table, which would be the most recent date. If this is for display only, then I would go into the properties of the text box and format it to look like a lebel and change its properties so that the useres cannot manipulate the displayed data.

 

[KevinM]

Create a Totals Query.
Include the Type field and Date Performed Field

Group on Max on the Date field.

To show these results as columns, you will need to create a crosstab query of the above totals query. You may even be able to do it all in one crosstab query.

Such queries should only be used in datasheet view where forms are concerned as the column names will be dynamic as data changes.