how do a retrieve only select records? 2

[kevin531]

i have a table that has the fields miles, date of maintenance, and unit number. there are multiple entries for each unit number. when i view a report i only want the report to display the most current date as well as the miles entered on that date for each unit number.

i don't want all the records of maintenance to display. is there a way to link the miles to the date that they were entered? any help would be appreciated. thanks.

 

[AutumnBlues]

Paste this into the SQL window and save it. Then base your report on the new query. (You'll need to change YourTable to your table, and UnitNumber, Date, Miles to match your fields)
-----------
select UnitNumber, date, Miles
from (select UnitNumber as TopUnit, Max(date) as TopDate from YourTable group by UnitNumber) AS Tops
inner join YourTable on (Tops.TopDate = YourTable.date) and(Tops.TopUnit = Table1.TopUnit);
-----------

AutumnBlues

 

[kevin531]

is there an easyier way to do this, say in design view? thanks.

 

[AutumnBlues]

It's not as difficult as it might look. All you need to do is start a new query as normal, but don't add any tables in design view. Move across to the SQL window and then paste in the above SQL statement.

Where it says 'YourTable' in the SQL, type in the name of your table (if your table name has a space, you'll need to put it into square brackets). Then, where the SQL statement says 'UnitNumber', change it to the field name you have (again, if your field name has spaces, you'll need to enclose it in square brackets). Do the same for the other two field names, then click back into design view and you'll see the query in the usual QBE format.

Give it a try.
AutumnBlues

 

[kevin531]

Okay, this is what i have. i get a syntax error in the JOIN expression. when i select "OK" it highlights "Table1" in the JOIN expression. what does this mean and how can i fix it?
Also, where it says "TopDate", do i have to replace the "date" part of that with my field name?

select [UNIT NUMBER], [DATE OF MAINTENANCE], [HOURS]
from (select [UNIT NUMBER] as TopUnit, Max([DATE OF MAINTENANCE]) as TopDate from [LIFT TRUCK MAINTENANCE] group by [UNIT NUMBER]) AS Tops
inner join [LIFT TRUCK MAINTENANCE] on (Tops.TopDate = [LIFT TRUCK MAINTENANCE].[DATE OF MAINTENANCE]) and(Tops.TopUnit = Table1.TopUnit);
thank you.

 

[AutumnBlues]

Sorry, I screwed up.

Try this instead...
---------------
select [UNIT NUMBER], [DATE OF MAINTENANCE], [HOURS]
from (select [UNIT NUMBER] as TopUnit, Max([DATE OF MAINTENANCE]) as TopDate from [LIFT TRUCK MAINTENANCE] group by [UNIT NUMBER]) AS Tops
inner join [LIFT TRUCK MAINTENANCE] on (Tops.TopDate = [LIFT TRUCK MAINTENANCE].[DATE OF MAINTENANCE]) and(Tops.TopUnit = [LIFT TRUCK MAINTENANCE].[UNIT NUMBER]);
----------------

Sorry Again
AutumnBlues

 

[kevin531]

Thanks, it works.
-Kevin531

 

[kevin531]

i have another question for you. i have the same problem with two other tables. i tried to copy and paste, then change the appropriate fields but that didn't work. i don't know why because the tables are identical except for the field names and table names, which i changed. i get an error when i try to run the query which says, "the specified field '[AUTO MAINTENANCE].[UNIT NUMBER]'could refer to more than one table listed in the from clause of your sql statement." any help would again be appriciated. here is what i have.

select [UNIT NUMBER], [DATE OF MAINTENANCE], [MILAGE]
from (select [UNIT NUMBER] as TopUnit, Max([DATE OF MAINTENANCE]) as TopDate from [AUTO MAINTENANCE] group by [UNIT NUMBER]) AS Tops
inner join [AUTO MAINTENANCE] on (Tops.TopDate = [AUTO MAINTENANCE].[DATE OF MAINTENANCE]) and(Tops.TopUnit = [AUTO MAINTENANCE].[UNIT NUMBER]);

thanks.

 

[AutumnBlues]

Try it like this...

select [UNIT NUMBER], [DATE OF MAINTENANCE], [MILAGE]
from (select [AUTO MAINTENANCE].[UNIT NUMBER] as TopUnit, Max([AUTO MAINTENANCE].[DATE OF MAINTENANCE]) as TopDate from [AUTO MAINTENANCE] group by [AUTO MAINTENANCE].[UNIT NUMBER]) AS Tops
inner join [AUTO MAINTENANCE] on (Tops.TopDate = [AUTO MAINTENANCE].[DATE OF MAINTENANCE]) and(Tops.TopUnit = [AUTO MAINTENANCE].[UNIT NUMBER]);

If it still doesn't work, post the structure of your tables and I'll have a proper look.

AutumnBlues

 

[kevin531]

hey, thanks for your tips. everything is up and working now. thanks again.
-Kevin531