Sequential Counter by Category Field

L

lemo

Registered User.
Local time
Yesterday, 22:33
Joined
Apr 30, 2008
Messages
187
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
 
Why don't you post your DCount() effort and we'll fix it. You'll need criteria on both the property and date fields.
 
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
 
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
 
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] & "'")
 
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;
 
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.
 
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
 
You must log in or register to reply here.

Similar threads

N
Add RowNumber Problem
Replies
11
Views
840
CJ_London
C
H
Solved Finding names from table of not duplicated rows
Replies
11
Views
563
Pat Hartman
P
K
Group tables
Replies
14
Views
728
jdelano
J
T
Solved the expression you entered is too complex
2
Replies
37
Views
2,259
Pat Hartman
P
lwarren1968
Delete Rows between dates/times using an additional field to determine date/time range
Replies
13
Views
1,773
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom