Discussion Locked

Numbers shouldn't add up

0 helpful
11 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a client table. Each client has two races, each race at two levels. I want to report the races, counting the lower-level race twice if need be when the higher-level race is counted only once. If, for instance, a client has one race European/Celtic and the other European/Germanic I want to count Celtic and Germanic once each but European once only, not twice; everything I have thought of yields a double count of European: the higher-level total insists on being the sum of the lower-level totals. The environment is Access 2000.

11 replies

by dklippert

Sounds like you need to have two tables in your Query. It's all right if they're the same table but with different names -- RaceMain ---RaceSub.
Then play with the relationships until the union matches what you're looking for.

by sl-campbell

You cannot do this in the Normal query because if the Areas (ie. European) count is 2 areas then 2 records would be returned, and if the Race count is 6 races then 6 records would be returned, so if you try to display them together, (4 columns: Areaname, Area count, Race Name, Race Count) the area columns would be repeated for each additional race record. Therefore you can only do this where the area and race columns are one and the same. Your only option is to use two queries.

I do not know if this will help but you could use a UNION query. Maybe you can expand on the idea (put this in the SQL window):

SELECT DISTINCT Area As Areas,"" As Races, Count(Area) AS Count
FROM RacesInAreas
Group By Area
UNION
SELECT "" As Areas,RaceAS Races,Count(Race) AS [Count]
FROM RacesInAreas
Group By Race;

OR

SELECT 'Area' AS Type, RacesInAreas.AREA AS Name, Count(RacesInAreas.Race) AS [Race Count]
FROM RacesInAreas
GROUP BY RacesInAreas.AREA
UNION
SELECT 'Race' AS Type,RacesInAreas.Race AS Name, Count(RacesInAreas.Race) AS [Race Count]
FROM RacesInAreas
GROUP BY RacesInAreas.Race

by sl-campbell

I made a mistake in the second query: The column/Field name for the Counts should be identical for both SELECTS. They both read in the above example as "Race Count" and the bothe should read "Count", or "Count Totals"

To test my examples easily, create a test table called RacesInAreas with 2 text fields: Area and Race.
Enter some data (I used 2 areas and 9 Race entries with 6 different races:

Area Race
---------------------
European Celtic
European Germanic
European OtherRace4
European Celtic
European Germanic
European Germanic
Asian OtherRace1
Asian OtherRace2
Asian OtherRace3

The results for the first query are:

Areas Races Count
---------------------------
Celtic 2
Germanic 3
OtherRace1 1
OtherRace2 1
OtherRace3 1
OtherRace4 1
Asian 3
European 6
================================
The results for the second query are:

Type Name Count
--------------------------
Area Asian 3
Area European 6
Race Celtic 2
Race Germanic 3
Race OtherRace1 1
Race OtherRace2 1
Race OtherRace3 1
Race OtherRace4 1

by sl-campbell

Could you post 3 or 4 pieces of sample data, in order to show the data is organized in the table and what the desired results should look like? (similar to what I did above)

by sl-campbell

John, Your going to have to create a DISTINCT query and then base a second query that does the count and group by on the first query:

Query1=
SELECT DISTINCT Region, PersName

This will return one record for each person and region (Region being European, etc)

Then, the second query uses the first one in the FROM section of the SELECT:

SELECT Region, Count(Region) AS Count
FROM Query1
GROUP BY Region

Another way would be to use SQL pass htrough if this is an option. Then you can put it all into one query:

(off the top of my head)

SELECT t1.Region, Count(t1.Region) AS COUNT
FROM (SELECT DISTINCT Region, PersName FROM MainTable) t1

by john_wills

I already have the UNION suggested by sl-campbell@gmx.de: that is what is giving me the numbers adding up when they shouldn't. As SQL is a set-theory-based language, it may be worth remarking that the sum I want for each higher-level race is [count of union] - [count of intersection], so perhaps the solution is to count the clients with the same higher-level race for both lower-level races. But I'm not sure where.

by john_wills

Omitting a lot, rows look like this
Smith 1: European/Celtic; 2: European/Germanic
Jones 1: African/Caribbean; 2: Asian/Japanese
Kamau 1: African/continental; 2:
Schmidt 1: European/Germanic; 2: Asian/Indian
This should give me totals
European: 2
African: 2
Asian: 2
but I get European: 3 because of Smith having both major races European.

by john_wills

After a month, I have worked the answer out myself. The method is to count the key(SSN in this case) separately for the major and minor races, thus:
SELECT Major, Minor, SSNMinor, SSNMajor
FROM [SELECT Major1 as Major, Minor1 as Minor, SSN AS SSNMinor,
SSN AS SSNMajor
FROM ClientWithLatestReferral
UNION
SELECT Major2 as Major, Minor2 as Minor,SSN AS SSNMinor, IIF(Major1=Major2,null,SSN) AS SSNMajor
FROM ClientWithLatestReferral
WHERE NOT(Major1=Major2 AND Minor1=Minor2)
AND Major2 is not null
AND Major2<>"" ]. AS UnionAlias;
The WHERE criteria are for problems with sloppy and incomplete input. In the report I have Count(SSNMinor) and Count(SSNMajor) at appropriate places. I have tested this method and it works.

This discussion is closed to new replies.