Count Queries

[SASHA_D]

Hi Everyone,

I am hoping someone can help me with this.....

I have a table with the following fields:-
Shop_ID, Order_Form_ID, Item_Ref_ID, Month

For a certain selection of shops I want to be able to count how many items there are per order form.
(One Order form will have many Item Refs)
If I do a simple group by query and do a count of 'order_form _ID' and 'Item_ref_ID', it brings back the same number i.e. the number of order forms.
I have also tried to run seperate queries for items and order forms, but this brings back the same results.
I have run a query without any grouping and I can see what I want to count in the results i.e. Order form ID appearing many times with different item refs, however I don't know how to tell Access to do it.
Can anyone help with this ?

Thanks!,

Sasha

 

[Rich]

Shouldn't you group by the OrderFormID and count ItemRef?

 

[raskew]

Sasha-


Try copying/pasting the following code to a new query in Northwind.

Think it's got the elements you're seeking (substituting EmployeeID for ShopID) and, once you're familiar with what it's doing, you can adapt it to your scenario.

What prompted for mm/yyyy enter something between 08/1994 and 06/1996 (the range of table Orders).

PARAMETERS [enter mm/yyyy] Text;
SELECT
    Orders.EmployeeID AS shop
  , [Order Details].OrderID
  , Orders.OrderDate
  , Count([Order Details].ProductID) AS CountOfProductID
FROM
   Orders 
INNER JOIN
   [Order Details] 
ON
   Orders.OrderID = [Order Details].OrderID
WHERE
   (((Orders.OrderDate) Between DateValue([enter mm/yyyy]) 
AND
   DateAdd("m",1,DateValue([enter mm/yyyy]))-1))
GROUP BY
   Orders.EmployeeID
  , [Order Details].OrderID
  , Orders.OrderDate;

HTH- Bob