A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Building Composit key on the fly, Boy Scout Data Base



 
 
Thread Tools Display Modes
  #1  
Old March 26th, 2010, 09:40 PM posted to microsoft.public.access.tablesdbdesign
Dick Patton
external usenet poster
 
Posts: 14
Default Building Composit key on the fly, Boy Scout Data Base

Hi all,

I have two tables each is unique. One is the "Merit Badge" tabel the other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.

I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no way
of concatination in access or where to place the code, but it must be built
before update.

So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event calle
"before UPdate" that did not work!

It seems like such a simple thig to do!

Help

Dick
  #2  
Old March 26th, 2010, 10:29 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Building Composit key on the fly, Boy Scout Data Base

On Fri, 26 Mar 2010 13:40:01 -0700, Dick Patton
wrote:

Hi all,

I have two tables each is unique. One is the "Merit Badge" tabel the other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.

I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no way
of concatination in access or where to place the code, but it must be built
before update.

So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event calle
"before UPdate" that did not work!

It seems like such a simple thig to do!


It is simpler than you're making it, and you certainly do not need to
construct a new field to do it.

Instead, open the link table in design view. ctrl-click the Person ID and the
Merit Bage fields so they're both selected (darkened). Click the key icon to
make these two fields a joint, two-field Primary Key; this will require that
every record be unique for the combination, even though each field by itself
can have duplicates.

If you already have a primary key, you can instead use the Indexes tool on the
toolbar to create a unique twofield index. Put some distinctive index name in
the left column, and the person ID next to it; put the MB Code in the second
column just below the person ID, and check the "unique" checkbox.
--

John W. Vinson [MVP]
  #3  
Old March 26th, 2010, 11:38 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Building Composit key on the fly, Boy Scout Data Base

Dick

I'm not sure I understand your situation, what it is that you are trying to
use Access to do.

One possible scenario for what you've described might be:

* You have people
* You have merit badges
* You have people-with-meritbadge(s)

If that's your situation, I think you need to be looking at three tables
instead of two.

More information, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Dick Patton" wrote in message
...
Hi all,

I have two tables each is unique. One is the "Merit Badge" tabel the
other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That
works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.

I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no
way
of concatination in access or where to place the code, but it must be
built
before update.

So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event
calle
"before UPdate" that did not work!

It seems like such a simple thig to do!

Help

Dick



  #4  
Old March 27th, 2010, 12:27 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Building Composit key on the fly, Boy Scout Data Base

When entering data into a database, you have to rely on the data entry
person at some point!

You need these tables:
TblPerson
PersonID
person fields

TblMeritBadge
MeritBadgeID
MeritBadgeName

TblMeritBadgeAward
MeritBadgeAwardID
PersonID
MeritBadgeID
DateMeritBadgeAwarded

You need a form/subform for entering data. Base the main form on TblPerson.
Base the subform on TblMeritBadgeAward. Set the LinkMaster and LinkChild
properties to PersonID. Use a combobox in the subform to enter MeritBadgeID.
Your form/subform will display a single person and a list of merit badges
awarded to that person. You can sort the merit badges in alphabetical order.
Now all you need do is rely on the data entery person not to enter a
duplicate merit badge for any person.

Steve



"Dick Patton" wrote in message
...
Hi all,

I have two tables each is unique. One is the "Merit Badge" tabel the
other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That
works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.

I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no
way
of concatination in access or where to place the code, but it must be
built
before update.

So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event
calle
"before UPdate" that did not work!

It seems like such a simple thig to do!

Help

Dick



  #5  
Old March 27th, 2010, 01:38 AM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Building Composit key on the fly, Boy Scout Data Base

"Steve" wrote in message
...

You need a form/subform for entering data. Base the main form on
TblPerson. Base the subform on TblMeritBadgeAward. Set the LinkMaster and
LinkChild properties to PersonID. Use a combobox in the subform to enter
MeritBadgeID. Your form/subform will display a single person and a list of
merit badges awarded to that person. You can sort the merit badges in
alphabetical order. Now all you need do is rely on the data entery person
not to enter a duplicate merit badge for any person.

Steve



You forgot the part about chastising the poster for his table design and
that only you can help him --- for a fee.

John...

  #6  
Old March 27th, 2010, 01:52 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Building Composit key on the fly, Boy Scout Data Base

On Fri, 26 Mar 2010 19:27:58 -0400, "Steve" wrote:

Now all you need do is rely on the data entery person not to enter a
duplicate merit badge for any person.


