Sequential Counter by Category Field

[lemo]

hi.
tried to figure this out on my own (using the DCount function), but sadly in vain.

say i have a table with two fields, Prop and DateInsp (property and date when it was inspected). i'd like to write a query that will produce the following output -

Prop DateInsp Cntr

Prop1 3/24/11 1
Prop1 1/16/10 2
Prop1 2/21/09 3
Prop2 4/13/11 1
Prop2 6/20/10 2
Prop3 3/30/11 1
...

basically, it's the table sorted by Prop ascending and DateInsp descending, and then the most recent inspection for each property is marked with 1, second recent marked with 2, etc.

thanks in advance,
len

 

[pbaldy]

Why don't you post your DCount() effort and we'll fix it. You'll need criteria on both the property and date fields.

 

[lemo]

i was trying to make this work -

SELECT InspectionMainSm.[Prop ID], InspectionMainSm.Date, DCount("[date]","InspectionMainSm","[date] <= " & [date]) AS [Counter]
FROM InspectionMainSm
ORDER BY InspectionMainSm.[Prop ID], InspectionMainSm.Date DESC;

tried to make it actually count, instead of displaying 0's, and then expand it with the "In (SELECT TOP x [date]..." criteria, perhaps, but nothing worked.
(sorry for the [date] variable name, i know it's not advisable, but i am stuck with it)
l

 

[pbaldy]

Well, as I said you'd need criteria on both fields, not just the date field. Also, date fields require a delimiter. More info here:

http://www.mvps.org/access/general/gen0018.htm

 

[lemo]

thanks Paul.
i still can't wrap my mind around this DCount business. just not enough brain cells / connections, looks like..

so the criteria for 'Date' could be -
In (SELECT Top 3 date FROM InspectionMainSm as t WHERE t.[Prop ID]=InspectionMainSm.[Prop ID] ORDER BY t.[Prop ID], t.date DESC)
correct?
but i can't figure what it could possibly be on [Prop ID]. and what do you mean by a delimiter for the date field?
thanks for your time and patience, i really appreciate.
the website you linked to is awesome!..
l

 

[pbaldy]

The DCount would look like:

DCount("*","InspectionMainSm","[date] <= #" & [date] & "# AND [Prop ID] = " & [Prop ID])

or if Prop ID is a text field

DCount("*","InspectionMainSm","[date] <= #" & [date] & "# AND [Prop ID] = '" & [Prop ID] & "'")

 

[lemo]

worked like a charm! the second one, since Prop ID is text indeed, and also with '>' instead of '<' since the latest dates should be labeled with '1', second latest - '2', etc.

thanks Paul, this is so great..

now i just need to figure out what's actually going on in this DCount statement, and also have them index the Prop ID and Date variables (otherwise it takes prohibitively long time to run).

the final code is -

SELECT InspectionMainSm.[Prop ID], InspectionMainSm.Date, DCount("*","InspectionMainSm","[date] >= #" & [date] & "# AND [Prop ID] = '" & [Prop ID] & "'") AS [Counter]
FROM InspectionMainSm
WHERE (((InspectionMainSm.Date) In (SELECT Top 3 [date] FROM InspectionMainSm as t WHERE t.[Prop ID]=InspectionMainSm.[Prop ID] ORDER BY t.[Prop ID], t.[date] DESC)))
ORDER BY InspectionMainSm.[Prop ID], InspectionMainSm.Date DESC;

 

[pbaldy]

Domain aggregate functions in queries are notorious performance problems. If this is for a report, I'd be more likely to use the running sum property of a textbox for this.

 

[lemo]

no, it's not for a report, it's for Excel pivot table(s), so i think i am stuck with this design. which in my case is not too bad if the variables involved are indexed, i tested before..
thanks again,
l