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  

Creating Relationships



 
 
Thread Tools Display Modes
  #1  
Old April 2nd, 2010, 05:17 PM posted to microsoft.public.access.tablesdbdesign
Lindsay
external usenet poster
 
Posts: 76
Default Creating Relationships

I am a VERY novice Access user and attempting to build my company's first
database. I've created three tables to track our consultants:
Consultant Contact information: ID # name, phone number, address, email,
country of residence
Consultant Forms: ID #, name, CV, Biodata, LOC
Consultant Experience: ID#, name, sector, region, comments, languages

Ideally, I would like to be able to create a form with information from all
three tables into one form, but this hasn't worked each time I use the form
wizard to generate it. I can generate a form for each table but not the
tables collectively. I currently have the relationship as one to many with
the Consultant contact info as the parent table and the other 2 as the
children. I wanted to link them from Consultant ID # to name in the children
tables. I can't figure out why the relationship doesn't work and the form
can't be generated. One thing I did notice was that the Consultant ID #s are
inconsistent between tables despite referring to the same person. The parent
table starts with an ID # of 2 and I can't change it to one. Could this be
the trouble? I'd also like to be able to create searches by name, sector, or
language. Any help would be most appreciated.
  #2  
Old April 2nd, 2010, 06:28 PM posted to microsoft.public.access.tablesdbdesign
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Creating Relationships

Lindsay -

You need to fix your table structure before you go any further. Also, do
not use the # symbol in the field name - it will cause you problems later.
In fact, don't use any special characters other than the underscore to be
safe. You can change the fieldnames in the existing tables - you don't need
to start from scratch. You may want to keep the name field in the two
related tables until you are sure the data is correct, and then remove them.
Assuming there can be many experiences for each consultant and many forms for
each consultant, then start with something like this:

tblConsultants:
ConsultantID Autonumber, primary key
ConsultantName
PhoneNumber
Address1
Address2
City
State
PostalCode
Country
Email

tblConsultantForms:
FormID Autonumber, primary key
ConsultantID Long Integer, foreign key - relates to tblConsultants
ConsultantID
CV
Biodata
LOC

tblConsultantExperience:
ExperienceID Autonumber, primary key
ConsultantID Long Integer, foreign key - relates to tblConsultants
ConsultantID
Sector
Region
Comments
Languages

Once the tables are set, check the foreign keys. The ConsultantID numbers
in the two related tables should be the ConsultantID numbers that match the
names in the tblConsultants. Note that the ConsultantID in the two related
tables should be Long Integers, not Autonumber. This will allow you to
update those field before removing the Name field from the two related tables.

Once this is correct, then you should be able to create one form for each
table, then put the two 'related-table' forms on the main Consultant form as
subforms, related by ConsultantID.

If you have problems once you get this far, post the issues and the table
structures again.

--
Daryl S


"Lindsay" wrote:

I am a VERY novice Access user and attempting to build my company's first
database. I've created three tables to track our consultants:
Consultant Contact information: ID # name, phone number, address, email,
country of residence
Consultant Forms: ID #, name, CV, Biodata, LOC
Consultant Experience: ID#, name, sector, region, comments, languages

Ideally, I would like to be able to create a form with information from all
three tables into one form, but this hasn't worked each time I use the form
wizard to generate it. I can generate a form for each table but not the
tables collectively. I currently have the relationship as one to many with
the Consultant contact info as the parent table and the other 2 as the
children. I wanted to link them from Consultant ID # to name in the children
tables. I can't figure out why the relationship doesn't work and the form
can't be generated. One thing I did notice was that the Consultant ID #s are
inconsistent between tables despite referring to the same person. The parent
table starts with an ID # of 2 and I can't change it to one. Could this be
the trouble? I'd also like to be able to create searches by name, sector, or
language. Any help would be most appreciated.

  #3  
Old April 2nd, 2010, 06:47 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Creating Relationships

Lindsay

One more observation...

If you use [Name] or even [ConsultantName], by implication you are storing a
"full name" (e.g., John H. Smith). Your situation may be one of the rare
ones ... but more commonly, sooner or later, someone will want to have a
list of "full names", sorted by last name.

Ahem! You don't have a last name. You have a "full name". How do you get
the "last name" portion of the "full name". Sorry, but that's not very
easy!

It's much easier to separate out FName, MName and LName fields from the
beginning, rather than try to get there from a full name field.

Good luck!

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.

"Lindsay" wrote in message
...
I am a VERY novice Access user and attempting to build my company's first
database. I've created three tables to track our consultants:
Consultant Contact information: ID # name, phone number, address, email,
country of residence
Consultant Forms: ID #, name, CV, Biodata, LOC
Consultant Experience: ID#, name, sector, region, comments, languages

Ideally, I would like to be able to create a form with information from
all
three tables into one form, but this hasn't worked each time I use the
form
wizard to generate it. I can generate a form for each table but not the
tables collectively. I currently have the relationship as one to many with
the Consultant contact info as the parent table and the other 2 as the
children. I wanted to link them from Consultant ID # to name in the
children
tables. I can't figure out why the relationship doesn't work and the form
can't be generated. One thing I did notice was that the Consultant ID #s
are
inconsistent between tables despite referring to the same person. The
parent
table starts with an ID # of 2 and I can't change it to one. Could this be
the trouble? I'd also like to be able to create searches by name, sector,
or
language. Any help would be most appreciated.



  #4  
Old April 2nd, 2010, 07:37 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Creating Relationships

NICE job with the tables!!!!

In tblConsultantForms and tblConsultantExperience you only need one
ConsultantID field. You show two - must have happened when you were copying
to create your message.

Steve



"Daryl S" wrote in message
...
Lindsay -

