Question about a query.

[Chelsea7]

I have a question about a query I'm trying to do. Below counts the total records such as
SELECT carrier_routes.[Parent Zip], Count(carrier_routes.Route) AS CountOfRoute, Count(logbook.AQRT) AS CountOfAQRT FROM carrier_routes INNER JOIN logbook ON carrier_routes.ID = logbook.ID GROUP BY carrier_routes.[Parent Zip];
Results:
CountOfRoute CountOfAQRT
62 7

However, if I use a Where command to exclude or look for a particular string, it will work but the count for CountOfRoute will change also,
SELECT carrier_routes.[Parent Zip], Count(carrier_routes.Route) AS CountOfRoute FROM carrier_routes INNER JOIN logbook ON carrier_routes.ID = logbook.ID WHERE ((logbook.AQRT)<>"AQRT AMS")) GROUP BY carrier_routes.[Parent Zip];
CountOfRoute CountOfAQRT
6 6
I need to keep them separate were it will not change the count of te Route;
CountOf Route CountOf AQRT
62 6
Can this be done in one query?

 

[r937]

could you do two separate queries?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Chelsea7]

I would like to do separate queries. I actually tried that. It wasn't a problem until I tried to use them in Access report. For some reason I get this error using the wizard which claims that more than one query can be added;

"You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

 

[r937]

if you could do separate queries, test them, get them working correctly, and paste them here, then i can show you how to combine them into one query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Chelsea7]

Hello. Okay here they are;

SELECT Count(amslogbook.AQRT) AS CountOfAQRT
FROM amslogbook
WHERE (((amslogbook.AQRT)<>"AQRT AMS"));


SELECT Count(amslogbook.AQRT) AS CountOfAQRT
FROM amslogbook
WHERE (((amslogbook.AQRT)<>"AQRT AMS"));



I was looking at Union, but I'm wasn't sure.

Thanks

 

[r937]

maybe it's me, but those queries look identical

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Chelsea7]

Sorry about that. Here it is;


SELECT amscarrier_routes.[Parent Zip], Count(amscarrier_routes.Route) AS CountOfRoute
FROM amscarrier_routes
GROUP BY amscarrier_routes.[Parent Zip];


SELECT Count(amslogbook.AQRT) AS CountOfAQRT
FROM amslogbook
WHERE (((amslogbook.AQRT)<>"AQRT AMS"));

 

[r937]

try this --

SELECT [Parent Zip]
     , COUNT(*) AS CountOfRoute
     , ( SELECT COUNT(*) 
           FROM amslogbook
          WHERE AQRT <> 'AQRT AMS' ) AS CountOfAQRT  
  FROM amscarrier_routes
GROUP 
    BY [Parent Zip]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Chelsea7]

Hello again. Thank you. It works. But there's one minor problem. It keeps the count under CountOfRoute and CountOfAQRT separate. Which is what I needed. However, it repeats the totals for CountOfAQRT for all the other records instead of just the one. The CountOfRoute is fine. Here's and example of what I'm talking about;

CountOfRoute CountOfAQRT
62 7
37 7
36 7

The ones with the CountOfRoute of 37 & 36 have no entries for CountOfAQRT and should be zero.

CountOfRoute CountOfAQRT
62 7
37 0
36 0

Is there something left out?

Thanks.

 

[r937]

you gave two queries, and the second one had no connection whatsoever to the first one

how/why would you associate CountOfAQRT with route 62?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Chelsea7]

Hello again. First, I would like to thank you for your patience. This is something I have to complete for a report. I apologize it I'm wasn't clear.

Anyway,the whole thing here is that the two queries have no connection so that I can get the total number of CountOfRoutes only regardless of the CountOfAQRT. However, the [Parent Zip] but have it's own CountOfAQRT associated to it. Something like this;

PARENT ZIP CountOfRoute CountOfAQRT
10001 62 7
10030 36 0

and so forth.

my original code was;

SELECT carrier_routes.[Parent Zip], Count(carrier_routes.Route) AS CountOfRoute, Count(logbook.AQRT) AS CountOfAQRT
FROM carrier_routes INNER JOIN logbook ON carrier_routes.ID = logbook.ID
GROUP BY carrier_routes.[Parent Zip];

But it didn't didn't exclude the 'AQRT AMS'. It became a problem when I used the 'where' statement. Then is only counted the records that had entries other than AQRT AMS. I still needed the total CountOfRoutes.

See below;

SELECT carrier_routes.[Parent Zip], Count(carrier_routes.Route) AS CountOfRoute, Count(logbook.AQRT) AS CountOfAQRT
FROM carrier_routes LEFT JOIN logbook ON carrier_routes.ID = logbook.ID
WHERE (((logbook.AQRT)<>"AQRT AMS"))
GROUP BY carrier_routes.[Parent Zip];

So it would look like this;
Parent Route CountofRoute CountOfAQRT
10001 7 7
10030 3 3

Instead of

Parent Route CountOfRoute CountOfAQRT
10001 62 7
10030 37 3

 

[r937]

i am totally lost

perhaps what you could do is give a few rows of sample data from each table, and then show the query results that you want for that sample

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

SELECT [Parent Zip]
, COUNT(*) AS CountOfRoute
, ( SELECT COUNT(*)
FROM amslogbook
WHERE AQRT <> 'AQRT AMS' [!]AND ID = amscarrier_routes.ID[/!]) AS CountOfAQRT
FROM amscarrier_routes
GROUP
BY [Parent Zip]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886