DCount and Sequence

[margaret]

I have a query as follows:

SELECT tblSales.fairdate, tblSales.id, DCount("ID","tblSales","ID<=" & [id])
AS Sequence, tblControlFile.CheckNumber, [sequence]+[checknumber] AS ckno,
tblSales.game, tblSales.amount, ([amount]*0.2) AS ckamt
FROM tblSales, tblControlFile
WHERE (((tblSales.fairdate)>#9/15/2008#));

Right now the sequence is giving me the exact number that "ID" currently is.
I would like a count of how many "ID"'s have been pulled with this criteria.
So while I have ID's numbered 1 through 20, I may only have 5 that are
pulled and the sequence should then be 1 through 5.

 

[Lord Kelvan]

well there seem to be a problem in that query

it is you are not joining tblSales and tblControlFile so you are
getting a cartesian product

that alone may resolve if it dosnt can you show us a sample of date
real or fake of what you are truing to get and the raw data it is
derived from

regards
Kelvan

 

[margaret]

Actually, I don't need the tables to join. The only inforation I'm pulling
is the Control File is "fairdate" ... The control file is just where I store
data that will be changed on a day to day basis. So when I run this query
the "fairdate" from the Control file only pulls one item all the way down the
query

 

[Michel Walsh]

I assume you mean that while the DCount return the count over all the
records, irrespectively of the criteria in the WHERE clause, your wish is to
get the count only of those respecting the criteria in the WHERE clause. If
so, you can try:


SELECT tblSales.fairdate,
tblSales.id,
DCount("ID","tblSales","ID<=" & [id] & " AND fairDate >
#9/15/2008# " ) AS Sequence,
tblControlFile.CheckNumber,
[sequence]+[checknumber] AS ckno,
tblSales.game,
tblSales.amount,
([amount]*0.2) AS ckamt
FROM tblSales, tblControlFile
WHERE (((tblSales.fairdate)>#9/15/2008#));



Vanderghast, Access MVP

 

[Lord Kelvan]

mmm can you give some sample data then so i can run some tests