Numbering Group Headers, then sum

[msi30502]

Hi,

I'm having difficulty with the following:
---------------------------------------------------

SELECT COUNT (*)
FROM OE_Orders AS s

WHERE EXISTS
( SELECT *
FROM OE_Orders AS t
WHERE s.CustomerKey = t.CustomerKey
AND t.CustomerKey= 1
)

GROUP BY s.CustomerKey
---------------------------------------------------

As you can see in my attachment I'm only getting zero values. I need each customer code to represent 1 stop, regardless of the number of orders.

Please help.

 

[Andrzejek]

What value as a Count do you get when you run your Select for one particular Cust Code?
[tt]
SELECT COUNT (*)
FROM OE_Orders AS s
...
GROUP BY s.CustomerKey[/tt] <- Why here is an Order By?

Above SELECT COUNT will give you just one outcome/ one number.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[msi30502]

Thanks for the Reply @Andrzejek. To answer your questions/comments,

1. When I select a particular customer code I get zero.
2. "Why here is an Order By?" - I copied the code from another website and filled in the blanks. After your question, I removed the GROUP BY.
3. I don't mind getting just one outcome/number. As per my attachment, you can see that I'm trying to get each CustomerCode to count as 1, and then have all the 1's sum at the bottom of the column.

Another option would be to use a line count in the CustomerCode Header. My objective will be met as long as I get the total number of customers.
However, I don't the code to generate line numbers.

 

[Andrzejek]

>My objective will be met as long as I get the total number of customers.

How about:
[tt]
Select COUNT(*) As NoOfCustomers
From tblCustomers
Where ...[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[msi30502]

Is this what you mean?

SELECT COUNT (*)
FROM AR_Customers AS s

WHERE EXISTS
( SELECT *
FROM OE_Orders AS t
WHERE s.CustomerKey = t.CustomerKey
AND t.CustomerKey = 1
)

Because the result is still zero.

 

[Andrzejek]

Your SQL does not make any sense to me.
It looks like you are testing if any records (well, just one record with one row) from:
[tt]
SELECT COUNT (*)
FROM AR_Customers
[/tt]
exist in
[tt]
SELECT *
FROM OE_Orders AS t
WHERE AR_Customers.CustomerKey = t.CustomerKey
AND t.CustomerKey = 1[/tt]

Unless the second SQL returns one column of numbers, which means [tt]OE_Orders[/tt] table has one numeric column of data and that's the only column in this table.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

It looks like you are attempting to report some data. Where are you using your SQL statement? Can you back out a bit and tell us what you are trying to accomplish with some table structures?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[msi30502]

Okay, let me back up.

I have two tables AR_Customers and OE_Orders.
AR_Customers is just the list of customers with basic information (name, number, address, etc.)
OE_Orders holds information about each order that is processed (product, prices, item codes, etc.)
They are both linked by the .CustomerKey

In my report, I select 2 filters, OE_Orders.OrderDate and OE_Orders.RouteCode. From there, group the results by OE_Orders.CustomerKey and use the AR_Customer table simply to show me the customer name because I don't know the customers by their CustomerKey number.

In reality, the only useful data comes from the OE_Orders table.

If I just run the report with a sum of invoices by date and route, I will get a count of all invoices regardless of the number of customers. Sometimes customers have more than 1 invoice per day.
What I need is the number of Customers (whether they have 1 invoice or multiple). It doesn't matter if I get it by numbering lines by customer or if by generating a unique count sum of all the customers.

Hope this helps. Thanks again for everything.

 

[Andrzejek]

>In my report, I select 2 filters, OE_Orders.[blue]OrderDate[/blue] and OE_Orders.[blue]RouteCode[/blue]
>In reality, the only useful data comes from the OE_Orders table
>What I need is the number of Customers

How about:

Select COUNT(DISTINCT CustomerKey)
From OE_Orders
Where OrderDate = [blue]'08/06/2021'[/blue]
And RouteCode = [blue]12[/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[msi30502]

Just tried it and didn't work:
It assigned each transaction/invoice a default value of 423, then summed by customer and by grand total (see attached)

Select COUNT(DISTINCT CustomerKey)
From OE_Orders
Where OrderDate = OE_Orders.InvoiceDate
And RouteCode = OE_Orders.RouteCode

 

[Andrzejek]

>it and didn't work
Did you get an error? "didn't work" is too vague..

Looks to me that your requirements keep changing. Nowhere InvoiceDate was mentioned before, RouteCode = OE_Orders.RouteCode equals itself...?

If your data looks something like this:
[pre]
OE_Orders table
CustomerKey OrderDate RouteCode ...[blue]
1 08/06/2021 12
1 08/06/2021 12[/blue]
1 08/06/2021 13[blue]
2 08/06/2021 12[/blue]
2 02/02/2021 12
2 03/03/2020 11
3 04/04/2000 12
3 05/05/2000 12
[/pre]
My SQL should return the count of 2 (blue records)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[msi30502]

My appologies I incorrectly typed OE_Orders.OrderDate where I should have written OE_Orders.InvoiceDate

In your code,

Where OrderDate = '08/06/2021'
And RouteCode = 12

you assigned hard values.

I need it to pull the values from my parameter settings. How can I do that?

 

[Andrzejek]

In my book, I ALAWYS start with the hard-coded values just to be sure my SQL works correctly. Only then I replace these values with parameters/variables/user input, etc.

If you do use hard-coded values, do you get the correct outcome?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[msi30502]

Almost there. The values are now 4, which IS the total number of stops. See attached

 

[dhookom]

Is this a Microsoft Access report or other? Access allows you to create a running sum text box in a header which can count the number of unique customers.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[msi30502]

Hi, this is a proprietary program that was developed by Aspen Systems. They included a report generator that allows me to creat reports using SQL expressions.

 

[Andrzejek]

So, what is the final 'SQL expression' that works for you for the number of Customers?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson