Composite primary key to other table as foreign key?

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-

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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-

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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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-

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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" wrote:

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" wrote in message
...
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).

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" wrote in message
...
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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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-

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-




"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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" wrote:

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" wrote in message
...
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).

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

-Kim-




"Pat Hartman" wrote:

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" wrote in message
...
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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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-

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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-




"Allen Browne" wrote:

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

"Kim Weiss" wrote in message
...
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" wrote:

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" wrote in message
...
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).

"Pat Hartman" wrote:

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" wrote in message
...
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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kim Weiss" wrote in message
...
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-



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.

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

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.
--

John W. Vinson [MVP]