Union Query Count Distinct

Hi,

Without knowing all teh livestock types beforehand this would be difficult, normal SQL does not support this - but access does,

If you create a new query and use the cross-tab query wizard this will ask you for your source query,

Point the query to the above that you have mentiond,

Follow the links to put the pasturename and livestocktypedescription as the row and column source respectivley and this will give you a dynamic query presenting the informatin as formatted above - without teh Total line

If you want a total then put the resulting cross-tab query as the recordsource for a report and then include the total at the end of the data provided

I wouldn't think the livestock types would need to be known since only those types brought up by the recordset would be used.  I there were for instance 6 types but the query only had 2, then only those 2 types would have column headings.

Hi,

You do not need to know the types if you use transform (the cross tab wizard)

Otherwise you can not do this without using sql. Ie Above you could do

Select
   t1.PastureName,
   sum(iif(t1.LivestockTypeDescription = "Bull", 1, 0)) As Bull,
   sum(iif(t1.LivestockTypeDescription = "Cow", 1, 0)) As Cow,
   sum(iif(t1.LivestockTypeDescription = "Heifer", 1, 0)) As Heifer,
   sum(iif(t1.LivestockTypeDescription = "Steer", 1, 0)) As Steer,
   count(*) as Total
From
(
SELECT tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID
) as t1
Group By
   t1.PastureName


But if you had a new type (kangaroo) for example then this would appear in the total but the individual breakup would not be shown.

You could - as you are mentioning a recordset, dynamically create the SQL as above, else use tranform - it will create all but the total column for you.

Firstly create a union query:

SELECT tkpPasture.PastureName As Location, tkpLivestockType.LivestockTypeDescription
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock
ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID)
ON tkpPasture.PastureID = tblLivestock.PastureID
UNION
SELECT "Total Cattle" As Location, tkpLivestockType.LivestockTypeDescription
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock
ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID)
ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName;

Then use a crosstab query like this:

TRANSFORM Count(tkpLivestockType.LivestockTypeDescription) AS CountOfLivestock
SELECT tkpPasture.PastureName As Location, tkpLivestockType.LivestockTypeDescription
FROM Query_1_Name GROUP BY tkpPasture.PastureName
PIVOT tkpLivestockType.LivestockTypeDescription

mpmccarthy,
thanks for jumpting in.
The first part, Union query is bringing up ".....does not include the specified expression 'LivestockTypeDescription' as part of an aggregate function.

Regardless, does the tranform work? as this is the only solution to your problem - I'd add the total column and at the bottom in a report, rather than in a query.

Sorry try this:

SELECT tkpPasture.PastureName As Location, tkpLivestockType.LivestockTypeDescription
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock
ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID)
ON tkpPasture.PastureID = tblLivestock.PastureID
UNION
SELECT "Total Cattle" As Location, tkpLivestockType.LivestockTypeDescription
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock
ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID)
ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription;

mpmcarthy,
now that I've got the union query saved how it incorporated into the crosstab? I've tried the wizard, selecting the new query and it said that I didn't have enough fields for the crosstab query.  Never used this type query before.  Thanks for your patience.

Open a new query.  Don't add any tables. Just go to the sql view and paste in the code.

NB - Replace 'Query_1_Name' with the name of the Union Query.

TRANSFORM Count(tkpLivestockType.LivestockTypeDescription) AS CountOfLivestock
SELECT tkpPasture.PastureName As Location, tkpLivestockType.LivestockTypeDescription
FROM Query_1_Name GROUP BY tkpPasture.PastureName
PIVOT tkpLivestockType.LivestockTypeDescription

now receiving .......does not recognize 'tkpPasture.PastureName' as a valid field name.
I've checked everything and the spelling seems ok everywhere?

Sorry I'm an idiot sometimes, try:

TRANSFORM Count(LivestockTypeDescription) AS CountOfLivestock
SELECT Location, LivestockTypeDescription
FROM Query_1_Name GROUP BY Location
PIVOT LivestockTypeDescription;

ok, this is what I'm getting.

Location             LivestockType        Bull         Cow       Heifer
Lot A                          Cow                             1             1
Lot B                           Bull               1             1             1
Total Cattle                 Bull                1             1             1

As you can see, it's still not quite right.  It's not totaling correctly and I don't want the second Column(LivestockType).

Try this:

