Multiple Records from Multiple Fields

[Rob B.]

I have a membership table (tAllRecs), with each member assigned a unique
number (fMbrNo).
I have an event table (tWalks), with each event assigned a unique number
(fWalkNo).
Each event has 7 leaders (fLdr1, fLdr2,…), and 7 assistant leaders (fALdr1,
ALdr2,…). The fLdr and fALdr fields contain the tAllRecs.fMbrNo for each of
the 14 total leaders and assistants.
For each event (record) in tWalks, I have to print data that exists in
tAllRecs for each leader and assistant (address, phone, etc.).
This is where I am confused, because what I need to do is look up 14 fields
in tWalks, and print the corresponding data that exists in tAllRecs for each
of the leaders and assistants. In other words, each of the 14 fields of
interest in a single record of tWalks points to 14 separate records in
tAllRecs.
Any thoughts how to do this? I am likely making this more difficult than
necessary. Thanks for any insight.

Rob B.

 

[Pieter Wijnen]

You have a normalization problem
there ought to be a seperate join table for Leaders assigned to Walks (With
a Boolean Field To seperate leader "types")
I Suggest, if you *own* the db to do that

MbrWalks
--------
fMbrNo (FK)
fWalkNo (FK)
IsLeader Boolean

HtH

Pieter

 

[Pieter Wijnen]

You can however get the data you're after by using a Series of UNION Queries
SELECT fWalkNo, fldr1, -1 As IsLeader FROM tWalks
UNION ALL
SELECT fWalkNo, fldr2, -1 FROM tWalks
UNION ALL
SELECT fWalkNo, fldr3, -1 FROM tWalks
.....
UNION ALL
SELECT fWalkNo, FAldr7,0 FROM tWalks

Which also can be used to create the join table ...

HtH

Pieter

 

[Rob B.]

I would likely better understand your response if I better understood Access
and SQL. Thank you for trying, though!

The task of transposing 14 fields in one record to 14 separate records seems
more difficult than I thought. For my purposes, there is no need to
distinguish between Leaders and Assistants, so I have not used the ISLEADER
Boolean field as you suggest. This is what I did

SELECT fWalkNo, fLdr1 FROM tblWalks
UNION ALL
SELECT fWalkNo, fLdr12 FROM tblWalks
UNION ALL
SELECT fWalkNo, fLdr13 FROM tblWalks
UNION ALL;

When I try the below query, Access tells me it is an, "Invalid SQL
statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE", so I am
still stuck. Are you trying to tell me to create 14 separate UNION queries?

 

[John W. Vinson]

The task of transposing 14 fields in one record to 14 separate records seems
more difficult than I thought. For my purposes, there is no need to
distinguish between Leaders and Assistants, so I have not used the ISLEADER
Boolean field as you suggest. This is what I did

SELECT fWalkNo, fLdr1 FROM tblWalks
UNION ALL
SELECT fWalkNo, fLdr12 FROM tblWalks
UNION ALL
SELECT fWalkNo, fLdr13 FROM tblWalks
UNION ALL;

When I try the below query, Access tells me it is an, "Invalid SQL
statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE", so I am
still stuck. Are you trying to tell me to create 14 separate UNION queries?

The problem here is the final UNION ALL;

You're saying to UNION the thirteen queries above with... nothing at all. It's
like an English sentence "I'd like vanilla ice cream and peaches and chocolate
sauce and." It's ungrammatical!

Just remove the last UNION ALL, or add the fourteenth field name.

John W. Vinson [MVP]

 

[Rob B.]

<slapping self on side of head>

Duh!

Thanks. Everything is working as expected, now.


Rob B.

 

[Pieter Wijnen]

de nada,
sql isn't that difficult, but not that easy either

Pieter