count() in a query

J

Jon Rowlan

I have query that returns all order lines for all orders, orders can have
between 1 and 10 order lines.

In then Group my query by Sales Rep.

and

count([ORDER NUMBER]), count([ORDER LINE])

both return the same number

As there are on average 2 lines per order then the count([ORDER NUMBER])
should return about half of count([ORDER LINE])

I have listed the SQL below ... I am wondering whether I am expecting too
much of this query and whether I should split it out into other queries ...

Can anyone advise please?

thanks,

jON

SELECT none_OEHEAD.ORDER_REP_CODE AS [Area Manager],
Count(none_OEHEAD.ORDER_NUMBER) AS [Number of Orders], Count([Order
Lines].ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]
FROM [Order Lines], none_OEHEAD
WHERE (((none_OEHEAD.ORDER_NUMBER)=[Order Lines].[ORDER_NUMBER]) AND
((none_OEHEAD.ORDER_DATE)>=[Forms]![AM Sales Report]![FromDate] And
(none_OEHEAD.ORDER_DATE)<=[Forms]![AM Sales Report]![ToDate]) AND (([Order
Lines].ORDER_NUMBER)>=[Forms]![AM Sales Report]![StartingOrder]))
GROUP BY none_OEHEAD.ORDER_REP_CODE;
 
O

OfficeDev18 via AccessMonster.com

It happened because there is no JOIN between the two tables. Simply change
your opening SELECT to SELECT DISTINCTROW... That should take care of the
error.

Sam

Jon said:
I have query that returns all order lines for all orders, orders can have
between 1 and 10 order lines.

In then Group my query by Sales Rep.

and

count([ORDER NUMBER]), count([ORDER LINE])

both return the same number

As there are on average 2 lines per order then the count([ORDER NUMBER])
should return about half of count([ORDER LINE])

I have listed the SQL below ... I am wondering whether I am expecting too
much of this query and whether I should split it out into other queries ...

Can anyone advise please?

thanks,

jON

SELECT none_OEHEAD.ORDER_REP_CODE AS [Area Manager],
Count(none_OEHEAD.ORDER_NUMBER) AS [Number of Orders], Count([Order
Lines].ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]
FROM [Order Lines], none_OEHEAD
WHERE (((none_OEHEAD.ORDER_NUMBER)=[Order Lines].[ORDER_NUMBER]) AND
((none_OEHEAD.ORDER_DATE)>=[Forms]![AM Sales Report]![FromDate] And
(none_OEHEAD.ORDER_DATE)<=[Forms]![AM Sales Report]![ToDate]) AND (([Order
Lines].ORDER_NUMBER)>=[Forms]![AM Sales Report]![StartingOrder]))
GROUP BY none_OEHEAD.ORDER_REP_CODE;
Click to expand...
 
J

Jon Rowlan

That didn't make a difference Sam ???

jON

OfficeDev18 via AccessMonster.com said:
It happened because there is no JOIN between the two tables. Simply change
your opening SELECT to SELECT DISTINCTROW... That should take care of the
error.

Sam

Jon said:
I have query that returns all order lines for all orders, orders can have
between 1 and 10 order lines.

In then Group my query by Sales Rep.

and

count([ORDER NUMBER]), count([ORDER LINE])

both return the same number

As there are on average 2 lines per order then the count([ORDER NUMBER])
should return about half of count([ORDER LINE])

I have listed the SQL below ... I am wondering whether I am expecting too
much of this query and whether I should split it out into other queries
...

Can anyone advise please?

thanks,

jON

SELECT none_OEHEAD.ORDER_REP_CODE AS [Area Manager],
Count(none_OEHEAD.ORDER_NUMBER) AS [Number of Orders], Count([Order
Lines].ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]
FROM [Order Lines], none_OEHEAD
WHERE (((none_OEHEAD.ORDER_NUMBER)=[Order Lines].[ORDER_NUMBER]) AND
((none_OEHEAD.ORDER_DATE)>=[Forms]![AM Sales Report]![FromDate] And
(none_OEHEAD.ORDER_DATE)<=[Forms]![AM Sales Report]![ToDate]) AND (([Order
Lines].ORDER_NUMBER)>=[Forms]![AM Sales Report]![StartingOrder]))
GROUP BY none_OEHEAD.ORDER_REP_CODE;
Click to expand...
Click to expand...
 
J

John Spencer

Count counts the number of times a field has a value, NOT the unique number
of values.

With your field and table names I would probably resort to nested queries to
get the distinct count.

QDistinct:
SELECT Distinct Order_Rep_Code, Order_Number
FROM [Order Lines] as OL Inner JOIN none_OEHEAD as OE
ON OL.Order_Number = OE.Order_Number
WHERE OE.ORDER_DATE>=[Forms]![AM Sales Report]![FromDate] And
OE.ORDER_DATE<=[Forms]![AM Sales Report]![ToDate] AND
OL.ORDER_NUMBER>=[Forms]![AM Sales Report]![StartingOrder]