SELECT tkpPasture.PastureName As Location, tkpLivestockType.LivestockTypeDescription
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock
ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID)
ON tkpPasture.PastureID = tblLivestock.PastureID
UNION
SELECT "Total Cattle" As Location, tkpLivestockType.LivestockTypeDescription
FROM tkpLivestockType;

Then:

TRANSFORM Count(LivestockTypeDescription) AS CountOfLivestock
SELECT Location
FROM Query_1_Name GROUP BY Location
PIVOT LivestockTypeDescription;

to be more exact the initial query results:
Location    LivestockDescription
Lot A                 Gelding
Lot B                 Bull
Lot A                 Cow
Lot A                 Heifer
Lot A                 Gelding
Lot B                 Cow
Lot A                Heifer
Lot B                Mare
Lot B                  Cow
Lot B                 Heifer
Lot A                 Heifer
Lot A                 Cow

Union Query Results
Location              LivestockTypeDescription
Lot A                      Cow
Lot A                      Gelding
Lot A                      Heifer
Lot B                      Cow
Lot B                      Cow
Lot B                      Mare
Total Cattle             Bull
Total Cattle             Cow
Total Cattle             Gelding
Total Cattle             Heifer
Total Cattle             Mare

CrossTab Query results:
Location             LivestockTypeDescription    Bull        Cow       Gelding       Heifer      Mare
Lot A                                 Cow                                1               1              1
Lot B                                  Bull                     1          1                               1            1
Total Cattle                        Bull                      1          1               1              1             1

Sorry this is so drawn out.  Don't forget I was also needing a total field column for each location as per my example.

You shouldn't still be getting LivestockTypeDescription in the crosstab as I removed it.  Anyway I've changed it all again. We'll get there eventually.


ANOTHER QUERY BEFORE THE UNION QUERY:

SELECT tkpPasture.PastureName As Location, tkpLivestockType.LivestockTypeDescription, Count(tkpLivestockType.LivestockTypeDescription) as CountStock
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock
ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID)
ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName , tkpLivestockType.LivestockTypeDescription;

UNION QUERY:
Change MyQuery to name of previous query.

SELECT *
FROM MyQuery
UNION
SELECT "Total Cattle" As Location, Count(tkpLivestockType.LivestockTypeDescription) as CountStock, tkpLivestockType.LivestockTypeDescription
FROM tkpLivestockType
GROUP BY Location, tkpLivestockType.LivestockTypeDescription;

CROSSTAB QUERY:
Change UnionQueryName to whatever.

TRANSFORM Sum(LivestockTypeDescription) AS CountOfLivestock
SELECT Location
FROM UnionQueryName GROUP BY Location
PIVOT LivestockTypeDescription;

Yeah, we're getting there.  

The corrected queries:
Union query brought up 'Enter parameter value' box Location

3rd query when ran:  ...does not recognize 'Location' as valid field name .....

Try using single instead of double quotes around Total Cattle:

SELECT *
FROM MyQuery
UNION
SELECT 'Total Cattle' As Location, Count(tkpLivestockType.LivestockTypeDescription) as CountStock, tkpLivestockType.LivestockTypeDescription
FROM tkpLivestockType
GROUP BY Location, tkpLivestockType.LivestockTypeDescription;

Made change and still receiving same error:
3rd query when ran:  ...does not recognize 'Location' as valid field name .....

What have you named your second (union) query and when you run it what are the field names showing on the datasheet?

These are the three queries
qry1
SELECT tkpPasture.PastureName AS Location, tkpLivestockType.LivestockTypeDescription, Count(tkpLivestockType.LivestockTypeDescription) AS CountStock
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription;

qry2
SELECT *
FROM qry1
UNION SELECT 'Total Cattle' As Location, Count(tkpLivestockType.LivestockTypeDescription) as CountStock, tkpLivestockType.LivestockTypeDescription
FROM tkpLivestockType
GROUP BY Location, tkpLivestockType.LivestockTypeDescription;

qry3
TRANSFORM Sum(LivestockTypeDescription) AS CountOfLivestock
SELECT Location
FROM qry2
GROUP BY Location
PIVOT LivestockTypeDescription;

                please let me know if I've got this wrong.

Sorry Access is having problems with the alias's try these:

qry1
SELECT tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription, Count(tkpLivestockType.LivestockTypeDescription)
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription;

qry2
SELECT *
FROM qry1
UNION SELECT 'Total Cattle', tkpLivestockType.LivestockTypeDescription, Count(tkpLivestockType.LivestockTypeDescription)
FROM tkpLivestockType
GROUP BY tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription;


