Select Records w/ & w/o Corresponding Details

[Guest]

I'm having trouble running a query in which i want to view outgoing Special
Events orders for the week. Some orders have corresponding order details and
others do not. For the orders that have corresponding details, the query
calculates the total number of items on that order.

Everything works fine except that the query doesn't return orders that have
no corresponding lines in the Event Detail table. Any way i can get both the
orders with and without order details to display??

Here's my non-working query so far (Please note it was written in Design
View):

SELECT Events.[Event Number], Events.Customer, Events.[Service Date],
Events.[Service Type], Events.[Number of Machines], Events.[Arrival Time],
Events.[Departure Time], Events.[Event City], Sum([Event Detail].Quantity) AS
SumOfQuantity
FROM Events INNER JOIN [Event Detail] ON Events.[Event Number]=[Event
Detail].[Event ID]
GROUP BY Events.[Event Number], Events.Customer, Events.[Service Date],
Events.[Service Type], Events.[Number of Machines], Events.[Arrival Time],
Events.[Departure Time], Events.[Event City]
HAVING (((Events.[Service Date]) Between Date() And Date()+7));

 

[Van T. Dinh]

.... or you can use Unmatched Query Wizard which will (eventually) create the
Outer Join for you.

--
HTH
Van T. Dinh
MVP (Access)

Aaah, solved my own problem here by chaning the INNER JOIN statement to LEFT
OUTER JOIN.

I'm having trouble running a query in which i want to view outgoing Special
Events orders for the week. Some orders have corresponding order details and
others do not. For the orders that have corresponding details, the query
calculates the total number of items on that order.

Everything works fine except that the query doesn't return orders that have
no corresponding lines in the Event Detail table. Any way i can get both the
orders with and without order details to display??

Here's my non-working query so far (Please note it was written in Design
View):

SELECT Events.[Event Number], Events.Customer, Events.[Service Date],
Events.[Service Type], Events.[Number of Machines], Events.[Arrival Time],
Events.[Departure Time], Events.[Event City], Sum([Event Detail].Quantity) AS
SumOfQuantity
FROM Events INNER JOIN [Event Detail] ON Events.[Event Number]=[Event
Detail].[Event ID]
GROUP BY Events.[Event Number], Events.Customer, Events.[Service Date],
Events.[Service Type], Events.[Number of Machines], Events.[Arrival Time],
Events.[Departure Time], Events.[Event City]
HAVING (((Events.[Service Date]) Between Date() And Date()+7));

 

[David Seeto via AccessMonster.com]

Yes, you can get orders with or without order details - you need to modify
your query so that instead of an INNER JOIN it uses an OUTER JOIN. You can
do this in Design view by right clicking on the join itself - the line
connecting the two tables. You should get a pop up menu with the options
"Join Properties / Delete".

Choose "Join properties" and then you can choose whichever table you want
to choose all the records from (in this case, Events - whether it is option
2 or option 3 depends on the direction you used when first joinging the
table together). The SQL this would generate looks something like:
SELECT Events.[Event Number], Events.Customer, Events.[Service Date],
Events.[Service Type], Events.[Number of Machines], Events.[Arrival Time],
Events.[Departure Time], Events.[Event City], Sum([Event Detail].Quantity)
AS SumOfQuantity
FROM Events LEFT JOIN [Event Detail] ON Events.[Event Number] = [Event
Detail].[Event ID]
GROUP BY Events.[Event Number], Events.Customer, Events.[Service Date],
Events.[Service Type], Events.[Number of Machines], Events.[Arrival Time],
Events.[Departure Time], Events.[Event City];

(left off the HAVING clause - not sure if this will cause a problem...)