count() in a query

  • Thread starter Thread starter Jon Rowlan
  • Start date Start date
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;
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top