qry3
TRANSFORM Sum(CountOfLivestockTypeDescription) AS CountOfLivestock
SELECT PastureName
FROM qry2
GROUP BY PastureName
PIVOT CountOfLivestockTypeDescription;

Sorry, similar problems:

qry2 asks for parameter value - ...not recognize 'tkpPasture.PastureName' .....

qry3 - database not recognize 'tkpPasture.PastureName' as valid field name...

qry2
SELECT *
FROM qry1
UNION SELECT 'Total Cattle', LivestockTypeDescription, Count(LivestockTypeDescription)
FROM tkpLivestockType
GROUP BY PastureName, LivestockTypeDescription;

qry3 stays the same:

qry3
TRANSFORM Sum(CountOfLivestockTypeDescription) AS CountOfLivestock
SELECT PastureName
FROM qry2
GROUP BY PastureName
PIVOT CountOfLivestockTypeDescription;

Same messages for both queries except now PastureName

qry2
SELECT *
FROM qry1
UNION SELECT 'Total Cattle' As PastureName, LivestockTypeDescription, Count(LivestockTypeDescription)
FROM tkpLivestockType
GROUP BY LivestockTypeDescription;

Any good???

qry2 ok, but qry3 gives .....not recognize 'CountOfLivestockTypeDescription'......

try:

qry3
TRANSFORM Sum(CountOfLivestockTypeDescription)
SELECT PastureName
FROM qry2
GROUP BY PastureName
PIVOT LivestockTypeDescription;

CountOfLivestockTypeDescription   not recognized

Run qry2 in datasheet view.  What fieldname is displayed for the last field?

CountStock

try:

qry3
TRANSFORM Sum(CountStock)
SELECT PastureName
FROM qry2
GROUP BY PastureName
PIVOT LivestockTypeDescription;

PastureName   not valid field name

Run qry2 again.  What are all the field names displayed?

Location, LivestockTypeDescription and CountStock

Fingers crossed, this might be it:

qry3
TRANSFORM Sum(CountStock)
SELECT Location
FROM qry2
GROUP BY Location
PIVOT LivestockTypeDescription;

Sorry,  Total Cattle is showing 1 for each field.  Not adding  correct and the two fields that have no values is still showing a total of 1 where a 0 should be

qry1
SELECT tkpPasture.PastureName AS Location, tkpLivestockType.LivestockTypeDescription, Count(tkpLivestockType.LivestockTypeDescription) AS CountStock
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName, tkpLivestockType.LivestockTypeDescription;

qry2
SELECT *
FROM qry1
UNION SELECT 'Total Cattle' As Location, LivestockTypeDescription, Count(LivestockTypeDescription) AS CountStock
FROM tkpLivestockType
GROUP BY LivestockTypeDescription;

qry3
TRANSFORM Sum(CountStock) AS CountOfStock
SELECT Location
FROM qry2
GROUP BY Location
PIVOT LivestockTypeDescription;

Replace all three queries just to be sure

No change

Hang on I've requested assistance

Z, You causing problems again? Don't they know cowboys don't make good coders? lol

You have this database zipped somewhere? Will it fit on EE?

Otherwise...a couple of questions.

Looking back on the initial Q,

qry results:

PastureName              LivestockTypeDescription
   Main                         bull
   Main                                     bull
   Main                                     heifer
   East                                      Steer
   East                                      Steer
   East                                      Cow

  **********  End result to be:  ********

Location             Bull               Cow         Heifer         Steer          Total
   Main                 2                   0               1              0       3
   East                  0                   1               0              2                3
-----------         --------         --------        --------       --------       --------
Total Cattle          2                   1               1               2                6


how do you want this data presented? you have some kind of form? A crosstab won't total two ways...just one. So this has to be coded. I'd be happy to help you do that, I'll need to either create your query results in my test DB or if you can get your database to upload, then do it there.

For now, I'm going to cut and paste in your vertical data and start playing with making it horizontal.

J

Thnks Jeff

I wasn't even looking at the opposite total.  It was hard enough trying to total in one direction.

zubin

Just out of curiousity what do you get in results if you run the following query on its own.

SELECT 'Total Cattle' As Location, LivestockTypeDescription, Count(LivestockTypeDescription) AS CountStock
FROM tkpLivestockType
GROUP BY LivestockTypeDescription;

Based on the results of your base query....I made it into a table so I could query it. Here's the horizontal base

