By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,376 Members | 3,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,376 IT Pros & Developers. It's quick & easy.

Self Join

P: 1
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.
Jul 25 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,638
  1. I do believe the correct output would be:
    Expand|Select|Wrap|Line Numbers
    1. Unit    Job    FemaleScore    MaleScore
    2. GA      1A          5             4
    3. NC      5B          2
    4. NC      5C                        8
    5.  
  2. My solution is not pretty but it does work. Assuming your Table Name is tblData:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblData.Unit, tblData.Job, fCalculateFemale([Unit],[Job]) AS FemaleScore, 
    2. fCalculateMale([Unit],[Job]) AS MaleScore
    3. FROM tblData
    4. GROUP BY tblData.Unit, tblData.Job, 
    5. fCalculateFemale([Unit],[Job]), fCalculateMale([Unit],[Job]);
    6.  
  3. The Function definitions are:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalculateFemale(varFUnit, varFJob)
    2. fCalculateFemale = DLookup("[Score]", "tblData", "[Unit] = '" & varFUnit & "' And [Job] = '" & _
    3.                         varFJob & "' And [Group] = 'Female'")
    4. End Function
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalculateMale(varMUnit, varMJob)
    2. fCalculateMale = DLookup("[Score]", "tblData", "[Unit] = '" & varMUnit & "' And [Job] = '" & _
    3.                         varMJob & "' And [Group] = 'Male'")
    4. 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.
Jul 26 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi. This is one of those rare occasions when an Access Crosstab query does the job well.

Access SQL:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM CLng(Nz(Sum([Score]),0)) AS TScore
  2. SELECT Unit.Unit, Unit.Job
  3. FROM Unit
  4. GROUP BY Unit.Unit, Unit.Job
  5. PIVOT Unit.Group;
Output:
Expand|Select|Wrap|Line Numbers
  1. Unit Job Female Male
  2. GA 1A 5 4
  3. NC 5B 2 0
  4. 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!
Jul 26 '08 #3

ADezii
Expert 5K+
P: 8,638
Hi. This is one of those rare occasions when an Access Crosstab query does the job well.

Access SQL:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM CLng(Nz(Sum([Score]),0)) AS TScore
  2. SELECT Unit.Unit, Unit.Job
  3. FROM Unit
  4. GROUP BY Unit.Unit, Unit.Job
  5. PIVOT Unit.Group;
Output:
Expand|Select|Wrap|Line Numbers
  1. Unit Job Female Male
  2. GA 1A 5 4
  3. NC 5B 2 0
  4. 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).
Jul 26 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.