Use that in another query to get the count - QCountOrders
SELECT Order_Rep_Code, Count(Order_Number) as CountOrders
FROM QDistinct

Finally add the QCountOrders to your original query
Note that I've aliased your table names just to make things easier for me to
read

SELECT OE.ORDER_REP_CODE AS [Area Manager],
Q.CountOrders,
Count(OL.ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]

FROM ( [Order Lines] as OL INNER JOIN none_OEHEAD as OE
ON OL.Order_Number = OE.Order_Number) INNER JOIN QCountOrders as Q
ON Q.Order_Rep_Code = OE.Order_Rep_Code

WHERE OE.ORDER_DATE>=[Forms]![AM Sales Report]![FromDate] And
OE.ORDER_DATE<=[Forms]![AM Sales Report]![ToDate] AND
OL.ORDER_NUMBER>=[Forms]![AM Sales Report]![StartingOrder]

GROUP BY OE.ORDER_REP_CODE, Q.CountOrders


Jon Rowlan said:
I have query that returns all order lines for all orders, orders can have
between 1 and 10 order lines.

In then Group my query by Sales Rep.

and

count([ORDER NUMBER]), count([ORDER LINE])

both return the same number

As there are on average 2 lines per order then the count([ORDER NUMBER])
should return about half of count([ORDER LINE])

I have listed the SQL below ... I am wondering whether I am expecting too
much of this query and whether I should split it out into other queries
...

Can anyone advise please?

thanks,

jON

SELECT none_OEHEAD.ORDER_REP_CODE AS [Area Manager],
Count(none_OEHEAD.ORDER_NUMBER) AS [Number of Orders], Count([Order
Lines].ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]
FROM [Order Lines], none_OEHEAD
WHERE (((none_OEHEAD.ORDER_NUMBER)=[Order Lines].[ORDER_NUMBER]) AND
((none_OEHEAD.ORDER_DATE)>=[Forms]![AM Sales Report]![FromDate] And
(none_OEHEAD.ORDER_DATE)<=[Forms]![AM Sales Report]![ToDate]) AND (([Order
Lines].ORDER_NUMBER)>=[Forms]![AM Sales Report]![StartingOrder]))
GROUP BY none_OEHEAD.ORDER_REP_CODE;
Click to expand...
 
J

Jon Rowlan

Wow, if you wrote those queries without a test bed then I am impressed
Spencer!

Once I'd added the Group By clause to the second query everything else just
fell in to place.

Very much appreciate, many thanks.

jON

John Spencer said:
Count counts the number of times a field has a value, NOT the unique
number of values.

With your field and table names I would probably resort to nested queries
to get the distinct count.

QDistinct:
SELECT Distinct Order_Rep_Code, Order_Number
FROM [Order Lines] as OL Inner JOIN none_OEHEAD as OE
ON OL.Order_Number = OE.Order_Number
WHERE OE.ORDER_DATE>=[Forms]![AM Sales Report]![FromDate] And
OE.ORDER_DATE<=[Forms]![AM Sales Report]![ToDate] AND
OL.ORDER_NUMBER>=[Forms]![AM Sales Report]![StartingOrder]

Use that in another query to get the count - QCountOrders
SELECT Order_Rep_Code, Count(Order_Number) as CountOrders
FROM QDistinct

Finally add the QCountOrders to your original query
Note that I've aliased your table names just to make things easier for me
to read

SELECT OE.ORDER_REP_CODE AS [Area Manager],
Q.CountOrders,
Count(OL.ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]

FROM ( [Order Lines] as OL INNER JOIN none_OEHEAD as OE
ON OL.Order_Number = OE.Order_Number) INNER JOIN QCountOrders as Q
ON Q.Order_Rep_Code = OE.Order_Rep_Code

WHERE OE.ORDER_DATE>=[Forms]![AM Sales Report]![FromDate] And
OE.ORDER_DATE<=[Forms]![AM Sales Report]![ToDate] AND
OL.ORDER_NUMBER>=[Forms]![AM Sales Report]![StartingOrder]

GROUP BY OE.ORDER_REP_CODE, Q.CountOrders


Jon Rowlan said:
I have query that returns all order lines for all orders, orders can have
between 1 and 10 order lines.

In then Group my query by Sales Rep.

and

count([ORDER NUMBER]), count([ORDER LINE])

both return the same number

As there are on average 2 lines per order then the count([ORDER NUMBER])
should return about half of count([ORDER LINE])

I have listed the SQL below ... I am wondering whether I am expecting too
much of this query and whether I should split it out into other queries
...

Can anyone advise please?

thanks,

jON

