Query - 3rd table w/no data skews results

[rx8pdx]

Here is a fictional understanding of what I'm trying to do. Customer #1 Orders a Big Mac with Fries and a drink


Customer #2 Orders a Big Mac only.


In my tables: Customers, Sandwhichs ordered, List of ingredients that make this sandwhich, and Add-Ons.


So now I want to use a query to print a receipt. In the query I simply have Customer Name(Customer Table), Which Sandwhich(Sandwhich's Ordered Table) and Additional 1 (Add-Ons Table).


So the query results will display Customer #1 correctly because he has items in "Additional 1"
But will not show Customer #2 because "Additional 1" is blank.


When I remove the Additional 1 field AND the Table from the query, Both customers show up.


Any help would be appreciated I ultimately will need to generate a report with all these fields.


P.S I have the following one-to-many relationships that work correctly: CustomerID, SandwhichID and AddonID.

[Robeen]

Could you post your SQL here?
In Design View -> View -> SQL View . . . and copy the SQL & post it here for us.

[orange]

Getting your tables and relationships set up correctly is key to successful database.

There is a series of free video tutorials at the link below that have an example dealing with design aspects of
Customer, Order, OrderItems, and Items

see post #2 at https://www.accessforums.net/showthre...lp-with-design

[rx8pdx]

Could you post your SQL here?
In Design View -> View -> SQL View . . . and copy the SQL & post it here for us.

SELECT [Vendor Contact].Zip, Contracts.ContractID, [Vendor Contact].Fax, Contracts.SponsorID, Contracts.ContactID, Event.State, Event.City, [Vendor Contact].Email, [Vendor Contact].PrimaryPhone, [Vendor Contact].State, LSTSponsorships.Price, [Vendor Contact].Contact, [Vendor Contact].City, [Vendor Contact].Address, [Vendor Contact].Company, Contracts.Market, Contracts.Date, Addon.AddOns, Contracts.Sponsorship, Event.CityFROM [Vendor Contact] INNER JOIN (Event INNER JOIN ((LSTSponsorships INNER JOIN Contracts ON LSTSponsorships.SponsorhipID = Contracts.Sponsorship) INNER JOIN Addon ON Contracts.SponsorID = Addon.SponsorID) ON Event.Market = Contracts.Market) ON [Vendor Contact].ContactID = Contracts.ContactID
WHERE (((Contracts.ContractID)=[Forms]![Contracts]![ContractID]));

[rx8pdx]

Getting your tables and relationships set up correctly is key to successful database.

There is a series of free video tutorials at the link below that have an example dealing with design aspects of
Customer, Order, OrderItems, and Items

see post #2 at https://www.accessforums.net/showthre...lp-with-design

Thank you for working with me? are you saying it is my table structure that causes this?

[rx8pdx]

I got it working by switching INNER JOIN Addon to a LEFT JOIN. A left join will return rows for all of the records in the table on the left if the "left join" keywords and, if there is matching data from the table on the right it will return that too. If there is no matching data, then the cell in the query for that row will be blank.