Query running very slow

[Daniel-Snack]

I have a site tbl with an indexed SiteID field as primary key), and a sales
table with an indexed "DateOfService" field. I have done two queries to
retrieve the most recent sale for each "SiteID" from the salestbl using the
following criteria:
DMax("[DateOfService]","[tblsales]","[SiteID] = " & [SiteID])

There are 2400 records in the sites table and 30,000 records in the sales
table. When run the query it takes about 90-120 seconds (a lifetime). Any
answers for me

PS just found this site and I'm in heaven thanks to all the smartys out
there...

Daniel-Snack

 

[Duane Hookom]

Please provide your full SQL view.

 

[Allen Browne]

If you don't mind a read-only result, you could create a query into tblSite
only, and use a subquery to get the most recent sale date.

Type something like this into a fresh column in the Field row:
LastSale: ( SELECT Max([DateOfService]) FROM tblSales
WHERE tblSales.SiteID = tblSite.SiteID)

If subqueries are a new concept, there's more info in this article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209066

 

[Daniel]

SELECT tblSales.SiteID, tblSales.DateOfService, tblSales.Totalquantityleft
FROM tblSales
GROUP BY tblSales.SiteID, tblSales.DateOfService, tblSales.Totalquantityleft
HAVING
(((tblSales.DateOfService)=DMax("[DateOfService]","[tblsales]","[SiteID] = "
& [SiteID])))
ORDER BY tblSales.SiteID, tblSales.DateOfService DESC;

Regards

Daniel-snack

Please provide your full SQL view.

--
Duane Hookom
MS Access MVP

I have a site tbl with an indexed SiteID field as primary key), and a sales
table with an indexed "DateOfService" field. I have done two queries to
retrieve the most recent sale for each "SiteID" from the salestbl using the
following criteria:
DMax("[DateOfService]","[tblsales]","[SiteID] = " & [SiteID])

There are 2400 records in the sites table and 30,000 records in the sales
table. When run the query it takes about 90-120 seconds (a lifetime). Any
answers for me

PS just found this site and I'm in heaven thanks to all the smartys out
there...

Daniel-Snack

 

[Daniel]

Allen,

Think I followed it correctly, set up new query and used your SQL in a
single field and no other fields using just the sites table. Worked a charm
thank you very much

Daniel

If you don't mind a read-only result, you could create a query into tblSite
only, and use a subquery to get the most recent sale date.

Type something like this into a fresh column in the Field row:
LastSale: ( SELECT Max([DateOfService]) FROM tblSales
WHERE tblSales.SiteID = tblSite.SiteID)

If subqueries are a new concept, there's more info in this article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a site tbl with an indexed SiteID field as primary key), and a sales
table with an indexed "DateOfService" field. I have done two queries to
retrieve the most recent sale for each "SiteID" from the salestbl using
the
following criteria:
DMax("[DateOfService]","[tblsales]","[SiteID] = " & [SiteID])

There are 2400 records in the sites table and 30,000 records in the sales
table. When run the query it takes about 90-120 seconds (a lifetime). Any
answers for me

PS just found this site and I'm in heaven thanks to all the smartys out
there...

Daniel-Snack