Performance problems with DMax function. 2

[HockeyFan]

Please help. I have a database that is split. It uses many internal tables, but one of them is linked to our mainframe computer for its data.
Anyhow this table can have between 12 and 18 thousand records. It gets purged weekly.

Here's the situation: My table contains records from approximately 7 days worth of data which was retrieved from PCs from our facility. I use the DMax funtion to look at the date field in my table, and retrieve only the highest transmission date from each PC.

The reason i use this function is because not every PC will report back data every day. Which means on today's date, 20050711, that may be the highest date for 50 of my records, but the most recent date for some of the PCs may be 20050708.
I need to show every PC's data, regardless of when their most recent date is.

The problem with my method, is that it is EXTREMLY slow. I followed any suggestion I found so far and still can't make it go any faster. Please help me find a better way.

 

[jrbarnett]

Can you not use a group by query with the Max function, something like:

Select PCName, Max (Datefield) As LatestSubmission
From tablename
Group By PCName

John

 

[HockeyFan]

I actually may have said something not quite clearly enough in my description. When I said , 'I need to show every PC's data, regardless of when their most recent date is.' what I meant was that I need to show the most recent date of every PC that reported back. The most recent date from some of them might be today, but the most recent date from others may be 2 days ago.

I think in your example above, I would get all of the data showing only the higest date. I don't believe it would show me all records.

 

[jrbarnett]

I don't quite understand your question. My posting will show the latest posting date for each pc together with the name; only pc's without submissions will not be shown at all.

If you want to show all records, but want to show the highest, I'd create a report based on the table, sorting on PC name and date with grouping on the PC name with a footer or header showing the date.

Can you post the SQL code for your existing query please? Hopefully this will let me understand your problem further.

John

 

[HockeyFan]

Thanks for all of your help. Here is the sql code. The problem with trying to speed up only the reports also, is that the database is used as interactive with the main form also. Entering the database is slow also because I do a refresh on the records since they're displayed on the main form.


SELECT CopyOfLinkedTable.SRCDSC, CopyOfLinkedTable.DATANM, CopyOfLinkedTable.ABBRV, CopyOfLinkedTable.PRODTE, TerminalsAndRegions.Terminal, TerminalsAndRegions.Region, TerminalsAndRegions.Location, TerminalsAndRegions.Vendor
FROM TerminalsAndRegions LEFT JOIN CopyOfLinkedTable ON TerminalsAndRegions.Terminal = CopyOfLinkedTable.DSTTRM
WHERE (((CopyOfLinkedTable.SRCDSC)="STREET ATLAS") AND ((CopyOfLinkedTable.PRODTE)=DMax("prodte","CopyOfLinkedTable","DSTTRM = " & [DSTTRM])));

I create a copy of my linked table(hence the name), because that way i can do format editing and stuff on a linked table. The field prodte is the date field. dsttrm is a unique number for each pc. The TerminalsAndRegions table has this number and where its located. In this particular query, Srcdsc contains one of the pieces of data we're looking for on the hard drive of the pc. I wish i could just take a screen capture of my table and show it to you. I know this all probably sounds pretty confusing, but I don't think I'm the only one to ever have this situation. Once again, thanks for helping me out here.

 

[jrbarnett]

Use a correlated subquery:

SELECT CopyOfLinkedTable.SRCDSC, CopyOfLinkedTable.DATANM, CopyOfLinkedTable.ABBRV, CopyOfLinkedTable.PRODTE, TerminalsAndRegions.Terminal, TerminalsAndRegions.Region, TerminalsAndRegions.Location, TerminalsAndRegions.Vendor
FROM TerminalsAndRegions LEFT JOIN CopyOfLinkedTable ON TerminalsAndRegions.Terminal = CopyOfLinkedTable.DSTTRM
WHERE (((CopyOfLinkedTable.SRCDSC)="STREET ATLAS") AND ((CopyOfLinkedTable.PRODTE)= (SELECT Max (ProdDTE) From CopyOfLinkedTable c Where c.dsttrm = CopyOfLinkedTable.ProDTE)

John

 

[HockeyFan]

I'm trying it now. 1 question. It said missing ) or ]. so i had to add 2 parenthesis. I added them at the very end of the statement.
Is that where you would have intended for them to be added?

 

[HockeyFan]

I ran it. It displays no records.

 

[HockeyFan]

Ok, I changed the last field in copyoflinkedtable.prodte to dsttrm. I did get records, but not all of them. It only displayed the records with the most recent(highest) date. here's what the modified sql looks like.

SELECT CopyOfLinkedTable.SRCDSC, CopyOfLinkedTable.DATANM, CopyOfLinkedTable.ABBRV, CopyOfLinkedTable.PRODTE, TerminalsAndRegions.Terminal, TerminalsAndRegions.Region, TerminalsAndRegions.Location, TerminalsAndRegions.Vendor
FROM TerminalsAndRegions LEFT JOIN CopyOfLinkedTable ON TerminalsAndRegions.Terminal = CopyOfLinkedTable.DSTTRM
WHERE (((CopyOfLinkedTable.SRCDSC)="STREET ATLAS") AND ((CopyOfLinkedTable.PRODTE)=(SELECT Max (ProDTE) From CopyOfLinkedTable c Where c.dsttrm = CopyOfLinkedTable.dsttrm)));

it's much faster, but unfortunately it's excluding the records where the date is not equal to the max.

 

[jrbarnett]

Oops spot the non-deliberate mistake (honest).

SELECT CopyOfLinkedTable.SRCDSC, CopyOfLinkedTable.DATANM, CopyOfLinkedTable.ABBRV, CopyOfLinkedTable.PRODTE, TerminalsAndRegions.Terminal, TerminalsAndRegions.Region, TerminalsAndRegions.Location, TerminalsAndRegions.Vendor
FROM TerminalsAndRegions LEFT JOIN CopyOfLinkedTable ON TerminalsAndRegions.Terminal = CopyOfLinkedTable.DSTTRM
WHERE (((CopyOfLinkedTable.SRCDSC)="STREET ATLAS") AND ((CopyOfLinkedTable.PRODTE)= (SELECT Max (ProdDTE) From CopyOfLinkedTable c Where c.dsttrm = CopyOfLinkedTable.dsttrm)

John

 

[HockeyFan]

you are great. thank you so much for this valuable tip.