Union 2 fields where values are counted?

[Guest]

My database is a sign inventory. One of the products we will give to the
client is a summary of all signs inventoried not including state owned signs.
Each location is unique and contains a sign (MUTCD) and a possible
supplemental sign (Supp_MUTCD) which have an Id. For example a Stop sign is
R1-1 which could have the supplemental sign R1-3 for "all way". The inventory
is one table.

I have tried the following Union query but I get a syntax error:

SELECT Signs.MUTCD,
Count (Signs.MUTCD) AS CountofMUTCD
FROM Signs
GROUP BY Signs.MUTCD,
WHERE Not (Signs.Ownership) = "State"
ORDER BY Signs.MUTCD

UNION ALL Signs.Supp_MUTCD,
Count (Signs.Supp_MUTCD) AS CountofMUTCD
FROM Signs
GROUP BY Signs.MUTCD,
WHERE Not(Signs.Ownership) = "State";

What I would like as a result is:

R1-1 100
R1-3 25
I3-1 120
etc.

Is this even possible to do or am I stuck giving two different reports while
manually adding up the signs? (UGH!)

 

[Amy Blankenship]

I think you'd need two queries, one to get the data and one to order it.
HTH;

Amy

 

[Guest]

Try this, assuming the there will always be a MUTCD even if there is no
supplement. --
SELECT Signs.MUTCD AS Sign, Count(Signs.MUTCD) AS CountOfMUTCD
FROM Signs
WHERE Signs.Ownership<>"STATE"
GROUP BY Signs.MUTCD

UNION SELECT Signs_1.Supp_MUTCD, Count(Signs_1.Supp_MUTCD) AS
CountOfSupp_MUTCD
FROM Signs AS Signs_1
WHERE Signs_1.Ownership<>"STATE" and Signs_1.Supp_MUTCD Is Not Null
GROUP BY Signs_1.Supp_MUTCD;

 

[Guest]

Thats really close!!
I now get this

Sign CountOfMUTCD
I12-1 1
I2-12 1
I3-1 4
I3-1 40

Which I believe I somehow achieved early on; but is there a way it can "add
up" the counts for the individual signs? For example, I3-1 should just be 44.
Does it require an If Then statement with a sum?

Thanks so much for your help so far!! Its been a very long time since I
have done any sort of "programming". Good to now i was on the right track.
This forum is great!

 

[Guest]

I thought I had it.

Post your data for me.

Thats really close!!
I now get this

Sign CountOfMUTCD
I12-1 1
I2-12 1
I3-1 4
I3-1 40

Which I believe I somehow achieved early on; but is there a way it can "add
up" the counts for the individual signs? For example, I3-1 should just be 44.
Does it require an If Then statement with a sum?

Thanks so much for your help so far!! Its been a very long time since I
have done any sort of "programming". Good to now i was on the right track.
This forum is great!

 

[Guest]

This is the query as I modified it. There are 15 towns that we are supplying
data to. You were correct to assume that not all MUTCD will have a
Supp_MUTCD. Again this inventory information is stored in one table of the
database. Do you need anything else? Here is the town of Arcadia's union
query.

SELECT ArcadiaSigns.MUTCD AS Sign, Count(ArcadiaSigns.MUTCD) AS CountOfMUTCD
FROM ArcadiaSigns
WHERE ArcadiaSigns.Ownership<>"STATE"
GROUP BY ArcadiaSigns.MUTCD

UNION SELECT ArcadiaSigns_1.Supp_MUTCD, Count(ArcadiaSigns_1.Supp_MUTCD) AS
CountOfSupp_MUTCD
FROM ArcadiaSigns AS ArcadiaSigns_1
WHERE ArcadiaSigns_1.Ownership<>"STATE" and ArcadiaSigns_1.Supp_MUTCD Is
Not Null
GROUP BY ArcadiaSigns_1.Supp_MUTCD;

I get this (there are 57 rows and it looks as if its still picking up the
null values):

