Composite primary key to other table as foreign key?

[Kim Weiss]

I am beginner with db's so this maybe very simple question or misunderstood
by me but let's try.

I am trying create db to record my customer jobs and car trips.

I have three tables
ClientTBL, -ClientID (pk) (no problem)
WorkTBL, (-WorkID), - ClientID (fk)(pk), - Startdate(pk)
TripTBL, -TripID (pk), -ClientStartdate (fk) (<-this is the WorkTBL
composite pk)

Idea is to bring ClientId as fk to WorkTBL as identify client. Then make
composite pk to WorkTBL combining ClientID and Startdate. And bring this
composite pk to TripTBL as fk. And why? This way I could make dropdown box to
TripTBL to choose work-client combination which trip is made for. Both
client and startdate is shown in one cell. Would be nice

I can make composite pk of ClientID and Startdate but I can't bring it to
TripTBL as fk. Is it because there is date and number, should it be number
and number? Is it even possible?

Am I missing something? Any ideas? I coundn't find single page concerning
this kind of case, and believe me I seached it for hours. (I'm using Access
2007 btw).

Thanks!

-Kim-

 

[Allen Browne]

After creating the tables, and setting the primary keys, open the
Relationships Window (Database Tools tab of the ribbon.)

In the Relationships Window, drag the ClientID field from WorkTBL, and drop
it on the ClientID field in TripTBL. Access opens the Create Relation
dialog. The first row of the dialog matches the ClientID fields, and there
are other rows below that. On the 2nd row of the dialog, match the Startdate
field in WorkTBL to the StartDate field in TripTBL.

When you Ok the dialog, Access creates the 2-field relation, and you see 2
lines between the 2 tables.

 

[Kim Weiss]

Thank you, it works that far! But... When I make combobox in TripTBL for
select composite Client-Startdate, it only shows me 1-1 cells. First is
ClientID and second? Well I don't know. Maybe number of Startdate record. But
still not showing client and date.

To WorkTBL it brings ClientName nicely, no problem...

-Kim-

 

[Allen Browne]

You are creating 2 separate fields in the related table aren't you?
In table design that's:
ClientID Number
StartDate Date/time

 

[Pat Hartman]

Although you can use a multi-field unique identifier with a combo, it
requires coding. The simpler solution is to change the current composite PK
to a unique index and add an autonumber to serve as the PK. That will fix
your problem with the combo. But it does create a visual issue since the
combo will show only a single field once it is closed. One solution is to
concatenate the fields you want to show in the closed combo so that they are
all mushed together as a single field for display purposes.

 

[Kim Weiss]

Yes exactly. And I thought that they are combinated somehow, but when I
change ClientID field in TripTBL, it leaves StartDate field empty. Although
it accepts only valid startdates from WorkTBL to use in TripTBL StartDate.
There is some connection

Maybe if I make form of WorkTBL to choose work and subform of TripTBL to
change trip details.

It's nice, it wasn't so easy

-Kim-

 

[Kim Weiss]

Quite new things there for me, have to try and study. Great to get new ideas,
thanks!

-Kim-

 

[Allen Browne]

I'm not sure about your expectation or context.

Just because you change the ClientID in the related table does not mean that
Access goes and finds a valid StartDate to match. There could be several
valid combinations of ClientID + StartDate.

But if you used a form, with a subform for TripTBL, with the
LinkMasterFields/LinkChildFields set to ClientID;StartDate, then when you
choose a record in the form and entered a new records in the subform, Access
would populate the ClientID and StartDate in the subform to match the
ClientID and StartDate in the main form.

 

[Nicholas]

Although you can use a multi-field unique identifier with a combo, it
requires coding. The simpler solution is to change the current composite PK
to a unique index and add an autonumber to serve as the PK. That will fix
your problem with the combo. But it does create a visual issue since the
combo will show only a single field once it is closed. One solution is to
concatenate the fields you want to show in the closed combo so that they are
all mushed together as a single field for display purposes.



