Combine 2 tables with no direct criteria to link

[lewds]

Here is what I'M trying to do:
tblWorkers. fldWorkerID,fldCrewID,fldShift
data W1 A 1
W2 B 2
Combine W3 A 3
tblMstrSChed. fldDate,fldShift1,fldShift2,fldShift3
data 02 X D N
03 X D N
Result 04 D X N
tblACrewSched. fldDate,W1,W3
data 02 X N
03 X N
04 D N

 

[r937]

please give better examples, those make no sense

it's not clear what you want


rudy

 

[lewds]

Thank You for taking the time to look at this. If you have an E-mail address, I,ll sent you an Excel drawing with a better layout. My E-mail is lewds@charter.net

 

[lewds]

i reverse-engineered it into access

the query you want is

TRANSFORM Min(IIf(Shift=1,Shift1,IIf(Shift=2,Shift2,Shift3))) AS ShiftX
SELECT qryMasterSchedule.theDate
FROM qryMasterSchedule, qryCrew
GROUP BY qryMasterSchedule.theDate
PIVOT qryCrew.WorkerID;

i tested it and it works

Although rudy and i worked this out in the background with email. i posted his reply . This code works after modification with my data.
rudy is ACES,
My Thanks Again.