Table Join

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Sara via AccessMonster.com (forum_at_AccessMonster.com)
Date: 02/17/05 

Date: Thu, 17 Feb 2005 19:38:52 GMT

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 

-- 
Message posted via http://www.accessmonster.com

Relevant Pages

  • Re: Business objects, subset of collection
    ... In addition, such infrastructures are, ... be relevant in a subsystem that modeled the customer's business problem. ... foreach (Vendor vendor in this) ... For each Payment ...
    (comp.object)
  • Re: help with object design for a database
    ... By conversion I meant loading the vendor data into the DB. ... You are going to have to write some code somewhere to parse the vendor format and convert it into a format that the RDB can eat. ... But with RAD IDEs like Access you can make a pure form-based UI where the clerk enters text fields from the keyboard. ... You are going to have to write some code for that interface because the RAD IDEs don't have infrastructure support for that yet AFAIK. ...
    (comp.object)
  • Re: $73 an Hour
    ... This means that the vendor has ... Blogger information indicates that the payment times have ... problems with their diesel engine supplier [Navistar]. ...
    (rec.crafts.metalworking)
  • Re: How to get Textbox dates into Stored Procedure
    ... Whether the format is wrong, or something else is what I will have to ... Check for the EOF condition on the recordset before trying to access any ... I have a form with two dates, StartDate and EndDate. ... FROM Floyd.dbo.SYSCountryCode INNER JOIN ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Current Balance Due
    ... FROM INVOICE INNER JOIN (PAYMENT INNER JOIN PAYMENTLINK ON PAYMENT.PAYMENTID ... FROM query AS a INNER JOIN query AS b ...
    (microsoft.public.access.queries)