Access 2007

[tklambStudent]

I have 3 tables. Owner, Pet, and Service. Owner has OID PK; Pet has PID PK,
OID FK; and Service has SID PK, OID FK, and PID FK. Owner is related to Pet
and Services. Pet is related to Services.

The problem I'm having is getting the service table to update properly. If I
create a subform for pet and services and place it in the owner form, then
the owner id will automatically fill in for all three (the form and two
subforms). However, filling in the pet info in the pet subform will not
update the pet id in the service subform.

If I have a form for owner with a button to open a form for pet containing
the service subform, the pet ID will update in the service subform, but the
owner ID will not update in either the pet form or the service subform.

I hope this makes sense. Can someone tell me what I am doing wrong?

 

[Keven Denen]

I have 3 tables. Owner, Pet, and Service. Owner has OID PK; Pet has PID PK,
OID FK; and Service has SID PK, OID FK, and PID FK. Owner is related to Pet
and Services. Pet is related to Services.

The problem I'm having is getting the service table to update properly. If I
create a subform for pet and services and place it in the owner form, then
the owner id will automatically fill in for all three (the form and two
subforms). However, filling in the pet info in the pet subform will not
update the pet id in the service subform.

If I have a form for owner with a button to open a form for pet containing
the service subform, the pet ID will update in the service subform, but the
owner ID will not update in either the pet form or the service subform.

I hope this makes sense. Can someone tell me what I am doing wrong?

I'm not sure you have your relationships established properly. Does
the owner have services or does the pet have services or both? The way
you have it set up now, it seems like both. If owners don't get
services, you shouldn't be linking owner directly to service.

My guess is that you should have OID in Owner as PK and in Pet as FK.
Owners have pets, pets receive services.

Let us know more about the real world relationship you are trying to
create and we can give more exact advice.

Keven Denen

 

[tklambStudent]

So basically, you are saying that OID should only be associated with Pets and
the pets only be associated with the services. It makes sense, but what if I
were to do a query on the services...shouldn't they then be associated with
the owner. Of course, the purpose of the query would be to ultimately find
the owner of a pet that had a specific service and what the cost of that
service is. So, again shouldn't the owner be associated with both pets and
services?

 

[Keven Denen]

So basically, you are saying that OID should only be associated with Petsand
the pets only be associated with the services. It makes sense, but what if I
were to do a query on the services...shouldn't they then be associated with
the owner. Of course, the purpose of the query would be to ultimately find
the owner of a pet that had a specific service and what the cost of that
service is. So, again shouldn't the owner be associated with both pets and
services?









- Show quoted text -

No, if you want to find out what services an owner had done to his/her
pets, you would write a query that connects the owner to the services
through the pets table.

Your query will end up being something along these lines:

SELECT owner.ownername, service.ServiceCost
FROM owner INNER JOIN pet ON owner.oid = pet.OID INNER JOIN service ON
pet.PID = service.PID

Keven Denen

 

[tklambStudent]

Thank you very much..I think you have saved me a lot of time as I would still
be struggling to get the owner and service tables/forms to work/update.

 

[»ÆÓÀÆ»]

Thank you very much..I think you have saved me a lot of time as I would
still
be struggling to get the owner and service tables/forms to work/update.

 

[»ÆÓÀÆ»]

Thankyou for you E-mail.

Thank you very much..I think you have saved me a lot of time as I would
still
be struggling to get the owner and service tables/forms to work/update.