2-field unique key

[dmeid]

I am trying to develop a unique key from a make-table query that selects records from user-specified date range. I am trying to extract the last record from the selected date range of meter readings. Some meters may have only 1 record while others may have many readings for the selected date range. I need to develop a unique key which willl concatenate the meter#(text field) and the reading date (Date/time field). With this concatenation I will be able to use the SELECT Max Statement. How do I concatenate a text field with a date/time field.

I keep getting unlike types. Was trying to change the date field to be text with an expression. I can not change the Master table date reading field to text do to many numerous queries already set up using this table.

Once I get this unique key set up, I will be setting up a calculation field called MaxDateField .

Hope this makes sense ! DM

 

[Jon K]

You can concatenate the fields using & :
[MeterNumber] & [ReadingDate]

However, if what you want is to retrieve, for each MeterNumber, the record with the maximum ReadingDate within a date range, you can do it with the following query instead of creating a unique key. Type/paste in the SQL View of a new query, replacing with the correct table name and field names:-

SELECT *
FROM TableName AS a
WHERE ReadingDate = (Select Max(ReadingDate) from TableName where MeterNumber = a.MeterNumber and ReadingDate <= [Enter cut-off date]);


When the query is run, the user is asked to enter a cut-off date.

Hope it helps.

 

[dmeid]

JON ..... THANK YOU SO VERY VERY MUCH ! CREATED THE QUERY LIKE YOU SAID AND IT WORKED OUT FANTASTICALLY ! THIS WILL SURELY SAVE US A LOT OF TIME.

THANKS AGAIN DM