Group By, Last, query does not work

[AlexMidd]

This is a recurring problem, which occurs sometimes and not others, with no apparent reason why.

I have an equipment table and a certs table, linked 1 to many, ie each item can have several certs. I need a query showing all the items with the LAST cert for each item. This could be done by using the cert number or cert date field.

I have a query with 'Group By' set for all fields except the ones from the certs table, which are set to 'Last'. However, although the query runs fine, it shows the last cert for some items and not for others. Has anyone come across this and is there a correct way of doing it?

Have fun! )

Alex Middleton

 

[cmmrfrds]

Does it list the item without a cert, or does the item just not show up. If you are doing an inner join and there are no certs then the item will not show up. You would need to do a left join on the item to cert.

.... from Item left join cert ON item.id = cert.id

 

[gusbrunston]

Hi:
I struggled with this problem so long...I don't want to go back there! But...
I found that my problem was cured when I deleted all but the absolutely necessary fields from the query...dunno why.
Cheers. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[AlexMidd]

Yes, Gus, that has happened with me in the past too. But it has reverted to type. It is not that the record does not appear, it comes up with the wrong cert, i.e. not the last one. I want it not to show any items that do not have a cert. Have fun! )

Alex Middleton