SELECT none_OEHEAD.ORDER_REP_CODE AS [Area Manager],
Count(none_OEHEAD.ORDER_NUMBER) AS [Number of Orders], Count([Order
Lines].ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]
FROM [Order Lines], none_OEHEAD
WHERE (((none_OEHEAD.ORDER_NUMBER)=[Order Lines].[ORDER_NUMBER]) AND
((none_OEHEAD.ORDER_DATE)>=[Forms]![AM Sales Report]![FromDate] And
(none_OEHEAD.ORDER_DATE)<=[Forms]![AM Sales Report]![ToDate]) AND
(([Order Lines].ORDER_NUMBER)>=[Forms]![AM Sales
Report]![StartingOrder]))
GROUP BY none_OEHEAD.ORDER_REP_CODE;
Click to expand...
Click to expand...
 
J

John Spencer

Glad that worked. Sorry I missed the Group by on the second query.


Jon Rowlan said:
Wow, if you wrote those queries without a test bed then I am impressed
Spencer!

Once I'd added the Group By clause to the second query everything else
just fell in to place.

Very much appreciate, many thanks.

jON

John Spencer said:
Count counts the number of times a field has a value, NOT the unique
number of values.

With your field and table names I would probably resort to nested queries
to get the distinct count.

QDistinct:
SELECT Distinct Order_Rep_Code, Order_Number
FROM [Order Lines] as OL Inner JOIN none_OEHEAD as OE
ON OL.Order_Number = OE.Order_Number
WHERE OE.ORDER_DATE>=[Forms]![AM Sales Report]![FromDate] And
OE.ORDER_DATE<=[Forms]![AM Sales Report]![ToDate] AND
OL.ORDER_NUMBER>=[Forms]![AM Sales Report]![StartingOrder]

Use that in another query to get the count - QCountOrders
SELECT Order_Rep_Code, Count(Order_Number) as CountOrders
FROM QDistinct

Finally add the QCountOrders to your original query
Note that I've aliased your table names just to make things easier for me
to read

SELECT OE.ORDER_REP_CODE AS [Area Manager],
Q.CountOrders,
Count(OL.ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]

FROM ( [Order Lines] as OL INNER JOIN none_OEHEAD as OE
ON OL.Order_Number = OE.Order_Number) INNER JOIN QCountOrders as Q
ON Q.Order_Rep_Code = OE.Order_Rep_Code

WHERE OE.ORDER_DATE>=[Forms]![AM Sales Report]![FromDate] And
OE.ORDER_DATE<=[Forms]![AM Sales Report]![ToDate] AND
OL.ORDER_NUMBER>=[Forms]![AM Sales Report]![StartingOrder]

GROUP BY OE.ORDER_REP_CODE, Q.CountOrders


Jon Rowlan said:
I have query that returns all order lines for all orders, orders can have
between 1 and 10 order lines.

In then Group my query by Sales Rep.

and

count([ORDER NUMBER]), count([ORDER LINE])

both return the same number

As there are on average 2 lines per order then the count([ORDER NUMBER])
should return about half of count([ORDER LINE])

I have listed the SQL below ... I am wondering whether I am expecting
too much of this query and whether I should split it out into other
queries ...

Can anyone advise please?

thanks,

jON

SELECT none_OEHEAD.ORDER_REP_CODE AS [Area Manager],
Count(none_OEHEAD.ORDER_NUMBER) AS [Number of Orders], Count([Order
Lines].ORDER_LINE) AS [Number of Lines],
Round(Sum(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]),2) AS [Order Value],
Round(Sum([Invoiced]*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE])),2) AS
[Invoice Value],
Round(Sum(IIf([ordered]-[allocated]-[delivered]-[invoiced]=0,0,(([ordered]-[allocated]-[delivered]-[invoiced])/[ordered])*(IIf([ORDER_TYPE]="C",-1,1)*[ORDER_VALUE]))),2)
AS [Backorder Value]
FROM [Order Lines], none_OEHEAD
WHERE (((none_OEHEAD.ORDER_NUMBER)=[Order Lines].[ORDER_NUMBER]) AND
((none_OEHEAD.ORDER_DATE)>=[Forms]![AM Sales Report]![FromDate] And
(none_OEHEAD.ORDER_DATE)<=[Forms]![AM Sales Report]![ToDate]) AND
(([Order Lines].ORDER_NUMBER)>=[Forms]![AM Sales
Report]![StartingOrder]))
GROUP BY none_OEHEAD.ORDER_REP_CODE;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select query row empty if value zero/null 1
DISTINCT records when combined with another query. 2
Query with count, but what am I counting?! 4
Code Check Request - Query to find reused values 2
Reduce # of date columns in crosstab query 4
Count function for single lines of query 6
Using count in a query 4
Simple count query not so simple... 3

Top