many to many relationship

[Apple]

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!

[Dave]

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

"Apple" <tt*********@ya hoo.com.hk> wrote in message
news:55******** *************** ***@posting.goo gle.com...

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!

[Chris2]

"Apple" <tt*********@ya hoo.com.hk> wrote in message
news:55******** *************** ***@posting.goo gle.com...

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.

[Apple]

"Chris2" <ra************ ******@GETRIDOF .luminousrain.c om> wrote in message news:<Ub******* *************@c omcast.com>...

"Apple" <tt*********@ya hoo.com.hk> wrote in message
news:55******** *************** ***@posting.goo gle.com...

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.

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&CarInf o Query
=============== =======
CarInfo.CarNo
..OwnerNo
..Brand
..Model
..MemberNo
..StampExpDate
..OpenA/CDate
..CloseA/CDate
CarOwner.OwnerN o
..Name
..Tel

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

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

4. A Call Query will be created

Call Query
==========
Call.RecordNo
..CallNo
..CarNo
CarOwner&CarInf o.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.Receipt No
..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

[Bob Quintal]

tt*********@yah oo.com.hk (Apple) wrote in
news:55******** *************** ***@posting.goo gle.com:

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

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.

[snip]

so far, good.
'3. I will make a table to join the CarOwner&CarInf o Query

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

4. A Call Query will be created

Call Query
==========
Call.RecordNo
.CallNo
.CarNo
CarOwner&CarInf o.OwnerNo
.Name
.Tel
.Brand
.Model
.MemberNo
.StampNo
.StampExpDate
.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

tblReceipt
==========
ReceiptNo- Auto (PK)
CallNo - text
PaymentMethod - text
the receipt needs Call.RecordNo to store that value as its
foreign key.
6. Join table Receipt with Call Query

Receipt Query
=============
Receipt.Receipt No
.CallNo
.PaymentMethod
Call Query.OwnerNo
.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.
P.S. The CallNo. must be reused after the job completed.

Sincerely,
Apple

Good luck

--
Bob Quintal

PA is y I've altered my email address.