formula report help please

  • Thread starter Lee-Anne Waters via AccessMonster.com
  • Start date
L

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
 
M

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
 
L

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,
Click to expand...
[quoted text clipped - 17 lines]
many thanks
Lee-Anne
Click to expand...
Click to expand...
 
Top