Domain aggregate help

[Brett T. Burtner]

Please help. I have an old Excel spreadsheet my company has asked me to
analyze data from. I need to look at the following fields:

[Serial Number]
[Date]

[Deposit]
[Rent]

For each [Serial Number], I need to find the most recent [Date] entered
where [Code] equals "SV".
Then, once I've found the most recent [Date], I need to calculate how much
to send a bill for each Serial Number fitting the above criteria, using the
following equation:

(((TodaysDate)-([Date])*[Rent])-[Deposit])

Please help!

Thanks in advance!

Brett T. Burtner

 

[John Spencer (MVP)]

One way would be to use stacked queries as below.


QueryOne:
Select [Serial Number], Max([Date]) as LastDate
FROM Table
WHERE Code = "SV"
GROUP BY [Serial Number]

QueryTwo:
SELECT T.[Serial Number], T.[Date],
((Date()-T.[Date]) * Rent) - Deposit as TheAmount
FROM Table As T Inner JOIN QueryOne as Q
ON T.[Serial Number] = Q.[Serial Number]
AND T.[Date] = Q.LastDate
WHERE Code = "SV"

If you really want to do this in one query, it can be done but it looks a bit
more complicated. Post back in that case and maybe Tom Ellison will show you
the more complex answer.