Self join assistance needed.

[eerich]

Hi everyone,

I need help with creating a double self join. I am attempting to pull together a security record where there are multiple security types and I need them to all layout in one row. I'm able to get a single self join to work perfectly, however in attempting to get the double self join I can't get the expected results. An example of expected output is:

UserID || Program || Type1 || Type2 || Type3
Bart || Check Print || 4 || 2 || B
Mike || Check Print || 4 || <null> || B
Bob || Check Print || 4 || 2 || <null>

Select Distinct F1.UserID, F1.Program, F1.Type1, F2.Type1 AS Type2, 
F3.Type1 AS Type3
From (FxnSec as F1 Inner Join FxnSec as F2 on (F1.UserID = F2.UserID AND 
F1.Program = F2.Program)) Inner Join FxnSec as F3 ON (F1.UserID = 
F3.UserID AND F1.Program = F3.Program)
Where (F1.Type1 = '4' AND F2.Type1 = '2') OR (F1.Type1 = '4' AND F3.Type1 = 'B')

The above does not give expected output as it generates two records instead of just one line as expected. 

One additional detail. There will need to always be at least two conditions met, and, in some cases all three conditions will be met, i.e., security types exists for each column. I believe that is the part that I can't figure out how to code correctly. 

Any help is appreciated.
Thanks.

 

[dhookom]

Have you considered using a crosstab query?

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Duane,

I've not written one of those before so wouldn't quite know how to set that up based on my criteria. Can you share any ideas?

 

[eerich]

Ok, I've created a crosstab using the Wizard to see what it does. This is not the solution to my problem because I'm not trying to aggregate anything. I simply want to show the security types assigned to each person by program. In the past someone has posted on the double self join; however I cannot find it via the search. I believe it was PHV who posted a solution at some point.

Any help would be much appreciated.

 

[dhookom]

Creating a crosstab doesn't have to make use of aggregates. How far did you get when creating the crosstab?

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

I've tweaked the cross tab query and it certainly has given me additional ideas on working with this data. I've removed the aggregate and that seems to work by just showing me a '1' in every column where the security type has been applied.

As I'd like to add other criteria and build on this, can that be done? Should I use the WHERE clause to specify the criteria like normal? Or is there a way to structure the double self-join as I originally hoped.

Thanks for your help.

 

[dhookom]

You can use a criteria in a crosstab. You may need to declare the data types of parameters.

Duane
Hook'D on Access
MS Access MVP