Using a table in a query multiple times.

[vgillis]

Hi all.

I have a question.


I have a table...lets call it name1.

I have another table called Restictions. This is a one to many relationship.

What I want to do is have a record based on criteria pulled from the restrictions table so that each critieria is in a seperate field.

This is what I have..

SELECT Restriction.Restriction, Name1.Name1, Restriction_1.Restriction, Restriction_2.Restriction, Restriction_3.Restriction
FROM (((Name1 LEFT JOIN Restriction ON Name1.ID = Restriction.[Id Number]) LEFT JOIN Restriction AS Restriction_1 ON Name1.ID = Restriction_1.[Id Number]) INNER JOIN Restriction AS Restriction_2 ON Name1.ID = Restriction_2.[Id Number]) LEFT JOIN Restriction AS Restriction_3 ON Name1.ID = Restriction_3.[Id Number]
WHERE (((Restriction.Restriction)="NOC") AND ((Restriction_1.Restriction)="AEM") AND ((Restriction_2.Restriction)="AMC") AND ((Restriction_3.Restriction)="APC"));

So I always want the name record. and only the restriction record that meets the criteria. As it is, it only brings me a record if there is a restriction record for every value. Which is not always the case.
If i change the ANDs to ORs..I get strange results.

Any help would be appreicated!

Thanks.
Veronica

[June7]

Name1.ID is the foreign key field for Restriction.[ID Number]?

You have an INNER JOIN which means data must be in both tables.

Not sure duplicate joins needed.

SELECT Name1, IIf([Restriction]="NOC",[Restriction],Null) As ResNOC, IIf([Restriction]="AEM",[Restriction],Null) As ResAEM, IIf([Restriction]="AMC",[Restriction],Null) As ResAMC, IIf([Restriction]="APC",[Restriction],Null) As ResAPC FROM Name1 LEFT JOIN Restriction ON Name1.ID = Restriction.[ID Number] WHERE Restriction = "NOC" OR Restriction = "AEM" OR Restriction = "AMC" OR Restriction = "APC";

This technique 'pivots' the data without CROSSTAB query. Of course, CROSSTAB is an available option.