Relationship

[Steven]

I have two tables- main table is tblEnquiry with
EnquiryID as Primary key, autonumber and several fields.
Second table is tblContact with Primary key COntactID,
and Contact Type adn EnquiryID fields. Both tables linked
through EnquiryID. I use a cbo based on tblContact for
the user to enter information into the 'Contact Type'
field in tblEnquiry. We would like to produce a monthly
report of Contact Type vs. Date of Enquiry.
However, we have a problem with, we think, the
relationship between the two tables. When we enter
information in 'Contact Type' field in tblContact, we get
the fault message saying the table cannot be updated
because there is no accompanying record in tblEnquiry.
Hope I have provided enough information and it is
understandable.
Many thanks.

 

[Guest]

Can one contact have many Enquiries?
Can one Enquiry have many contacts?

The way you're describing the current table relationship, it's the later.

What kind of information is in the Enquiry table?

 

[Steven]

It is the former - one contact can have many enquiries.
the scenario is that our English Language school wishes
to monitor the types and number of enquiries made re.
potential student enrollments. Therefore, within
tblEnquiry it is mainly the enquirer's personal
particulars, date of enquiry and type of enquiry (mode of
approach - telephone, email etc).
hope this helps.

 

[Guest]

Then you need to set up your tables a little differently.

tblContacts
ContactID
FirstName
LastName, etc....

tblEnquiry
EnquiryID
ContactID
EnquiryDate, etc.....

In the relationships window, drag the ContactId from the contacts table to the enquiry table. Build your main form on the contact table and have a subform based on the enquiry table.

You might also consider having a table for EnquiryTypes - allows room for expansion without opening the tables properties. Have a combo on the subform for selecting the type.

hth

 

[Guest]

I was re-reading your first post, and noticed that ContactType is a field in the contacts table. Is that correct? If so, I suggest a separate lookup table for contact types in addition to your other tables:

tlkpContactType
ContactTypeID
TypeDescription

 

[John Vinson]

I have two tables- main table is tblEnquiry with
EnquiryID as Primary key, autonumber and several fields.
Second table is tblContact with Primary key COntactID,
and Contact Type adn EnquiryID fields. Both tables linked
through EnquiryID.

This is a one to many relationship, where each Enquiry is related to
(zero, one or many) Contacts - does that reflect reality?

I use a cbo based on tblContact for
the user to enter information into the 'Contact Type'
field in tblEnquiry.

There cannot possibly be a Contact Type in Enquiry; since each Enquiry
is related to many Contacts, a given Enquiry will have multiple
contact types!

I strongly suspect that you have your relationship backwards. Does one
Contact have many Enquiries, or vice versa?

Note that table datasheets are *not* ideal for data entry and viewing;
and that Microsoft's "Lookup Wizard" misfeature is a major source of
confusion. See http://www.mvps.org/access/lookupfields.htm for a
critique.

 

[Steven]

Thanks for your patience and reply.
I don't understand why you would recommend splitting the
information into two tables, and a third one for the cbo.
The information to be included in the form is very
straightforward and I thought keeping it all in one table
would simplify the process. The other table is for the
cbo and basically is a mechanism for getting the 'Contact
Type' info into the main table 'Contact Type' field.
Could you explain how using three tables and using a
subform on the main form will fix up the relationship
problem (if this is the problem)?
thanks

-----Original Message-----
Then you need to set up your tables a little differently.

tblContacts
ContactID
FirstName
LastName, etc....

tblEnquiry
EnquiryID
ContactID
EnquiryDate, etc.....

In the relationships window, drag the ContactId from the

contacts table to the enquiry table. Build your main
form on the contact table and have a subform based on the
enquiry table.

You might also consider having a table for EnquiryTypes -

allows room for expansion without opening the tables
properties. Have a combo on the subform for selecting
the type.

 

[Guest]

RPW
thanks for your continued help on this. It is a much
bigger issue than I first thought. I read your referenced
article on normalization although it was difficult for me
to understand everything the author states.
My problem is trying to improve a database with almost 4k
entries which have been all entered in one table. I
thought I would be able to simply cut and paste fields
into new tables but this isn't working.
Basically, what I am trying to do is tblEnquiry which
contains all the enqirer's personal details.
tblEnquiryDate - EnqiryID, EnquiryDateID, EnquiryTypeID
and EnquiryDate.
tblEnquiryType - EnquiryID, EnquiryDateID, EnquiryTypeID
and EnquiryType.
As per your advice, I create a subform based on
tblEnquiryDate, included on main form which is based on
tblEnquiry. Therefore, the subform has only one entry
field - Date of Enquiry. A cbo based on tblEnquiryType is
included on the main form. But I cannot resolve this
relationship problem. Is it possible to cut the existing
information (4,000 entries) and paste into the new table
format after the relationships have been established?
thanks for anymore advice.

-----Original Message-----
Hmmm.... I'm starting to understand what you've got in

your tables now. Typically, 'contact' information is the
personal details (name, address, etc) and Inquiries are
things that the contacts do. You have all of the
personal details in your Enquiry table and are trying to
link a list of contacttypes to the table.

Well, sorry to inform you, but you're going about this

in a way that will continually cause you problems. Your
tables are not "Normalized". Here's a link to an article
(that Jeff Conrad here had included in one of my requests
for help) about normalization. I suggest that you take
the time to read and understand it thoroughly before you
spend to much more time building forms and such.

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

