Do I need another table

[Harold Druss]

Hi All
I'm using VB6 as a front end for an Access DB.
The program allows couriers to log their daily jobs.
I currently have two tables:
tblLocations
tblJobs

The Jobs table fields are:

DateOfJob
Tracking#
ProofOfDelivery
TimeDelivered
PickLocationName
PickLocationStreet
PickLocationCity
PickLocationState
PickLocationZIP
PickLocationID
DropLocationName
DropLocationStreet
DropLocationCity
DropLocationState
DropLocationZIP
DropLocationID

The Locations table has the Name, Street, etc.

I only have one table because a location can be either a "Pick" or a "Drop".
I use two duplicate combo boxes for the pick and the drop.
I would like to have a relationship between the PickLocationID and the
Location table and the same for the DropLocationID
That way I would only have the PickLocationID and the DropLocationID in the
Jobs table.

Do I need to create two tables (one for the Pick locations, one for the Drop
Locations)?

Thank you
Harold

 

[Rick Brandt]

Hi All
I'm using VB6 as a front end for an Access DB.
The program allows couriers to log their daily jobs.
I currently have two tables:
tblLocations
tblJobs

The Jobs table fields are:

DateOfJob
Tracking#
ProofOfDelivery
TimeDelivered
PickLocationName
PickLocationStreet
PickLocationCity
PickLocationState
PickLocationZIP
PickLocationID
DropLocationName
DropLocationStreet
DropLocationCity
DropLocationState
DropLocationZIP
DropLocationID

The Locations table has the Name, Street, etc.

I only have one table because a location can be either a "Pick" or a
"Drop". I use two duplicate combo boxes for the pick and the drop.
I would like to have a relationship between the PickLocationID and the
Location table and the same for the DropLocationID
That way I would only have the PickLocationID and the DropLocationID
in the Jobs table.

Do I need to create two tables (one for the Pick locations, one for
the Drop Locations)?

Thank you
Harold

No. The fact that you have two foreign keys pointing to one table is not a
problem at all. Just create two relationships and then get rid of those
redundant location fields in the jobs table.

 

[Harold Druss]

No. The fact that you have two foreign keys pointing to one table is not
a problem at all. Just create two relationships and then get rid of those
redundant location fields in the jobs table.

Hi Rick

I tried that before and could not get it to work.
After you assuring me it does work, I tried again, and you are right.
I'm not sure what I was doing wrong before.

Thank you very much for your help.
Harold