Union A Totals and Count Query 1

[perrymans]

I have a table with two fields:

UnitAConnector
UnitBConnector

Sometimes the connectors are the same, sometime they are not. I want to create a query that will group each column, then count the totals for each.

My problem should be obvious, I can't get it to group, count, total and Union these two fields.

Any ideas? Thanks. Sean.

 

[lespaul]

could you provide some sample data and what results you expect from them?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[perrymans]

UnitAConnector UnitAConnectorQty
ABC 1
ABC 1
BCD 1

UnitBConnector UnitBConnectorQty
ABC 1
BCD 1
CDE 1

Union Query Results:
Connector CountOfConnector
ABC 3
BCD 2
CDE 1

Again, the trick is that they are in the same table.

I have begun working on another option, which may be better. I am using Append Queries to populate a table with the info, then reading it out of that table. Does this approach make more sense to you? It certainly works.

Thanks. Sean.

 

[PHV]

SELECT Connector, Sum(ConnectorQty) AS CountOfConnector
FROM (
SELECT UnitAConnector AS Connector, UnitAConnectorQty AS ConnectorQty FROM yourTable
UNION ALL SELECT UnitBConnector, UnitBConnectorQty FROM yourTable
) U GROUP BY Connector;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[perrymans]

Thanks for the start, but I think I may have mislead you.

The UnitAConnectorQty is not a table field, but rather a count of UnitAConnector.

So I now have:

SELECT Connector, Sum(ConnectorQty) AS CountOfConnector
FROM
(SELECT UnitAConnector AS Connector, Count(UnitAConnector) AS ConnectorQty FROM Tbl_CableNum
UNION ALL SELECT UnitBConnector, Count(UnitBConnector) FROM Tbl_CableNum)
GROUP BY Connector;

I took out the "U" before the GROUP BY, which I assume was a mistake.

But when I try to run the above SQL, I get an error saying that 'Connetor' is not part of an aggregate function.

Thanks. Sean.

 

[PHV]

SELECT Connector, Sum(ConnectorQty) AS CountOfConnector
FROM (
SELECT UnitAConnector AS Connector, Count(*) AS ConnectorQty FROM Tbl_CableNum GROUP BY UnitAConnector
UNION ALL SELECT UnitBConnector, Count(*) FROM Tbl_CableNum GROUP BY UnitBConnector
) U
GROUP BY Connector;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

The U is an alias for the UNION QUERY FROM Clause. In this situation it's not really necessary, but in other queries that you may be given it is essential for the query to function.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

In this situation it's not really necessary
I think access chokes on unaliased embedded view.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[perrymans]

Wow that worked! Thanks PH!

Now I have to go back and change out of that whole append query process I did, but this will be well worth it!

Thanks again. Sean.

 

[lespaul]

I think access chokes on unaliased embedded view.

I guess I didn't realize that, seems to be the standard, so does mine.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual