numbering records according to date and ID

[×ייל המבולבל]

Hope you can help,

I have a groupby query that gets data from several tables called QOrders.
The records are sorted by PurchaseDate and then by PurchaseID (Primary Key).
I would like to make a running sum in a subform of PurchaseAmount according
to the same order (first sort by date then by ID).

Since I could only manage to sort by ID even though I entered date as
criteria, I wonder if it would be better to add a records counter that will
count according to my sorting order.
Then I plan to use this counter as criteria for the running sum.

My query looks like this

PurchaseDate PurchaseID PurchaseAmount Counter
12/3/07 123 500 2
12/3/07 125 250 2
12/4/07 113 200 3
12/5/07 153 100 4


Tried with Dcount to make the counter (used format(Purchasedate,"general
number") but as you can see, it groups the counter for eadh date.

thanks

 

[Allen Browne]

You could try an expression like this:

=DSum("PurchaseAmount", "QOrders", "(PurchaseDate <= " &
Format(Nz([PurchaseDate], #1/1/1900#, "\#mm\/dd\/yyyy\#") &
") AND IIf(PurchaseDate = " &
Format(Nz([PurchaseDate], #1/1/1900#, "\#mm\/dd\/yyyy\#") &
", PurchaseID <= " & Nz(PurchaseID, 0) ", True)

The Criteria consists of 2 parts that must both be true for the records to
be included in the sum:
a) The purchase date must be up to the date of the current record
(inclusive.)

b) If the purchase date is the same as the current record, then the
PurchaseID must be up to the current PurchaseID value. Otherwise, the 2nd
part of the condition is evaluated as True (i.e. there is no comparison on
the PurchaseID field.)

The formatting of the date is important if the database could be used in a
country with non-US dates. More info:
http://allenbrowne.com/ser-36.html

If either value were Null, the Criteria string would be invalid, and so the
query would show #Error for that row. To avoid that, we included Nz() around
both field values in the expression.

Finally, the query will not give the results the user expects if they apply
a filter to the query (since the expression doesn't filter, i.e. it sums all
values), or if the user sorts the query differently (since the expression
assumes a sort by PurchaseDate + PurchaseID.)

 

[KARL DEWEY]

Since I could only manage to sort by ID even though I entered date as
criteria,
What has criteria to do with sorting?
Post your query SQL. Open the query in design view, click on menu VIEW -
SQL View, highlight all, copy, and paste in a post.