SELECT tkpPasture.Location, Sum(IIf([type]="Cow",1,0)) AS Cow, Sum(IIf([type]="Bull",1,0)) AS Bull, Sum(IIf([type]="Heifer",1,0)) AS Heifer, [cow]+[bull]+[heifer] AS Total
FROM tkpPasture
GROUP BY tkpPasture.Location;

Location      Cow      Bull      Heifer      Total
Lot A                     2      0      3      5
Lot B                     2      1      1      4

Working on the last total

Here's the last total

SELECT "" AS TotalCattle, Sum([Cow]) AS _____, Sum([Bull]) AS ______, Sum([Heifer]) AS ____, Sum([Total]) AS _______
FROM Cattle1;

Looks like

TotalCattle      _____      ______      ____      _______
                     4             1      4             9

Depending on how you want to present this data (won't be one query), you can use the SQL to populate two datasheets on a form, one for tally, one for totals. OR two subreports on a main.

Let me know if you'd rather do this with code.
J

Jeff

You're my hero.  I'd been trying to answer this for so long I got locked into the wrong method completely.

Mary

I like this one better

SELECT "Total Cattle" AS [-], Sum([Cow]) AS [- -], Sum([Bull]) AS [- - -], Sum([Heifer]) AS [- - - -], Sum([Total]) AS [- - - - -]
FROM Cattle1;

   -                 - -           - - -      - - - -     - - - - -    
Total Cattle      4        1           4                9

J

Mary,
No problem....I've been working with a client that's an accountant...and I've done nothing but horizontal totaling for the last 6 weeks...something about end of FY and profit and loss....you know, accounting stuff. So this is very fresh on my poor tired brain. Happy to help


Hey Z....when you get a chance, get back with us. And Mary, dear Mary deserves the poinks here. She worked her lil hiney off!
:o)

J

Boy, this is getting real interesting/confusing.  

I came up with this which is giving me totals on each location which is good but I'm still needing the As RowType total for the columns.

TRANSFORM CLng(Nz(Count(tkpLivestockType.LivestockTypeDescription),0)) AS CountStock
SELECT tkpPasture.PastureName AS Location, Count(tkpLivestockType.LivestockTypeDescription) AS [Total Livestock]
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName
PIVOT tkpLivestockType.LivestockTypeDescription;

I'll work more on this and suggestions tomorrow

Thanks Jeff

But I honestly don't mind sharing.  If you hadn't jumped in I could still be here this time next week.  I hate to leave someone hung up on a problem.

I'm in the opposite position to you.  One of my clients has a legacy system thats being changed next year for very good reason.  It's diabolical and I spend so much time piecing together adhoc queries for them that my SQL is getting better ....  sometimes.

Mary

jeff,
        Nice to see you once again.  I've uploaded the database and also a jpg which shows what I'm after (which can be done in).  I purchased a program and am tryng to customize it into another application.  don't believe there is any violation there?
         https://filedb.experts-exchange.com/incoming/ee-stuff/1221-New-Compressed--zipped--Folder.zip

TRANSFORM CLng(Nz(Count(tkpLivestockType.LivestockTypeDescription),0)) AS CountStock
SELECT tkpPasture.PastureName AS Location, Count(tkpLivestockType.LivestockTypeDescription) AS [Total Livestock]
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID
UNION SELECT 'Total Cattle' As Location, Count(tkpLivestockType.LivestockTypeDescription) AS [Total Livestock]
FROM tkpPasture INNER JOIN (tkpLivestockType INNER JOIN tblLivestock ON tkpLivestockType.LivestockTypeID = tblLivestock.LivestockTypeID) ON tkpPasture.PastureID = tblLivestock.PastureID
GROUP BY tkpPasture.PastureName
PIVOT tkpLivestockType.LivestockTypeDescription;

receiving:
missing operator in expression 'tkpPasture.PastureID = tblLivestock.PastureID Union Select 'Total Cattle' as Location'

That's a cool idea Mary, Hadn't thought about a Union.

not recognizing 'Location' as valid field name.  Were you able to open my db upload?

mpmcarthy, jefftwiley:
I appreciate all the work done.  Was really hoping that something 'simple' would have solved it.  As you can see from the jpg I uploaded, it has/can be done.  Sorry, just didn't get the answers here.
Has to be a better way.  Jeff: If I add another Livestock Description then I would have to go back and 'touch up' the queries which is not the way to go? Enough time has been spent on this.  

Thanks.