many to many relationship

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Apple - 26 Feb 2005 11:13 GMT

I have to create a query with many to many relationship, but I can't
break it into 2 x 1 to many, should there anyone can teach me how to
solve this problem. Thanks in advance!

Reply to this Message

Dave - 26 Feb 2005 13:05 GMT

What are the entities involved apple? Normally another entity is needed
between the relationship.

>I have to create a query with many to many relationship, but I can't
> break it into 2 x 1 to many, should there anyone can teach me how to
> solve this problem. Thanks in advance!

Reply to this Message

Chris2 - 26 Feb 2005 19:09 GMT

> I have to create a query with many to many relationship, but I can't
> break it into 2 x 1 to many, should there anyone can teach me how to
> solve this problem. Thanks in advance!

Apple,

Can you please explain why you can't use two "one to many"
relationships to express a many to many relationship?

Sincerely,

Chris O.

Reply to this Message

Apple - 27 Feb 2005 09:26 GMT

> > I have to create a query with many to many relationship, but I can't
> > break it into 2 x 1 to many, should there anyone can teach me how to
[quoted text clipped - 8 lines]
>
> Chris O.

Thank you Chris, I believe my table is not sturcture good. Please Let
me show you my table structure and hope you can give me some advise.

1. I will make 2 tables as :
tblCarOwner ( PK - Primary key)
===========
OwnerNo - text (PK)
Name - text
IDNo - text
Birthday - date
Sex - text
Tel - text
Add - text

tblCarInfo
==========
CarNo - text (PK)
OwnerNO - text (Combo box)
Brand - text
Model - text
MemberNo - text
StampNo - text
StampExpDate - date
OpenA/CDate - date
CloseA/CDate - date

2. I will join the tables CarOwner & CarInfo as

CarOwner&CarInfo Query
======================
CarInfo.CarNo
.OwnerNo
.Brand
.Model
.MemberNo
.StampExpDate
.OpenA/CDate
.CloseA/CDate
CarOwner.OwnerNo
.Name
.Tel

'3. I will make a table to join the CarOwner&CarInfo Query

tbl.Call
========
RecordNo - AutoNo (PK)
CallNo - text
CarNo - text

4. A Call Query will be created

Call Query
==========
Call.RecordNo
.CallNo
.CarNo
CarOwner&CarInfo.OwnerNo
.Name
.Tel
.Brand
.Model
.MemberNo
.StampNo
.StampExpDate
.OpenA/CDate
.CloseA/CDate (expression : is Null)

5. Finally, I will create table Receipt to join the Call query

tblReceipt
==========
ReceiptNo- Auto (PK)
CallNo - text
PaymentMethod - text

6. Join table Receipt with Call Query

Receipt Query
=============
Receipt.ReceiptNo
.CallNo
.PaymentMethod
Call Query.OwnerNo
.Name
.CarNo

The problem is :
1. when I use CallNo in table receipt as PK, it's not allow me to
charge the same CallNo more than once.
2. when I use ReceiptNo in table receipt as PK, it's can't append
record, I think the reason is the join fields not the PK.

I hope I describe my suituation well to let you understand, please
teach me what shall I do. Thanks in advance.

P.S. The CallNo. must be reused after the job completed.

Sincerely,
Apple

Reply to this Message

Bob Quintal - 27 Feb 2005 11:55 GMT

>> Can you please explain why you can't use two "one to many"
>> relationships to express a many to many relationship?
[quoted text clipped - 6 lines]
> Please Let me show you my table structure and hope you can
> give me some advise.

[snip]

so far, good.

> '3. I will make a table to join the CarOwner&CarInfo Query
>
[quoted text clipped - 21 lines]
> .OpenA/CDate
> .CloseA/CDate (expression : is Null)

You should have Call.RecordNo in this query.

> 5. Finally, I will create table Receipt to join the Call query
>
[quoted text clipped - 3 lines]
> CallNo - text
> PaymentMethod - text

the receipt needs Call.RecordNo to store that value as its
foreign key.

> 6. Join table Receipt with Call Query
>
[quoted text clipped - 6 lines]
> .Name
> .CarNo

the receipt needs Call.RecordNo to store that value as its
foreign key.

> The problem is :
> 1. when I use CallNo in table receipt as PK, it's not allow me
> to charge the same CallNo more than once.

that's because you need to store Call.RecordNo as the key.

> 2. when I use ReceiptNo in table receipt as PK, it's can't
> append record, I think the reason is the join fields not the
> PK.

No I suspect that the use of the wrong key gives a result where
Access can't decide which Receipt is the valid one, because you
have a bad PK there.

> I hope I describe my suituation well to let you understand,
> please teach me what shall I do. Thanks in advance.
[quoted text clipped - 3 lines]
> Sincerely,
> Apple

Good luck

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message