Database Relationship Problems

[Chris P.]

I am having some problems with my relationships. I
learned Access about 2 years ago and I haven't used it
much up until now. My relationships are all messed up.
Some tables I can't get a relationship to work
approriately and then they display a one-to-many
relationship when it should be a one-to-one. Also it wont
allow me to enforce referential integrity which i need so
it will delete all associated records in all the tables
if i delete a client/customer. This is a summary of the
problems I am facing.

I have 4 tables; Clients, Billing, TechSupport,
Technicians. The relationship between clients and
techsupport is fine because when i look at a client
record i can see subrecords of that client from the
TechSupport table. Now the only way I got the Billing and
Technicians table to interact was to write a program in
VB to alow this. I would liie my databse to work for me
instead of using a VB program to.

Without me getting into a ton of details is there anyone
willing to look at this small database with only a few
records that I added for testing? Liek i said it is small
it has only 4 tables and I don't have much experience
with Access or databases to get the realtionships working
right. I searched for infromation all over and I can't
get it to work so this is a last ditch effort to get it
to work. I am in college for IST so I have a very good
idea about computers, but I learned more programming and
networking than databases. I had databases classes but 2
years ago and I haven't got a clue what to do.

Thanks ahead of time for your time and help. If you are
interested in looking at the database contact me at
(e-mail address removed) and I will email it to you or place
it on the web for DL. It is only 600K in size.

Chris P.

 

[Steve Schapel]

Chris,

In addition to Ken's comments, let me throw a couple of others into
the ring.

Do you have your database "split" into a backend file with your main
data tables, and a frontend file with the rest? If so, you will need
to directly access the backend file to set up your relationships
properly, as it can't be done from the frontend file.

When you mention about the tables "interacting" with each other, I've
got a sneaking suspicion you are looking at the data directly in the
tables, with the table's Subdatasheet property still enabled, which is
a different issue again.

- Steve Schapel, Microsoft Access MVP

 

[Chris P.]

I haven't got a clue what you mean by backend and
frontend. I know what that means if we were talking about
networking, but I dont think that applies since this is a
small database made in access with 4 tables. I am making
a program in VB to use, manipulate, and maintain the
database. This has no bearing on the issue because since
my database wont allow all 4 of the tables to work
together because the relationships are all screwy, I
forced it to work through my program. however, my program
is not the issue. I need to get the relationships to work
for the 4 tables as forementioned and I can't get it
done. I have read alot of information off the interent,
from my books, and from DB examples i have of NorthWind
and Biblio. I just cant get the right relationship like a
one-to-one to work and enforce referential integrity.
If you need more information about my DB let me know or
if you like to see it 600K is the size i can zip it and
email it to you let me know (e-mail address removed)

Ideally, I would like to have the 4 table work together
as such; The client
table will allow me to see all the techsupport info for
each client. The
Techsupport table will display all the billings for the
issues in the TechSupport table.Lastly,
the billings table will show all the information for that
technician in the technician table.

Thanks for your time guys,
Chris P.

 

[John Vinson]

My relationships are all messed up.
Some tables I can't get a relationship to work
approriately and then they display a one-to-many
relationship when it should be a one-to-one. Also it wont
allow me to enforce referential integrity which i need so
it will delete all associated records in all the tables
if i delete a client/customer. This is a summary of the
problems I am facing.

I have 4 tables; Clients, Billing, TechSupport,
Technicians.

One to one relationships are VERY RARE, and just based on the table
names I don't see any role for them here. Surely one Client will have
more than one Bill, and more than one TechSupport issue; I'm also
certain that each Technicial will deal with many TechSupport issues.

Without a paying contract, I'd be reluctant to receive the database -
but you'll probably get a good, multi-person discussion going if
you'll post how you see these four tables interacting.

I fully agree with the comments elsethread. Table datasheets are for
DEBUGGING ONLY not for routine data entry. You'll want Forms for that.
But the table structure must be right first!

 

[John Vinson]

Ideally, I would like to have the 4 table work together
as such; The client
table will allow me to see all the techsupport info for
each client.

Ok... each Client has multiple Tech Support info records. One to many.

The
Techsupport table will display all the billings for the
issues in the TechSupport table.

Billings must be (I would presume) to a Client and should reference a
Tech Support issue, so the Billings table would be related one to many
to the Client table; since a given bill might cover several tech
support issues (rolled together into one bill) and a given tech
support issue may run a long time and might involve several bills. A
many to many relationship would seem indicated, requiring a new
BillingDetails table.

Lastly,
the billings table will show all the information for that
technician in the technician table.

The billings table obviously should have a LINK to the technician
table, but the information for the technician should exist ONLY in the
technicians table.

Where are your desired one to one relationships? Note that a one to
one is VERY RARELY NEEDED (for things like Subclassing and getting
around some security limitations) - one to many are MUCH more common,
and many to many (which are implemented as two one-to-many
relationships) yet more common. If you really have a one to one
relationship, you can ordinarily simply use a single table rather
than joining two.

Please explain what one to one relationships you are trying to
implement, and why. I suspect that your problem lies not in Access'
software limitations, but in your conceptualization of the problem!