You need to fix your table structure before you go any further. Also, do
not use the # symbol in the field name - it will cause you problems later.
In fact, don't use any special characters other than the underscore to be
safe. You can change the fieldnames in the existing tables - you don't
need
to start from scratch. You may want to keep the name field in the two
related tables until you are sure the data is correct, and then remove
them.
Assuming there can be many experiences for each consultant and many forms
for
each consultant, then start with something like this:

tblConsultants:
ConsultantID Autonumber, primary key
ConsultantName
PhoneNumber
Address1
Address2
City
State
PostalCode
Country
Email

tblConsultantForms:
FormID Autonumber, primary key
ConsultantID Long Integer, foreign key - relates to tblConsultants
ConsultantID
CV
Biodata
LOC

tblConsultantExperience:
ExperienceID Autonumber, primary key
ConsultantID Long Integer, foreign key - relates to tblConsultants
ConsultantID
Sector
Region
Comments
Languages

Once the tables are set, check the foreign keys. The ConsultantID numbers
in the two related tables should be the ConsultantID numbers that match
the
names in the tblConsultants. Note that the ConsultantID in the two
related
tables should be Long Integers, not Autonumber. This will allow you to
update those field before removing the Name field from the two related
tables.

Once this is correct, then you should be able to create one form for each
table, then put the two 'related-table' forms on the main Consultant form
as
subforms, related by ConsultantID.

If you have problems once you get this far, post the issues and the table
structures again.

--
Daryl S


"Lindsay" wrote:

I am a VERY novice Access user and attempting to build my company's first
database. I've created three tables to track our consultants:
Consultant Contact information: ID # name, phone number, address, email,
country of residence
Consultant Forms: ID #, name, CV, Biodata, LOC
Consultant Experience: ID#, name, sector, region, comments, languages

Ideally, I would like to be able to create a form with information from
all
three tables into one form, but this hasn't worked each time I use the
form
wizard to generate it. I can generate a form for each table but not the
tables collectively. I currently have the relationship as one to many
with
the Consultant contact info as the parent table and the other 2 as the
children. I wanted to link them from Consultant ID # to name in the
children
tables. I can't figure out why the relationship doesn't work and the form
can't be generated. One thing I did notice was that the Consultant ID #s
are
inconsistent between tables despite referring to the same person. The
parent
table starts with an ID # of 2 and I can't change it to one. Could this
be
the trouble? I'd also like to be able to create searches by name, sector,
or
language. Any help would be most appreciated.



  #5  
Old April 5th, 2010, 04:11 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Creating Relationships

I expect it is word wrapping in your newsreader (and mine too).

I expect it was intended to note that ConsultantID relates to ConsultantID in
tblConsultants. Parentheses might have been used thus:

ConsultantID (relates to tblConsultants ConsultantID)


Steve wrote:
NICE job with the tables!!!!

In tblConsultantForms and tblConsultantExperience you only need one
ConsultantID field. You show two - must have happened when you were copying
to create your message.

Steve


Lindsay -

[quoted text clipped - 83 lines]
or
language. Any help would be most appreciated.


--
Message posted via http://www.accessmonster.com

  #6  
Old April 5th, 2010, 05:23 PM posted to microsoft.public.access.tablesdbdesign
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Creating Relationships

Yes, that is just a word wrap, only one ConsultantID per table...

--
Daryl S


"BruceM via AccessMonster.com" wrote:

I expect it is word wrapping in your newsreader (and mine too).

I expect it was intended to note that ConsultantID relates to ConsultantID in
tblConsultants. Parentheses might have been used thus:

ConsultantID (relates to tblConsultants ConsultantID)


Steve wrote:
NICE job with the tables!!!!

In tblConsultantForms and tblConsultantExperience you only need one
ConsultantID field. You show two - must have happened when you were copying
to create your message.

Steve


Lindsay -

[quoted text clipped - 83 lines]
or
language. Any help would be most appreciated.


--
Message posted via http://www.accessmonster.com

.

  #7  
Old April 5th, 2010, 07:05 PM posted to microsoft.public.access.tablesdbdesign
Lindsay
external usenet poster
 
Posts: 76
Default Creating Relationships

Thanks for the help. I'll see what happens. As for the names, I already did
separate the name into last, first, and middle initial

"Jeff Boyce" wrote:

Lindsay

One more observation...

If you use [Name] or even [ConsultantName], by implication you are storing a
"full name" (e.g., John H. Smith). Your situation may be one of the rare
ones ... but more commonly, sooner or later, someone will want to have a
list of "full names", sorted by last name.

Ahem! You don't have a last name. You have a "full name". How do you get
the "last name" portion of the "full name". Sorry, but that's not very
easy!

It's much easier to separate out FName, MName and LName fields from the
beginning, rather than try to get there from a full name field.

Good luck!

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.

"Lindsay" wrote in message
...
I am a VERY novice Access user and attempting to build my company's first
database. I've created three tables to track our consultants:
Consultant Contact information: ID # name, phone number, address, email,
country of residence
Consultant Forms: ID #, name, CV, Biodata, LOC
Consultant Experience: ID#, name, sector, region, comments, languages

Ideally, I would like to be able to create a form with information from
all
three tables into one form, but this hasn't worked each time I use the
form
wizard to generate it. I can generate a form for each table but not the
tables collectively. I currently have the relationship as one to many with
the Consultant contact info as the parent table and the other 2 as the
children. I wanted to link them from Consultant ID # to name in the
children
tables. I can't figure out why the relationship doesn't work and the form
can't be generated. One thing I did notice was that the Consultant ID #s
are
inconsistent between tables despite referring to the same person. The
parent
table starts with an ID # of 2 and I can't change it to one. Could this be
the trouble? I'd also like to be able to create searches by name, sector,
or
language. Any help would be most appreciated.



.

 




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 09:30 AM.


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