Please show an example of how one can calculate a composit ID from two source fields in order to create a single primary key field that ID's a record. This would actually be so much easier if Access would allow Calculated Fields in a Table for this purpose. Example: I have a lookup table that contains Transaction Types ie: Debit Card Transactions, Standard Checks, Printed Checks Credit Cards: AMX Classic AMX Optima; Citibank Visa Acct-xxxx; Citibank Mastercard Acct-xxxx etc each of these has a transaction Number ie Printed Check # 245 or Standard Check 245 The lookup table creates codes for each of the Transaction Types ie: CKp CKs DCTc DCTd (d=debit c=credit) + The Transaction number ie: the check number or DateTime Code formatted as 20080415-1321 for a credit/debit transaction. If I could calculate these fields together it would create one field as the Primary ID that would read as follows: DCTd-20041215-1300 or CKp-2115 Please allow the use of Calculated fields it

makes lookups and linking to other tables much easier.

 

[John W. Vinson]

On Tue, 24 Jun 2008 13:03:18 -0700, Nicholas

Please show an example of how one can calculate a composit ID from two source
fields in order to create a single primary key field that ID's a record. This
would actually be so much easier if Access would allow Calculated Fields in a
Table for this purpose. Example: I have a lookup table that contains
Transaction Types ie: Debit Card Transactions, Standard Checks, Printed Checks
Credit Cards: AMX Classic AMX Optima; Citibank Visa Acct-xxxx; Citibank
Mastercard Acct-xxxx etc each of these has a transaction Number ie Printed
Check # 245 or Standard Check 245 The lookup table creates codes for each of
the Transaction Types ie: CKp CKs DCTc DCTd (d=debit c=credit) + The
Transaction number ie: the check number or DateTime Code formatted as
20080415-1321 for a credit/debit transaction. If I could calculate these
fields together it would create one field as the Primary ID that would read as
follows: DCTd-20041215-1300 or CKp-2115 Please allow the use of Calculated
fields it makes lookups and linking to other tables much easier.


This is a good case for a "surrogate key". You can, as noted in the thread,
use two (or ten, for that matter) fields in a Key (primary or foreign).

Storing data (a date say) in a composite primary key is A Bad Idea, in
general, though. I'd really treat the date of the transaction *AS DATA*, as a
date/time field on its own; the check number or CC account number is a
different kind of data, in a field of its own. You can create unique
multifield Indexes to prevent duplicates; using an Autonumber as a primary key
and a Long Integer as a foreign key (both concealed from user view) makes
structuring the database much easier, and the relationships will be fast and
efficient.

 

[Shabra Dowson]

Although you can use a multi-field unique identifier with a combo, it
requires coding. The simpler solution is to change the current composite PK
to a unique index and add an autonumber to serve as the PK. That will fix
your problem with the combo. But it does create a visual issue since the
combo will show only a single field once it is closed. One solution is to
concatenate the fields you want to show in the closed combo so that they are
all mushed together as a single field for display purposes.

Where is the best place to concatenate the fields for the closed combo
display? I could create a query that joins my two fields with a space in
between - and then they wouldn't be so mushed.

 

[Shabra Dowson]

Although you can use a multi-field unique identifier with a combo, it
requires coding. The simpler solution is to change the current composite PK
to a unique index and add an autonumber to serve as the PK. That will fix
your problem with the combo. But it does create a visual issue since the
combo will show only a single field once it is closed. One solution is to
concatenate the fields you want to show in the closed combo so that they are
all mushed together as a single field for display purposes.

Where is the best place to concatenate the fields for the closed combo
display? I could create a query that joins my two fields with a space in
between - and then they wouldn't be so mushed.

 

[Armen Stein]

On Wed, 20 May 2009 02:52:03 -0700, Shabra Dowson <Shabra

Where is the best place to concatenate the fields for the closed combo
display? I could create a query that joins my two fields with a space in
between - and then they wouldn't be so mushed.

The query is the right place. And when I say query, it can be either
a saved query or a query string right in the row source property. The
saved query is more reusable, if this combobox appears on other forms
too.

Just concatenate the fields that you want to display in the closed
combobox, and include formatting characters too, if you like. (spaces,
hyphens, parentheses, etc.)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

On Wed, 20 May 2009 02:52:03 -0700, Shabra Dowson <Shabra

Where is the best place to concatenate the fields for the closed combo
display? I could create a query that joins my two fields with a space in
between - and then they wouldn't be so mushed.

The query is the right place. And when I say query, it can be either
a saved query or a query string right in the row source property. The
saved query is more reusable, if this combobox appears on other forms
too.

Just concatenate the fields that you want to display in the closed
combobox, and include formatting characters too, if you like. (spaces,
hyphens, parentheses, etc.)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com