Results 1 to 8 of 8

simple query joining 2 tables not producing expected result

  1. #1
    deebowyer is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    4

    simple query joining 2 tables not producing expected result

    Hi

    I think I am missing something obvious. I have a query to populate a form of invoices. One field is a restaurantid, which I want to pull the name associated with the id. It seems simple, but is not acting like I think it should. Is my structure a mess? What else would be helpful?

    SELECT Invoices.*, Restaurant.RestoName


    FROM Restaurant INNER JOIN Invoices ON Restaurant.RestoId = Invoices.RestaurantID;


    Click image for larger version. 

Name:	structure.PNG 
Views:	15 
Size:	17.2 KB 
ID:	26972




    Don

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,898
    A clue as to what exactly is "not acting like I think it should"?

  3. #3
    deebowyer is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    4
    Of course. My bad

    I wanted a listing of all the invoice table records, and the corresponding resto name that is associated with the restoId in both tables.

    What I get is a listing below which I think I should be able to understand why. The restoid and name repeat, but in the Invoice table the restoId is correct. Something is wrong in how they are joined or something

    InvoiceID SaleDate RestaurantID Season FarmerMarket MarketEarnings RestoName
    18 8/25/2009 1 2009 No
    Borealis Grille
    21 9/1/2009 1 2009 No
    Borealis Grille
    24 9/8/2009 1 2009 No
    Borealis Grille

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,097
    Your listing is exactly what you would expect from your query, and I see nothing wrong with your relationships.

    You have three invoices - 18, 21 and 24 - for restaurant ID 1, which is Borealis Grille.
    An of course restaurant ID and Restaurant Name repeat - that's how queries work.

    What is it that you think might be wrong?

  5. #5
    deebowyer is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    4
    Hi

    I needed to do a right join. What threw me off was when I did that join it sorted the output by restoid, so it looked like it was pulling only the first resto record. I didn't catch that the invoice numbers were skipping in the result. A order by invoice number sorted it the way I expected.

    thanks for helping me see the skipping invoice numbers.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,898
    Right join? Do you not have your Referential Integrity set?

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,097
    Further to aytee111's remark -

    If when you run that query you have missing invoice numbers, you have a data integrity problem. Most likely it is because you have some invoice records with a restaurantID value that does not a corresponding record in the Restaurant table, or in which the restaurantID value is missing completely.

    How you solve that depends on whether or not therestaurantID is a required item in the Invoices table; in other words, does each invoice have to refer to an existing restaurant?

  8. #8
    deebowyer is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    4
    Hi

    I do have the referential integrity set.

    At this point I am wondering if it always was working fine. I deleted my relationships between the table, reset them and retried the initial query. it did the same as the right join, and works. I don't understand why it sorts on the restoID without any explicit order by clause anywhere. Would have thought it sorted the primary key of the invoice table.

    I will have to put some structure in to make sure the restoID is not null in the invoice table. I found a few missing ones.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-01-2016, 05:41 AM
  2. Joining multiple tables get wrong result
    By Tony Thi in forum Queries
    Replies: 3
    Last Post: 10-16-2015, 07:54 AM
  3. Replies: 4
    Last Post: 04-03-2013, 07:49 PM
  4. Joining 2-Tables in a query
    By djclntn in forum Queries
    Replies: 7
    Last Post: 03-15-2012, 07:37 AM
  5. Joining 2-Tables in a Query
    By djclntn in forum Queries
    Replies: 25
    Last Post: 01-13-2012, 11:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums