SQL help, not retrieving data from joined tables

[maxflitom]

Hello Tek-Tips. I have the following SQL statement on an Access database. The only issue I have is when there is no entry in the EventDetail Table matching the Events.EventID field, the aggregate functions (GrandTotal & SubTotal) return a null even though there is an entry in the MeetingRoomPrice field. The GrandTotal & SubTotal fields return a value as long as there is a matching EventDetail.EventID to the Events.EventID field.

Any suggestions will be greatly appreciated.

Tom

SELECT Customers.CustomerName, Customers.ContactName,
Customers.Address1, Customers.Address2,
Customers.Phone1, Customers.Phone2, Customers.Fax1,
Customers.eMail, Customers.City & ",
" & States.StateShort & " " & Customers.Zip AS CityStateZip,
Events.EventName, Events.EventID, Events.BookingDate,
Events.EventDate, Events.EventStartTime,
Events.EventEndTime, Events.GTD,
Events.MeetingRoomPrice, Events.OnSiteContact,
Events.PaymentDetail, Events.CCExpDate, Events.Notes,
Events.SalesTax, MeetingRooms.MeetingRoomName,
Events.Grat11 + Events.Grat7 AS ServiceCharge,
SetupTypes.SetupType, PaymentTypes.PaymentType,
SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice + Events.SalesTax + Events.Grat11
+ Events.Grat7 AS GrandTotal,
SUM(EventDetail.Price * EventDetail.Quantity)
+ Events.MeetingRoomPrice AS SubTotal
FROM ((((((Events INNER JOIN
Customers ON Events.CustomerID = Customers.CustomerID)
INNER JOIN
Employees ON
Events.EmployeeID = Employees.EmployeeID) INNER JOIN
MeetingRooms ON
Events.MeetingRoomID = MeetingRooms.MeetingRoomID)
INNER JOIN
SetupTypes ON
SetupTypes.SetupTypeID = Events.SetupTypeID) INNER JOIN
PaymentTypes ON
PaymentTypes.PaymentTypeID = Events.PaymentTypeID)
INNER JOIN
States ON States.StateID = Customers.StateID) LEFT JOIN
EventDetail ON EventDetail.EventID = Events.EventID
WHERE Events.EventID = ?
GROUP BY Customers.CustomerName, Customers.ContactName,
Customers.Address1, Customers.Address2,
Customers.Phone1, Customers.Phone2, Customers.Fax1,
Customers.eMail, Customers.City, Customers.Zip,
Events.EventDate, Events.EventName, Events.EventID,
Events.BookingDate, Events.EventDate,
Events.EventStartTime, Events.EventEndTime, Events.GTD,
Events.MeetingRoomPrice, Events.OnSiteContact,
Events.PaymentDetail, Events.CCExpDate, Events.Notes,
Events.SalesTax, MeetingRooms.MeetingRoomName,
Events.Grat11, Events.Grat7, SetupTypes.SetupType,
PaymentTypes.PaymentType, States.StateShort

 

[cyberbiker]

WHERE Events.EventID = ?
will look for a value in the event id field, but will not
find a record containing a Null value in that field.


Terry (cyberbiker)

 

[CCLINT]

What happens when you use a RIGHT JOIN?

 

[maxflitom]

cyberbiker,

Thank you for your response. I thought when I Left Join the EventDetails table to the Events Table it would include all records in the EventDetails table even if they do not exist. Or maybe my syntax is not correct.

Tom

 

[maxflitom]

CCLINT,

Thank you for responding, I attempted the Right Join however I receive a "Join Expression not supported" error. I am using Access XP.

Tom

 

[cyberbiker]

I have not worked with Access XP yet. But unless I am very much mistaken, you are comparing

WHERE Events.EventID = "something".

Since NULL is NOT "Something",
but instead is "nothing" you will not find any records where
WHERE Events.EventID contains a null value.

I would guess that you will get the results you wish by
WHERE Events.EventID = ? or isNull(Events.EventID).

I have not had time to verify this and will definately yield to CCLint should he suggest I am wrong since he seems to be much better versed in this subject than I.


Terry (cyberbiker)

 

[maxflitom]

cyberbiker

I'll play around with it today. Sorry I did not respond, I have been on some other projects.

Thanks for helping me!

Tom