|
|
|
|
|
| |
 Answer |
|
| |
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" <KimWeiss@discussions.microsoft.com> wrote in message
news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
>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-
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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" <KimWeiss@discussions.microsoft.com> wrote in message
> news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
> >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-
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
| |
Answer |
|
| |
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" <KimWeiss@discussions.microsoft.com> wrote in message
news:6CD5AE10-2A20-4646-BF80-569CBF7B58BA@microsoft.com...
> 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" <KimWeiss@discussions.microsoft.com> wrote in message
>> news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
>> >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).
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <KimWeiss@discussions.microsoft.com> wrote in message
> news:6CD5AE10-2A20-4646-BF80-569CBF7B58BA@microsoft.com...
> > 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" <KimWeiss@discussions.microsoft.com> wrote in message
> >> news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
> >> >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).
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <KimWeiss@discussions.microsoft.com> wrote in message
news:2500B095-1033-4C0B-8433-803EE7A9E31A@microsoft.com...
> 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" <KimWeiss@discussions.microsoft.com> wrote in message
>> news:6CD5AE10-2A20-4646-BF80-569CBF7B58BA@microsoft.com...
>> > 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" <KimWeiss@discussions.microsoft.com> wrote in message
>> >> news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
>> >> >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).
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
| |
Answer |
|
| |
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" <KimWeiss@discussions.microsoft.com> wrote in message
news:6CD5AE10-2A20-4646-BF80-569CBF7B58BA@microsoft.com...
> 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" <KimWeiss@discussions.microsoft.com> wrote in message
>> news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
>> >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-
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <KimWeiss@discussions.microsoft.com> wrote in message
> news:6CD5AE10-2A20-4646-BF80-569CBF7B58BA@microsoft.com...
> > 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" <KimWeiss@discussions.microsoft.com> wrote in message
> >> news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
> >> >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-
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
"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" <KimWeiss@discussions.microsoft.com> wrote in message
> news:6CD5AE10-2A20-4646-BF80-569CBF7B58BA@microsoft.com...
> > 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" <KimWeiss@discussions.microsoft.com> wrote in message
> >> news:9884728B-3257-4A19-B9D3-D3322D38396A@microsoft.com...
> >> >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. |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
On Tue, 24 Jun 2008 13:03:18 -0700, Nicholas
<Nicholas@discussions.microsoft.com> 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]
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
On Wed, 20 May 2009 02:52:03 -0700, Shabra Dowson <Shabra
Dowson@discussions.microsoft.com> 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.
>
>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
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Question
Notify me of replies