MS Access JOIN Query

Posted on 2011-04-25
Last Modified: 2012-06-27
Good Day Experts

Perhaps you all can assist me as my MS Access query building experience is rather light.  
I have three tables that I am working with...InvoiceHeader, InvoiceDetail and Address.
I am interested in getting all of the data fron the InvoiceDetail table for each InvoiceHeader record.  InvoiceNumber is the common field between the two tables.  In the InvoiceHeader and Address tables, I have a field names StoreNumber.  I want to get the address information from the Address table.  

Conceptually this seems doable:
1)Get InvoiceDetail records based on InvoiceHeader.InvoiceNumber
2)Get Address records based on InvoiceHeader.StoreNumber

I do ok when not trying to do the Address table.  But, I am not sure how to do it to get the address  table data.

Any helpful hints or insight is appreciated,
Question by:Jimbo99999
    LVL 40

    Accepted Solution

    Are you looking for this?
    SELECT * 
      FROM ((InvoiceHeader 
             INNER JOIN InvoiceDetail 
               ON InvoiceHeader.InvoiceNumber = InvoiceDetail.InvoiceNumber) 
            INNER JOIN Address 
              ON InvoiceHeader.StoreNumber = Address.StoreNumber)

    Open in new window


    Author Comment

    Oh...I did not know of that technique with the inner join in the inner join.  Thanks, I will try it in the morning.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now