Looking Up a date in a key from another table

[pmk]

Hi all,
I have 2 tables with multiple field keys.
Table1 = ATTORNEY WITH PROGRAM DETAILS
fields = AttorneyCode (number)(key)
ProgSemCode (number)(key)
SeminarDate (number)(key)
BillingDate
Table2 = PRGRAM AND SEMINAR DETAILS
fields = ProgSemCode (number)(key)
SeminarDate (number)(key)
Trainer
City
Cost
Relationship between Tbl1 and Tbl2 = ProgSemCode and SeminarDate

I would like to have the SeminarDate field in Table1 lookup the SeminarDate field in Table2 based on a match between ProgSemCode between both tables. I've been trying to work this out for hours, and I can't seem to come up with a solution. Any ideas??? The lookup wizard states that I cannot use the data type of date for a lookup.
Thanks in advance,
Peter

 

[Darrylles]

Hi,

I realise that this is a relational database question, and that you may not have much knowledge of efficient database design.

Can you tell me why you have SeminarDate in your main table, and SeminarDate in your SEMINAR DETAILS table?
Your SEMINAR DETAILS table holds this value for a seminar.

I see this design:

ATTORNEY (table)
Attorney_PK (Primary key)
BillingDate (Date)


PROGRAM
Program_PK (Primary key)
Attorney_FK (Foreign key link to Attorney_PK)
Seminar_Date (Date field)
Trainer
City
Cost

In Access, link the Attorney_fk in the PROGRAM table to the
Attorney_PK in the ATTORNEY table.
The _PK fields in each table are Primary Keys.

You will have MANY PROGRAM records for each ATTORNEY.

I've a feeling that this won't do exactly what you want, but just post again.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."