"Chris2" <ra******************@GETRIDOF.luminousrain.com> wrote in message news:<Ub********************@comcast.com>...
"Apple" <tt*********@yahoo.com.hk> wrote in message
news:55**************************@posting.google.c om... 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&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