one to many tbl group and count

[shutzy]

i have 2 tables. tblOrders and tblOrdersItems. tblOrdersItems is the child and tbl Orders is the parent. the linking field is OrderID.

i am wanting to count all the OrderID in tblOrders. the criteria i need is EmployeeListID which is stored in tblOrdersItems.

when i put these tables into query design, add the criteria, grouping(see below)

SELECT tblOrders.OrderID
FROM tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee
WHERE (((tblOrders.OrderDate)>Date()) AND ((tblEmployeeList.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees]))
GROUP BY tblOrders.OrderID;

i get (record count at bottom of query screen) 87 records. then i add a field to give me the count so i can then use it i get many more.

im hoping someone can get the count working. i have uploaded the relevant tbl and my attempted query.

thanks

 

[Mihail]

Is this what you are looking for ?

SELECT tblOrders.OrderID, Count(tblOrders.OrderID) AS CountOfOrderID
FROM tblEmployeeItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeItems.EmployeeListID = tblOrdersItems.Employee
WHERE (((tblOrders.OrderDate)>Date()) AND ((tblEmployeeItems.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees]))
GROUP BY tblOrders.OrderID;

 

[shutzy]

no, you see the access record count at the bottom. i want that value in a row. im usually very good with queries but this one i am stumped. im not sure if the criteria (EmployeeListID) is having a bad effect as it is forcing me to include tblOrdersItems. i could do it in 2 seperate queries. could i? ive thought of having a query within a query. first, group all the OrderID with the criteria specified and then put that in as a field and count. would this work, i havent tried it because i dont really know what i am doing with putting a query in a query. someone has done it before fore me put i can just not replicate it.

 

[Mihail]

SELECT Count(tblOrdersItems.OrdersItemsID) AS CountOfOrdersItemsID
FROM tblOrdersItems
GROUP BY tblOrdersItems.Employee
HAVING (((tblOrdersItems.Employee)=[Forms]![rptReportsMainScreen]![txtEmployees]));

 

[Mihail]

Also, you can use the DCount function:
DCount("*", "tblOrdersItems", "Employee = " & [Forms]![rptReportsMainScreen]![txtEmployees])

Not try but should work

 

[shutzy]

i have just tried this query in another query

first one

SELECT tblOrders.OrderID
FROM tblEmployeeItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeItems.EmployeeListID = tblOrdersItems.Employee
WHERE (((tblOrders.OrderDate)>Date()) AND ((tblEmployeeItems.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees]))
GROUP BY tblOrders.OrderID;

then inside this

SELECT Count(("SELECT tblOrders.OrderID FROM tblEmployeeItems INNER JOIN  tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeItems.EmployeeListID = tblOrdersItems.Employee WHERE (((tblOrders.OrderDate)>Date()) AND  (tblEmployeeItems.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees])) GROUP BY tblOrders.OrderID;")) AS [Count]
FROM tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee;

first of all can i just say that i am impressed that i did not get any errors. but unfortunately it did not give the desired results.

 

[shutzy]

sorry milhail. its the OrderID's i want to count. but in the tblOrdersItems the are many of the same as it is one to many tbl system.

if you put this in then at the bottom of the screen where it shows Record 1 of 87.

87 is the number i want in a field.

SELECT Count(("SELECT tblOrders.OrderID FROM tblEmployeeItems INNER JOIN  tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeItems.EmployeeListID = tblOrdersItems.Employee WHERE (((tblOrders.OrderDate)>Date()) AND  (tblEmployeeItems.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees])) GROUP BY tblOrders.OrderID;")) AS [Count]FROM tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee;

 

[Mihail]

I see that I make a mistake. Sorry
Only by using two queries I have accomplished this task.

 

[shutzy]

thanks milhail. im now wondering if it is possible to do it in one query. more liek the one i did earlier. i prefer to keep everything as tidy as possible.

 

[Mihail]

I think that is possible. But only for very skilled guys in the SQL area.
I don't know if in one query (as we know) but almost sure in only one SQL string.