Multiple fields in one table link to one field in another table

[Amy E. Baggott]

Our online application allows exhibitors to request up to three separate
booths with up to six choices for each (a total of 18 possible choices). The
web site exports the application as a single record with the choices marked
as "Booth 1 Choice1", "Booth 1 Choice2", etc. Now my boss wants me to give
him a report showing the dimensions and sqare feet of each exhibitor's
choices. I have a table for the booth inventory that includes that data, but
I don't know how to get it to link each choice to the booth table to get me
the information for that booth number. Is there any way to do it short of
some sort of massive union query?

 

[KC-Mass]

Hi Amy,

I would not be thinking of a Union query, more probably a simple join.
Could
you help us by giving the structure of the tables and the fields common one
to another.

Maybe then we can help you.

Regards

Kevin

 

[Amy E. Baggott]

The relevant fields in the application table are
Booth 1 Width Booth 1 Depth Booth 1 Sq Ft Booth 1 Call Me Booth 1
Choice1 Booth 1 Choice2 Booth 1 Choice3 Booth 1 Choice4 Booth 1 Choice5 Booth
1 Choice6 Booth 2 Width Booth 2 Depth Booth 2 Sq Ft Booth 2 Call Me Booth 2
Choice1 Booth 2 Choice2 Booth 2 Choice3 Booth 2 Choice4 Booth 2 Choice5 Booth
2 Choice6 Booth 3 Width Booth 3 Depth Booth 3 Sq Ft Booth 3 Call Me Booth 3
Choice1 Booth 3 Choice2 Booth 3 Choice3 Booth 3 Choice4 Booth 3 Choice5 Booth
3 Choice6 ShowID

The fields in the Booth Table are
Booth Number Width Depth SqFt ShowID

The two tables need to join on each of the booth choice fields plus the
ShowID in the Main Application table to the Booth Number and ShowID in the
Booth Table.

The last time I tried to join multiple fields at one time to a single field
in the other table, it only pulled records where all the choices were the
same.

--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

Hi Amy,

I would not be thinking of a Union query, more probably a simple join.
Could
you help us by giving the structure of the tables and the fields common one
to another.

Maybe then we can help you.

Regards

Kevin

 

[John Spencer]

You can probably do it

SELECT A.[Booth 1 Choice], B.*
A.[Booth 2 Choice], B2.*
A.[Booth 3 Choice], B3.*
A.[Booth 4 Choice], B4.*
FROM (((Application as A LEFT JOIN Booth as B
ON A.[Booth 1 Choice]= B.[Booth Number]
AND A.ShowID = B.ShowID)
LEFT JOIN Booth as B2
ON A.[Booth 2 Choice]= B2.[Booth Number]
AND A.ShowID = B2.ShowID)
LEFT JOIN Booth as B3
ON A.[Booth 3 Choice]= B3.[Booth Number]
AND A.ShowID = B3.ShowID)
LEFT JOIN Booth as B4
ON A.[Booth 3 Choice]= B4.[Booth Number]
AND A.ShowID = B4.ShowID

In the query grid you add the Booth table 18 times and link from the
application table to the showId on each of the 18 tables and then link
from the various booth choice fields to the Booth number field in the
booth table.

THEN you double click on on each of the join lines and set the to show
all in the application table and matching in booth table.

Of course this solution is just as ugly as using a UNION query to
mormalize the Application table data and then joining to the Booth table.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================