Self Join

[michaeltro]

I need help with an Access query. I have a table in access with stacked data in the following format:
ID Unit Job Group Score
1 GA 1A Female 5
2 GA 1A Male 4
3 NC 5B Female 2
4 NC 5C Male 8

I need the query output to be formatted as follows:
Unit Job FemaleScore MaleScore
GA 1A 5 4
NC 5B 2
NC 5C 8

I could do this in with Query Analyzer years ago by doing a self join similar to the query below, but I can't figure it out in Access.

Select F.Unit,F.Job, F.Group, F.Score, M.Score
from Unit as F left outer join Unit as M on F.ID = M.ID and F.Group = "Female" and M.Group="Male"

Any suggestions appreciated.

[ADezii]

1. I do believe the correct output would be:
   

   Unit    Job    FemaleScore    MaleScore
   GA      1A          5             4
   NC      5B          2
   NC      5C                        8

2. My solution is not pretty but it does work. Assuming your Table Name is tblData:
   

   SELECT tblData.Unit, tblData.Job, fCalculateFemale([Unit],[Job]) AS FemaleScore, 
   fCalculateMale([Unit],[Job]) AS MaleScore
   FROM tblData
   GROUP BY tblData.Unit, tblData.Job, 
   fCalculateFemale([Unit],[Job]), fCalculateMale([Unit],[Job]);

3. The Function definitions are:
   

   Public Function fCalculateFemale(varFUnit, varFJob)
   fCalculateFemale = DLookup("[Score]", "tblData", "[Unit] = '" & varFUnit & "' And [Job] = '" & _
                           varFJob & "' And [Group] = 'Female'")
   End Function

   Public Function fCalculateMale(varMUnit, varMJob)
   fCalculateMale = DLookup("[Score]", "tblData", "[Unit] = '" & varMUnit & "' And [Job] = '" & _
                           varMJob & "' And [Group] = 'Male'")
   End Function

4. Although it has been tested and works quite well, I'm sure the SQL Gang will come up with a better solution, so don't rely on this one too heavily.

[Stewart Ross]

Hi. This is one of those rare occasions when an Access Crosstab query does the job well.

Access SQL:

TRANSFORM CLng(Nz(Sum([Score]),0)) AS TScore
SELECT Unit.Unit, Unit.Job
FROM Unit
GROUP BY Unit.Unit, Unit.Job
PIVOT Unit.Group;

Output:

Unit Job Female Male
GA 1A 5 4
NC 5B 2 0
NC 5C 0 8

-Stewart

ps a self join as you suggest in post 1 would not work as it would exclude cases where there are all female or all male members in any one job group. In the 'this might have worked' SQL you suggested you would also have returned no rows, because you were self joining on the unique ID - where a row is either for a male or for a female but not both - then adding a condition for male = female which will never be true!

[ADezii]

Hi. This is one of those rare occasions when an Access Crosstab query does the job well.

Access SQL:

TRANSFORM CLng(Nz(Sum([Score]),0)) AS TScore
SELECT Unit.Unit, Unit.Job
FROM Unit
GROUP BY Unit.Unit, Unit.Job
PIVOT Unit.Group;

Output:

Unit Job Female Male
GA 1A 5 4
NC 5B 2 0
NC 5C 0 8

-Stewart

ps a self join as you suggest in post 1 would not work as it would exclude cases where there are all female or all male members in any one job group. In the 'this might have worked' SQL you suggested you would also have returned no rows, because you were self joining on the unique ID - where a row is either for a male or for a female but not both - then adding a condition for male = female which will never be true!

I just knew there was a much better, and more efficient, reply to this Thread (LOL).