Sort and Count 1

[awesomeBA]

Good Morning!-

I need to create a query or report that will sort a table by building_code and return/display the total. The table doesn't currently have a numeric field to use to count.

 

[lespaul]

could you tell us a little bit about the table structure, maybe some sample records and expected output of your query?

Leslie

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

 

[awesomeBA]

Let me explain. I have two tables that I would like to compare. Table1 and Table2 both contain the same data , but for the different apps.The building_code is the relationship

I'd like to

1) Group or sort each table by building_code.
2) Obtain a building count(total) for each building_code in both tables.
3) Compare the total for each building_code in Table1 to the total of the same building_code in Table2.

Building_code Table1Ct Table2Ct Variance
11011 10 14 +4


Hope this helps

 

[PHV]

Something like this ?
SELECT Building_code, Sum(IIf(tn='T1',1,0)) AS Table1Ct, Sum(IIf(tn='T2',1,0)) AS Table2Ct, (Sum(IIf(tn='T2',1,0)) - Sum(IIf(tn='T1',1,0))) AS [Variance]
FROM (
SELECT Building_code, 'T1' AS tn FROM Table1
UNION ALL SELECT Building_code, 'T2' FROM Table2
) AS U
GROUP BY Building_code;

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

 

[lespaul]

Ok, try this:

SELECT Building_Code, Count(A.*) As Table1Ct, Count(B.*) As Table2Ct, Count(B.*) - Count(A.*) As Variance From TableName As A
INNER JOIN TableName As B ON A.Building_Code = B.BuildingCode
GROUP BY Building_Code

(Although I'm sure PHV will post a much more elegant and probably more functional SQL right after this!)

Leslie

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

 

[awesomeBA]

I am going to give them both a whirl. I will keep you posted

Thanks!

 

[awesomeBA]

Beautiful!!!!! Thank you both so much.