Sign CountOfMUTCD
306
Double W2-3 1
I12-1 1
I2-12 1
I3-1 4
I3-1 40
I3-1, don't know 1
I3-2 6
I3-2 58
I3-2, I3-1 1
I3-2, I3-2 1
M13-13, M13-41, M13-14, M13-42 1
M3-1, M3-1 1
P1-16 11
R10-1 4
R1-1 73
R1-3 7
R1-3, I3-1 1
R2-1 49
R2-2 8
R3-32 1
W1-1 6
W1-10 1
W1-11 1
W1-13 13
W1-13 19
W1-14 4
W1-14 27
W1-2 3
W1-3 29
W1-4 27
W1-6 2
W1-7 10
W1-8 8
W1-9 1
W2-1 20
W2-15 13
W2-18 13
W2-2 30
W2-3 30
W2-4 1
W4-1 2
W5-11 2
W5-13 1
W5-14 9
W5-16 19
W5-3 1
W5-9 10
W6-4 5
W7-10 4
W7-10 8
W7-12 4
W7-13 4
W7-7 2
W9-1 73
W9-1, W7-10 1
W9-1, W7-19 1

 

[Guest]

When I said "Post your data for me" I meant from your table - like
ArcadiaSigns. I want to run the query on the data to see what is going wrong
as it runs fine on sample I created.

 

[Guest]

Ah.
The table has 40 fields and 514 rows. So as all of that isn't going to fit
here I'm hoping that a sample of the fields in question will be ok. All of
the other data is either numeric or text.

SignID MUTCD Supp_MUTCD
ARC-225 -0.655 W1-8
ARC-225 -0.605 W1-4 W9-1
ARC-225 -0.582 W2-2
ARC-225 -0.320 W1-8
ARC-225 -0.015 R2-2
ARC-234 -0.000 R1-1
ARC-234 -0.030 M3-1 M13-51
ARC-234 -0.160 W5-14
ARC-234 -0.350 W1-7 W9-1
ARC-234 -0.405 W5-14
ARC-234 -0.565 W5-14
ARC-234 -0.545 I3-2 I3-1
ARC-234 -0.575 W2-2
ARC-234 -0.600 W1-3
ARC-234 -0.890 W1-4
ARC-234 -1.085 R2-2
ARC-234 -1.280 R2-1
ARC-234 -1.337 W2-2
ARC-234 -1.414 R1-1
ARC-234 -1.440 R2-1
ARC-234 -1.535 W2-3
ARC-234 -1.840 R2-1
ARC-234 -2.275 W2-3
ARC-234 -2.297 I3-2 I3-1
ARC-234 -2.410 W2-2
ARC-234 -2.455 W6-4 W7-10
ARC-234 -2.540 W1-4 W9-1
ARC-234 -2.635 W1-14 W1-13
ARC-234 -2.650 W1-14 W1-13
ARC-234 -2.655 R1-1
ARC-234 -2.714 W1-14 W1-13
ARC-234 -2.741 W2-3
ARC-234 -2.825 W6-4 W7-10
ARC-235 -0.000 R1-1
ARC-235 -0.063 W7-10
ARC-235 -0.390 W2-3 W5-16
ARC-235 -0.630 R1-1
ARC-235 -0.640 R1-1
ARC-235 -0.923 W1-3 W9-1

 

[Guest]

Try this --
SELECT ArcadiaSigns.MUTCD AS Sign, Count(ArcadiaSigns.MUTCD) AS CountOfMUTCD
FROM ArcadiaSigns
WHERE ArcadiaSigns.Ownership<>"STATE" OR ArcadiaSigns.Ownership Is Null
GROUP BY ArcadiaSigns.MUTCD

UNION SELECT ArcadiaSigns_1.Supp_MUTCD, Count(ArcadiaSigns_1.Supp_MUTCD) AS
CountOfSupp_MUTCD
FROM ArcadiaSigns AS ArcadiaSigns_1
WHERE (ArcadiaSigns_1.Ownership<>"STATE" OR ArcadiaSigns_1.Ownership Is
Null) AND (ArcadiaSigns_1.Supp_MUTCD <>"") AND (ArcadiaSigns_1.Supp_MUTCD Is
Not Null)
GROUP BY ArcadiaSigns_1.Supp_MUTCD;

 

[Guest]

No change in the output other than the null values are no longer counted
(thats good).
I'm still trying out different things here but I may have to come up with
another idea for a summary report. But if you have anything more to add that
would be great if not, I truly appreciate all your efforts, I'm learning
quite a bit.

~Sara

 

[Guest]

I would recommend using a single table instead of one per city. Just add a
field for the city and append all into one table. Use city criteria.