Current On-Hand-Quantity

[Guest]

I am trying to write a query to get some information out of a legacy database. The following is an example of the data and it's structur

In this database, I have a query (qry_Unit_Asset_ReportDate)that gives me a list of my units, their critical assets, and a date value, where the date is every friday

Unit Asset Dat
U1 shirt 4/30/0
U1 shirt 4/23/0
U1 shirt 4/16/0
U1 pant 4/30/0
U1 pant 4/23/0
U1 pant 4/16/0

I then have a table (tbl_Unit_Assets_Reported) where U1 occassionally reports their On-hand-quantity of these items. Unfortunately, they don't always report all of their items, and they don't usually report on the actual date of the report. For reporting purposes, I have to assume that the number they last reported is still valid on the report date if I have not received an interim repor

Unit Asset SubDate Qt
U1 shirt 4/12/04 1
U1 shirt 4/17/04 2
U1 shirt 4/22/04 2
U1 shirt 4/29/04 3
U1 pant 4/11/04 1
U1 pant 4/15/04 1
U1 pant 4/23/04 1

The report should list

Unit Asset RptDate Quantit
U1 shirt 4/30/04 3
U1 shirt 4/23/04 2
U1 shirt 4/16/04 1
U1 pant 4/30/04 1
U1 pant 4/23/04 1
U1 pant 4/16/04 1

My current query looks something like the following, but this is extremely slow, undoubtedly because of the DMAX statement. Is there a better way to join these tables to speed up this operation

SELECT Q.Unit, Q.Asset, Q.RptDate, T.Quantit
FROM qry_Unit_Asset_ReportDate
LEFT JOIN tbl_Unit_Assets_Reported
ON Q.Unit = T.Uni
AND Q.Asset = T.Asse
AND T.SubDate = DMAX("SubDate", "tbl_Unit_Assets_Reported", "Unit = '" & Q.Unit & "' AND Asset = '" & Q.Asset & "' AND SubDate <= #" & Q.RptDate & "#")

 

[Brian Camire]

What about something like this:

SELECT
qry_Unit_Asset_ReportDate.Unit,
qry_Unit_Asset_ReportDate.Asset,
qry_Unit_Asset_ReportDate.RptDate,
(SELECT TOP 1
tbl_Unit_Assets_Reported.Qty
FROM
tbl_Unit_Assets_Reported
WHERE
tbl_Unit_Assets_Reported.Unit = qry_Unit_Asset_ReportDate.Unit
AND
tbl_Unit_Assets_Reported.Asset = qry_Unit_Asset_ReportDate.Asset
AND
tbl_Unit_Assets_Reported.SubDate <= qry_Unit_Asset_ReportDate.RptDate
ORDER BY
tbl_Unit_Assets_Reported.SubDate DESC) AS Quantity

I am trying to write a query to get some information out of a legacy

database. The following is an example of the data and it's structure

In this database, I have a query (qry_Unit_Asset_ReportDate)that gives me

a list of my units, their critical assets, and a date value, where the date
is every friday.

Unit Asset Date
U1 shirt 4/30/04
U1 shirt 4/23/04
U1 shirt 4/16/04
U1 pant 4/30/04
U1 pant 4/23/04
U1 pant 4/16/04

I then have a table (tbl_Unit_Assets_Reported) where U1 occassionally

reports their On-hand-quantity of these items. Unfortunately, they don't
always report all of their items, and they don't usually report on the
actual date of the report. For reporting purposes, I have to assume that
the number they last reported is still valid on the report date if I have
not received an interim report

Unit Asset SubDate Qty
U1 shirt 4/12/04 10
U1 shirt 4/17/04 20
U1 shirt 4/22/04 25
U1 shirt 4/29/04 30
U1 pant 4/11/04 10
U1 pant 4/15/04 15
U1 pant 4/23/04 16

The report should list:

Unit Asset RptDate Quantity
U1 shirt 4/30/04 30
U1 shirt 4/23/04 25
U1 shirt 4/16/04 10
U1 pant 4/30/04 16
U1 pant 4/23/04 16
U1 pant 4/16/04 15

My current query looks something like the following, but this is extremely

slow, undoubtedly because of the DMAX statement. Is there a better way to
join these tables to speed up this operation?

SELECT Q.Unit, Q.Asset, Q.RptDate, T.Quantity
FROM qry_Unit_Asset_ReportDate Q
LEFT JOIN tbl_Unit_Assets_Reported T
ON Q.Unit = T.Unit
AND Q.Asset = T.Asset
AND T.SubDate = DMAX("SubDate", "tbl_Unit_Assets_Reported", "Unit = '"

& Q.Unit & "' AND Asset = '" & Q.Asset & "' AND SubDate <= #" & Q.RptDate &
"#")