Table Join

[Sara via AccessMonster.com]

Joining 3 tables without repetition of records - please Help
------------------------------------------------------------
I have tried to get this right and I am unable to find the solution. Please
help me to solve this
I want to JOIN 3 tables
Tables PrimaryKey Fields to be selected
1 VendorPayment PaymentID Date,Sum(Cash+Cheque)
2 Vendor Master VendorID VendorName,OPBL
3 ReceiptMaster ReceiptID
Date,BillAmt

Here is the query I have written using Inner join

"SELECT format(Vendor_Payment_Details.Date,""dd/mm/yyyy"") AS PaymentDate,
(Sum(Cash_Amt)+Sum(Cheque_Amt)) AS Payment, format
(Receipt_Master.Date,""dd/mm/yyyy"")AS ReceiptDate, Receipt_Master.Bill_Amt
AS Receipts,
Vendor_Master.OPBL" & _ " FROM Vendor_Master
INNER JOIN
(Vendor_Payment_Details INNER JOIN Receipt_Master ON
Vendor_Payment_Details.Vendor_ID = Receipt_Master.Vendor_ID)
ON
(Vendor_Master.Vendor_ID = Receipt_Master.Vendor_ID)
AND
(Vendor_Master.Vendor_ID = Vendor_Payment_Details.Vendor_ID)" & " where
Vendor_master.Vendor_ID=1" & "
AND
Vendor_Payment_Details.Date Between #" & Me.txtfdate & "# and #" &
Me.txttdate & "#
AND
Receipt_Master.Date Between #" & Me.txtfdate & "# and #" & Me.txttdate &
"#" & _"
GROUP BY
Vendor_Payment_Details.Date, Receipt_Master.Bill_Amt, Vendor_Master.OPBL,
Receipt_Master.Date;"


Note:
? Here the txtfdate, txttdate values are entered by the user.
? Format function is used to convert the dates which are stored in short
date (mm/dd/yyyy) format to dd/mm/yyyy format.
? Here I want to select the records for a specified Vendor and between two
dates Say 1/1/2004 to 1/2/2004(dd/mm/yyyy).
? I tried this with both inner and outer Join operation. When the number
of records present in VendorPayment table (which meets this criteria) are
more than the ReceiptMaster table it is displaying repeated values from
the receipt master table.

? I have tried the query with Left and right outer join But I still get the
same results.

Following is the results obtained for a particular vendor between 1st
---------------------------------------------------------------------
Jan 2004 to 1st Feb 2004.
------------------------
This particular vendor has records in the vendor_payment_details table for
both 1/1/2004 and 1/2/2004
But has only one entry in Receipt_master table on 1/1/2004.

I.e. For the single bill, 2 part payments have been made

Current result
-------------
PaymentDate Payment ReceiptDate Receipts Opbl
01/01/2004 2500 01/01/2004 4500 10000
01/02/2004 2000 01/01/2004 4500 10000

Desired Result
------------
PaymentDate Payment ReceiptDate Receipts Opbl
01/01/2004 2500 01/01/2004 4500 10000
01/02/2004 2000


**Receipts in the result is Bill_Amt in the receipt_Master table for a
particular vendor on that date.
**Payment is the sum of Cash_Amt and Cheque_Amt in the
vendor_Payment_detail table.
**OPBL - opening balance

All the 3 Tables with complete field names

Vendor_Master
------------
Vendor_ID
Company_Name
Address
City
Phone_No
Contact_Person
Fax_No
E-mail
OPBL

Vendor_payment_details
----------------------
Receipt_ID
Vendor_ID
PaymentDate
Mode_Of_Payment
Cheque_Date
Cheque_No
Cheque_Amt
Bank_Name
Branch_Name
City

Receipt_Master
-------------
Receipt_ID
Vendor_ID
Bill_Amt
GR_No
Bill_No
Receiptdate


Thanking you all in advance
Sara

 

[Ken Snell [MVP]]

Your query is doing what it's supposed to do. You asked that the matching
value from the ReceiptMaster table be shown with each record, and that is
what the query is doing.

What do you want the query to show for the "ReceiptMaster" values on those
"additional" records if not the real value?

Remember, a query is not intended to look like a report. A report can be
designed so that you'd see the ReceiptMaster data just once for each group
of records, but the query will still return that value for each record.

Also, do not use Date as the name of a field. It and many other words are
reserved words in ACCESS, and can create serious confusion for ACCESS and
Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763