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 john_wills
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
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
by john_wills
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.
by john_wills
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending