Please help with Query or my Boss will kill me.

[Guest]

I am looking to run a query on the following.

1. The first column in my table contains Customer Numbers.
2. The second column in my table contains Circuit IDs (these are all unique)
3. In many cases, there are multiple Circuit IDs per Customer Number.
4. The third column in my table contains Cost for each unique Circuit ID
5. In many cases, there is no value populated in the Cost field.

I would like to be able to run a query that returns ONLY Customer Numbers
whereby ALL the Cost fields have been populated with a value. I would like
the query to include the total value of the Cost field.

I am new to this and have attempted an SQL statement, but no luck thus far.
Please help.

Todd

 

[Duane Hookom]

Todd,
Since you are under such pressure, you should consider providing some sample
records and desired output/display. It would make helping you a lot easier.

You might be able to use a totals query with SQL like:

SELECT [CustomerNumber], Sum([Cost]) as TotalCost
FROM [table with no name provided]
Having Count([CustomerNumber]) = Count([Cost]);

 

[Guest]

Duane: Does the following sample help? When I tried to run the query below
I got the error "you tried to execute a query that does not include the
specified expression "Cust No" as part of an aggregate function."

TABLE NAME is "data3"

Cust No Ckt ID Loop Cost
123 GH1234 $100
123 AB9999 $250
888 CD8888 $0
888 DD5555 $50
555 AA6666 $100

The result I am looking for would return ONLY those customer numbers where
the Loop Cost was populated with something other than $0 (or null). I would
like the query to return the following based on the above info:

Cust No Loop Cost
123 $350
555 $100
-this would be the only returns. Since Cust No 888 has a $0 in the Loop
Cost field it would not be returned.

I appreciate your help. Todd

Todd,
Since you are under such pressure, you should consider providing some sample
records and desired output/display. It would make helping you a lot easier.

You might be able to use a totals query with SQL like:

SELECT [CustomerNumber], Sum([Cost]) as TotalCost
FROM [table with no name provided]
Having Count([CustomerNumber]) = Count([Cost]);
--
Duane Hookom
MS Access MVP
--

I am looking to run a query on the following.

1. The first column in my table contains Customer Numbers.
2. The second column in my table contains Circuit IDs (these are all
unique)
3. In many cases, there are multiple Circuit IDs per Customer Number.
4. The third column in my table contains Cost for each unique Circuit ID
5. In many cases, there is no value populated in the Cost field.

I would like to be able to run a query that returns ONLY Customer Numbers
whereby ALL the Cost fields have been populated with a value. I would like
the query to include the total value of the Cost field.

I am new to this and have attempted an SQL statement, but no luck thus
far.
Please help.

Todd

 

[Duane Hookom]

My bad on missing the GROUP BY Cust No.

SELECT [Cust No], Sum([Loop Cost]) as TotalCost
FROM [data3]
GROUP BY [Cust No]
Having Count([Cust No]) = Sum(Abs(Nz([Loop Cost],0)<>0) );


--
Duane Hookom
MS Access MVP

Duane: Does the following sample help? When I tried to run the query
below
I got the error "you tried to execute a query that does not include the
specified expression "Cust No" as part of an aggregate function."

TABLE NAME is "data3"

Cust No Ckt ID Loop Cost
123 GH1234 $100
123 AB9999 $250
888 CD8888 $0
888 DD5555 $50
555 AA6666 $100

The result I am looking for would return ONLY those customer numbers where
the Loop Cost was populated with something other than $0 (or null). I
would
like the query to return the following based on the above info:

Cust No Loop Cost
123 $350
555 $100
-this would be the only returns. Since Cust No 888 has a $0 in the Loop
Cost field it would not be returned.

I appreciate your help. Todd

Todd,
Since you are under such pressure, you should consider providing some
sample
records and desired output/display. It would make helping you a lot
easier.

You might be able to use a totals query with SQL like:

SELECT [CustomerNumber], Sum([Cost]) as TotalCost
FROM [table with no name provided]
Having Count([CustomerNumber]) = Count([Cost]);
--
Duane Hookom
MS Access MVP
--

I am looking to run a query on the following.

1. The first column in my table contains Customer Numbers.
2. The second column in my table contains Circuit IDs (these are all
unique)
3. In many cases, there are multiple Circuit IDs per Customer Number.
4. The third column in my table contains Cost for each unique Circuit
ID
5. In many cases, there is no value populated in the Cost field.

I would like to be able to run a query that returns ONLY Customer
Numbers
whereby ALL the Cost fields have been populated with a value. I would
like
the query to include the total value of the Cost field.

I am new to this and have attempted an SQL statement, but no luck thus
far.
Please help.

Todd

 

[Guest]

Awesome, it worked. Thanks Duane.

My bad on missing the GROUP BY Cust No.

SELECT [Cust No], Sum([Loop Cost]) as TotalCost
FROM [data3]
GROUP BY [Cust No]
Having Count([Cust No]) = Sum(Abs(Nz([Loop Cost],0)<>0) );


--
Duane Hookom
MS Access MVP

Duane: Does the following sample help? When I tried to run the query
below
I got the error "you tried to execute a query that does not include the
specified expression "Cust No" as part of an aggregate function."

TABLE NAME is "data3"

Cust No Ckt ID Loop Cost
123 GH1234 $100
123 AB9999 $250
888 CD8888 $0
888 DD5555 $50
555 AA6666 $100

The result I am looking for would return ONLY those customer numbers where
the Loop Cost was populated with something other than $0 (or null). I
would
like the query to return the following based on the above info:

Cust No Loop Cost
123 $350
555 $100
-this would be the only returns. Since Cust No 888 has a $0 in the Loop
Cost field it would not be returned.

I appreciate your help. Todd

Todd,
Since you are under such pressure, you should consider providing some
sample
records and desired output/display. It would make helping you a lot
easier.

You might be able to use a totals query with SQL like:

SELECT [CustomerNumber], Sum([Cost]) as TotalCost
FROM [table with no name provided]
Having Count([CustomerNumber]) = Count([Cost]);
--
Duane Hookom
MS Access MVP
--

I am looking to run a query on the following.

1. The first column in my table contains Customer Numbers.
2. The second column in my table contains Circuit IDs (these are all
unique)
3. In many cases, there are multiple Circuit IDs per Customer Number.
4. The third column in my table contains Cost for each unique Circuit
ID
5. In many cases, there is no value populated in the Cost field.

I would like to be able to run a query that returns ONLY Customer
Numbers
whereby ALL the Cost fields have been populated with a value. I would
like
the query to include the total value of the Cost field.

I am new to this and have attempted an SQL statement, but no luck thus
far.
Please help.

Todd