MS Access Database SQL Query

Question

I have 3 Tables called Invoice, Customer and Company. I want to merge this 3 tables into single using Query. In Invoice Table contain Customer Id and Company Id. How to Join 3 tables ?

I tried Invoice and Customer Table working fine with this query. But I dont have idea to add 3rd table with this.

SELECT RPT_Invoice_Less.InvoiceNumber, RPT_Invoice_Less.Terms,
    RPT_Invoice_Less.Invoicedate, RPT_Invoice_Less.OurQuote,
    RPT_Invoice_Less.SalesPerson, RPT_Customer.CustomerName,
    RPT_Customer.CustomerId, RPT_Customer.ContactPerson,
    RPT_Customer.BillingAddress, RPT_Customer.DeliveryAddress,
    RPT_Invoice_Less.OrderNumber, RPT_Invoice_Less.ShippingBy,
    RPT_Invoice_Less.ShipReferenceNo, RPT_Invoice_Less.Notes,
    RPT_Invoice_Less.Price, RPT_Invoice_Less.Discount,
    RPT_Invoice_Less.Shipping, RPT_Invoice_Less.Tax,
    RPT_Invoice_Less.GrandTotal, RPT_Invoice_Less.Company
FROM RPT_Invoice_Less
INNER JOIN RPT_Customer
ON RPT_Invoice_Less.CustomerId = RPT_Customer.CustomerId;

this code working fine for 2 tables

SELECT RPT_Invoice_Less.InvoiceNumber, RPT_Invoice_Less.Terms, RPT_Invoice_Less.Invoicedate, RPT_Invoice_Less.OurQuote, RPT_Invoice_Less.SalesPerson, RPT_Customer.CustomerName, RPT_Customer.CustomerId, RPT_Customer.ContactPerson, RPT_Customer.BillingAddress, RPT_Customer.DeliveryAddress, RPT_Invoice_Less.OrderNumber, RPT_Invoice_Less.ShippingBy, RPT_Invoice_Less.ShipReferenceNo, RPT_Invoice_Less.Notes, RPT_Invoice_Less.Price, RPT_Invoice_Less.Discount, RPT_Invoice_Less.Shipping, RPT_Invoice_Less.Tax, RPT_Invoice_Less.GrandTotal, RPT_OrionSystem.Company, RPT_OrionSystem.CompanyId
FROM RPT_Invoice_Less 
INNER JOIN RPT_Customer 
ON RPT_Invoice_Less.CustomerId = RPT_Customer.CustomerId
INNER JOIN RPT_OrionSystem
ON RPT_Invoice_Less.CompanyId = RPT_OrionSystem.CompanyId;

This code showing syntax error.

Help me to add 3rd Company table to this.

To answer your question we need to know where is the relation between the Company table and one of the other two tables. Something like a CompanyID in RPT_Customer table for example. — Steve
– Steve, Commented Feb 1, 2014 at 10:50

Steve · Accepted Answer · 2014-02-01 11:07:45Z

1

Supposing that you have a CompanyID field (or something like that) in the RPT_Customer table or in the RPT_Invoice_Less, it is just a matter to add another INNER JOIN

....
FROM ((RPT_Invoice_Less
INNER JOIN RPT_Customer
ON RPT_Invoice_Less.CustomerId = RPT_Customer.CustomerId)
INNER JOIN RPT_OrionSystem 
ON  RPT_Invoice_Less.CompanyID = RPT_OrionSystem.CompanyID)

edited Feb 1, 2014 at 11:07

answered Feb 1, 2014 at 10:52

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Srihari Over a year ago

Hi, I edited my question now I tried same as you mentioned but getting syntax error

Steve Over a year ago

Try adding a couple of parenthesys

Srihari Over a year ago

Where to add ? I tried already that I add brackets on (RPT_Invoice_Less.CustomerId = RPT_Customer.CustomerId) and (RPT_Customer.CompanyID = RPT_CompanyData.CompanyID) still same syntax error

Steve Over a year ago

Answer updated refresh, and remove the semicolon at the end. Could you add the exact C# code around this call?

Srihari Over a year ago

Yeah that's working fine after adding parenthesys . This Query I used in MS Access Database only. Then I populate to C#. Thankz a lot.

Collectives™ on Stack Overflow

MS Access Database SQL Query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related