formula report help please

[Lee-Anne Waters via AccessMonster.com]

hi,

i have a report that list the number of orders and the number of people doing
the orders

in order to count the total orders i have the following which works fine

=Count([Order]) & " Scheduled"

however i also want to count the number of people doing the work.

=Count([CName]) & " Techs"

however it counts the same number of records.
if a day has say 100 orders and 10 techs doing the work then how do i change
the formula to only count the techs?

many thanks
Lee-Anne

 

[Marc]

Hi,
You need a group query
SELECT [the date], techname, Count(techname) as CountOfName FROM orders
GROUP BY [the date];

Note the order is important.
for the above you will get a count for each techname for a date.

For
SELECT [the date], techname, Count(techname) as CountOfName Count(Orders) as
CountOfOrders FROM orders GROUP BY [the date];
you get the count of orders that the tech person worked on that day, i.e. no
different from Count(techname).

For
SELECT [the date], Count(Orders) as CountOfOrders, Count(techname) as
CountOfName FROM orders GROUP BY [the date];
you get the total of all orders for each date, no matter what the techname
is because you are grouping on date.

If you want to obtain both values on the same report you need separate
queries.
HTH
Marc

 

[Lee-Anne Waters via AccessMonster.com]

many thanks Marc

Hi,
You need a group query
SELECT [the date], techname, Count(techname) as CountOfName FROM orders
GROUP BY [the date];

Note the order is important.
for the above you will get a count for each techname for a date.

For
SELECT [the date], techname, Count(techname) as CountOfName Count(Orders) as
CountOfOrders FROM orders GROUP BY [the date];
you get the count of orders that the tech person worked on that day, i.e. no
different from Count(techname).

For
SELECT [the date], Count(Orders) as CountOfOrders, Count(techname) as
CountOfName FROM orders GROUP BY [the date];
you get the total of all orders for each date, no matter what the techname
is because you are grouping on date.

If you want to obtain both values on the same report you need separate
queries.
HTH
Marc

hi,

[quoted text clipped - 17 lines]

many thanks
Lee-Anne