From what I've gleened from your posts so far you have

up to four separate subjects: Contacts (the people
details), Enquiries (the enquiry details), ContactType
(you haven't described this or I missed it - what is it?
a classification of the people - teacher, student?), and
EnquiryType (the method of contact - telephone, email,
etc.)

Now, that's the breakdown of the subjects. How the

tables are structured is not the same as how you view the
information. All of the information in the four tables
I've suggested can be viewed on one form that has a sub-
form and two combo boxes.

Continue on this thread if you have more questions - as

you can see by John Vinson's posting, there are more
people that are willing to assist and guide you in the
right direction so that your application works.

 

[Guest]

Yeah, it took me a couple of reads through it before it started to really sink in. The other thing that helped me was finding posts for table design help and trying to figure out thier situation and then comparing to what the experts posted.

So now onto your problem....

I'd like to suggest you change the name of tblEnquiry to tblEnquirer because the table holds the details of the (person) enquirer, right?

Then I'd like to suggest that you change the name of tblEnquiryDate to tblEnquiry. Because on Enquirer may make several Enquiry's

So with those changes, here's the table structure:

tblEnquirer
EnquirerID 'autonumber PK
FirstName
LastName
(other fields with those personal details.....)

tblEnquiry
EnquiryID 'autonumber PK
EnquirerID FK
EnquiryTypeID
EnquiryDate
(other fields that describe the enquiry - time, subject, classes?)

tblEnquiryType
EnquiryTypeID
EnqTypDesc 'short for Enquiry Type Description...

In the relationships window, drag the EnquirerID field from the tblEnquirer to the EnquirerID field of the tblEnquiry. Enforce referential integrity. Then drag the EnquiryTypeID field from tblEnquiryType to the EnquiryTypeID field of tblEnquiry and make your settings for referential integrity.

Notice that the EnquiryType does not have the EnquirerID or the EnquiryID. Those are not a part of the subject of Enquiry Types.

The cbo for EnquiryType should be a part of the subform since it is helping to define each individual Enquiry and does nothing to define the Enquirer.

After you set things up this way, then YES there is a way to take the information from the existing table and paste it into the new tables. It's done by using an Append query.

 

[Steven]

rpw
thanks again
I have done everything you mention below, except
establish the relationships. Access will not allow me to
establish referential integrity between the tables as you
have described below. I'm sure I'm missing something very
simple here.
further advice appreciated.

-----Original Message-----
Yeah, it took me a couple of reads through it before it

started to really sink in. The other thing that helped
me was finding posts for table design help and trying to
figure out thier situation and then comparing to what the
experts posted.

So now onto your problem....

I'd like to suggest you change the name of tblEnquiry to

tblEnquirer because the table holds the details of the
(person) enquirer, right?

Then I'd like to suggest that you change the name of

tblEnquiryDate to tblEnquiry. Because on Enquirer may
make several Enquiry's

So with those changes, here's the table structure:

tblEnquirer
EnquirerID 'autonumber PK
FirstName
LastName
(other fields with those personal details.....)

tblEnquiry
EnquiryID 'autonumber PK
EnquirerID FK
EnquiryTypeID
EnquiryDate
(other fields that describe the enquiry - time, subject, classes?)

tblEnquiryType
EnquiryTypeID
EnqTypDesc 'short for Enquiry Type Description...

In the relationships window, drag the EnquirerID field

from the tblEnquirer to the EnquirerID field of the
tblEnquiry. Enforce referential integrity. Then drag
the EnquiryTypeID field from tblEnquiryType to the
EnquiryTypeID field of tblEnquiry and make your settings
for referential integrity.

Notice that the EnquiryType does not have the EnquirerID

or the EnquiryID. Those are not a part of the subject of
Enquiry Types.

The cbo for EnquiryType should be a part of the subform

since it is helping to define each individual Enquiry and
does nothing to define the Enquirer.

After you set things up this way, then YES there is a

way to take the information from the existing table and
paste it into the new tables. It's done by using an
Append query.

 

[Guest]

Good morning! I just built the tables and relationships in about 10 minutes or less, no problems. So I'm guessing that the fields are not the correct data types. I have the Primary Key in each table as an autonumber-long integer. The only table that has Foreign Keys is tblEnquiry.

Anytime you are creating a relationship between two fields, the data type should match. The exception is when you use an autonumber. An autonumber is a number data type- long integer. The foreign key field must be assignable by the user, so it can't be an autonumber. So Access will allow the foreign key to be a number field- long integer.

Here's how my table is setup - compare it to yours to see if there's a difference.

The only other thing I can think of that might not allow the link is the direction of the drag - it must be FROM the Primary key TO the Foreign key.

Did you try this in a new database? Or in your existing?

 

[Guest]

Are things working for you now? Do you need any help with the append query?

 

[Steven]

rpw
Thanks - didn't look at this database problem over
weekend.
My problem was trying to make the corrections to the
existing database. I have now created the new database
and ran the append queries. Have successfully transferred
info on tblEnquirer and tblEnquiryType. I am now having
problems trying to run the append query on tblEnquiry. It
will not transfer the info due to Null values in some
entries.
any assistance appreciated.

 

[Guest]

You might try a select query looking for all of the nulls, fixing the nulls, and then doing the append.