running total # of occurances

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I have a query with 2 tables. They are order header and
order detail. They are joined by Order Number where Order
number can occur only once in order header and many times
in order detail. Is there a way to add a field that
counts the number of times a particular order # occurs in
order detail. example below:

Order Number: # of Occurances:
123456 1
123456 2
123456 3
123456 4
123457 1
123457 2
 
hi,
yes but it might require a 3rd query.
create a query with order detail table. in the query pull
the order number in twice. click the total icon (looks
like the greek letter sigma). in the query pane read down
the left side to find "totals" click in the totals rows on
the second order number. click the down arrow and scroll
down to and click Count. run the query. you should have a
query showing each order number and number of occurances.
you can add this query to your other query, link on order
number and add the count field.
 
that was very close. however it shows like the following:

Order#: Occurences:
123456 4
123456 4
123456 4
123456 4

any suggestions? thx for your help so far
 
Back
Top