Rank or Dcount by dates

[ciarra41]

Hi,
I'm trying to rank my date field in this order using 20080101,20080201 etc to rank 1,2

1 20080101
2 20080201

however it want rank using 1 or 11 it is starting with 2,3 here's the sql. Any help would do; MUCH THANKS!

SELECT DCount("evt_mnth_key","TestTable","evt_mnth_key <= " & [evt_mnth_key]) AS sdate, bar.idmth
FROM TestTable RIGHT JOIN bar ON TestTable.idmonth = bar.idmth;

 

[BitZero]

Could you provide some information about the "bar" table. What is the key of the table? Is the field idmth part of the key?

I suspect the problem has to do with joining to the bar table. I don't see why you need the bar table, since the only field you're using is bar.idmth, and you could use TestTable.idmonth instead.

 

[ciarra41]

Guys, Any suggestions on this?
Reiterating: I'm currently trying to use the sql below to rank these field like this;
1 20080101
2 20080201
3 20080301
4 20080401
But its showing this: It want recount at 1
2 20080201
3 20080301
4 20080401
5 20080501
6 20080601
7 20080701
8 20080801
9 20080901
10 20081001
11 20081101
11 20081101
11 20081101
11 20081101

Here's the sql again

SELECT DCount("evt_mnth_key","TestTable","evt_mnth_key <= " & [evt_mnth_key]) AS sdate, bar.idmth
FROM TestTable RIGHT JOIN bar ON TestTable.idmonth = bar.idmth;

 

[PHV]

The real question is:
What are the data in the tables used for your posted sample ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ciarra41]

All of these are number fileds. The key value is in the "TestTable" field "evt_mnth_key"
I can send the mdb file if you want.