Count number of records

[Jahappz]

I have an report where i have
sorted date by week

Week Total:
12 600

Orders Value:
90002 100
90003 200
90005 300

Week
13


etc.

What i want to accomplish:

Orders Total
3 600

i want to count the number of "orders"
how do i do this?

 

[LittleSmudge]

Try this:-

In the Orders Control on the Report in the ControlSource put

=Count(Orders)

and in the Total Control CountrolSource put

=Sum(Value)

assuming that Orders and Value are field names that come from your data table.


'ope-that-'elps

G LS

 

[Jahappz]

Yep this works but.

when i have a week that has the following


Week Total:
12 900

Orders Value:
90002 100
90003 200
90002 300
90005 300

Week
13


i want the count for orders just to be for the unique orders

eg Total Orders(week 12) = 3

how do i count the unique numbers?

 

[LittleSmudge]

Go back and Normalise your database structure - is my first response !

How come you can possibly have two orders on the same order number - with DIFFERENT values ! ( Ever. let alone in the same week )



I think the answer will involve the Distinct keyword in a bit of SQL somewhere - but I'll need a lot more info on your schema to sort this one out, I fear.


G LS

 

[Jahappz]

The reason why i have the same order, is that it is not realy an order but order row here i show only the order no as many times that the order has rows.

 

[LittleSmudge]

So do the Count(xxxx) on the actual unique order number instead then - Or whatever the Order table primary key happens to be.


G LS

 

[Jahappz]

I have a table with 3 colums orderno, delivery date and order sum. orderno apeare as often as there are row in the order, i want to count how many row there is sorted by week and how many uniqe orderno there are sorted by week. sorry for the bad spelling.
orderno (rows) Del.date order row sum
1000 2002-07-12 100.00
1000 2002-07-12 123.00
1001 2002-08-15 159.00
1002 2002-08-10 10000.00

 

[LittleSmudge]

Please, PLEASE, PLEASE tell me you have a primary key value somewhere in the table that uniquely identifies any ONE specific record from any of the rest.





Sobbs uncontrollably and wanders off into the night .. .. .. ..



.. .. ..



.. ..



..


g ls

 

[Jahappz]

yes.. i have a unique nr for every line

 

[Advocate]

Jahappz,

one way around this (no doubt there are others ...)

step 1. create new access query (assume called qOrders) which will select distinct orderno (assume called orders) for the required period of weeks eg

select distinct orderno from table
where week between x and y

step 2. put =count(qOrders.orders) control on report

this will now count and display the distinct ordernos value required

hope this helps ...
Best of Irish Luck, David.
djwilkes@hotmail.com

 

[LittleSmudge]

Okay, so it was late at night .. ..

What is your table structure ?

The table that contains these three ( now four ) fields is linked ( INNER JOIN ? ) at the many and to some other table - yes?

What are the tables called
How are they joined


G LS