Or you can use Access as it is designed... with a unique index on the two
fields. You're not enhancing your reputation any, Steve.
--

John W. Vinson [MVP]
  #7  
Old March 28th, 2010, 10:36 PM posted to microsoft.public.access.tablesdbdesign
Dick Patton
external usenet poster
 
Posts: 14
Default Building Composit key on the fly, Boy Scout Data Base

John,

Yes, Yes, that worked perfectly. It was simpler than i thought.

Rule: Use the DBMS to do the work when ever you can!

Thank loads....

"John W. Vinson" wrote:

On Fri, 26 Mar 2010 13:40:01 -0700, Dick Patton
wrote:

Hi all,

I have two tables each is unique. One is the "Merit Badge" tabel the other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.

I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no way
of concatination in access or where to place the code, but it must be built
before update.

So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event calle
"before UPdate" that did not work!

It seems like such a simple thig to do!


It is simpler than you're making it, and you certainly do not need to
construct a new field to do it.

Instead, open the link table in design view. ctrl-click the Person ID and the
Merit Bage fields so they're both selected (darkened). Click the key icon to
make these two fields a joint, two-field Primary Key; this will require that
every record be unique for the combination, even though each field by itself
can have duplicates.

If you already have a primary key, you can instead use the Indexes tool on the
toolbar to create a unique twofield index. Put some distinctive index name in
the left column, and the person ID next to it; put the MB Code in the second
column just below the person ID, and check the "unique" checkbox.
--

John W. Vinson [MVP]
.

  #8  
Old March 28th, 2010, 10:40 PM posted to microsoft.public.access.tablesdbdesign
Dick Patton
external usenet poster
 
Posts: 14
Default Building Composit key on the fly, Boy Scout Data Base

Thank you, Jeff i am sorry i did not communicate well. There are 3 table and
MB-Link forms the retionship between the merit badge and the councilor but
both are one to many relationships so i neede3d a way to store only those
that are unique combinations and John's suggestion worked perfectly. I just
wish i could trap the error and give a used friendly message like "this
record already exists".

"Jeff Boyce" wrote:

Dick

I'm not sure I understand your situation, what it is that you are trying to
use Access to do.

One possible scenario for what you've described might be:

* You have people
* You have merit badges
* You have people-with-meritbadge(s)

If that's your situation, I think you need to be looking at three tables
instead of two.

More information, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Dick Patton" wrote in message
...
Hi all,

I have two tables each is unique. One is the "Merit Badge" tabel the
other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That
works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.

I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no
way
of concatination in access or where to place the code, but it must be
built
before update.

So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event
calle
"before UPdate" that did not work!

It seems like such a simple thig to do!

Help

Dick



.

  #9  
Old March 29th, 2010, 05:25 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Building Composit key on the fly, Boy Scout Data Base

On Sun, 28 Mar 2010 14:36:01 -0700, Dick Patton
wrote:

Rule: Use the DBMS to do the work when ever you can!


g You've sure got that right!!!
--

John W. Vinson [MVP]
  #10  
Old March 31st, 2010, 06:02 PM posted to microsoft.public.access.tablesdbdesign
J_Goddard via AccessMonster.com
external usenet poster
 
Posts: 221
Default Building Composit key on the fly, Boy Scout Data Base

Sorry Stevie - WRONG answer!!!

One of the things we try to do as developers is to anticipate users' mistakes
and prevent or correct them. In this case, detecting a duplicate entry of a
merit badge is a trivial task - any any decent developer would include it,
and not "rely on the data entery person not to enter a duplicate merit
badge for any person."


Small wonder you have to grovel here for work, if that's the type of advice
you give.



Steve wrote:
When entering data into a database, you have to rely on the data entry
person at some point!

You need these tables:
TblPerson
PersonID
person fields

TblMeritBadge
MeritBadgeID
MeritBadgeName

TblMeritBadgeAward
MeritBadgeAwardID
PersonID
MeritBadgeID
DateMeritBadgeAwarded

You need a form/subform for entering data. Base the main form on TblPerson.
Base the subform on TblMeritBadgeAward. Set the LinkMaster and LinkChild
properties to PersonID. Use a combobox in the subform to enter MeritBadgeID.
Your form/subform will display a single person and a list of merit badges
awarded to that person. You can sort the merit badges in alphabetical order.
Now all you need do is rely on the data entery person not to enter a
duplicate merit badge for any person.

Steve


Hi all,

[quoted text clipped - 23 lines]

Dick


--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.