Drew Wutka
DWUTKA at marlow.com
Wed May 7 14:33:03 CDT 2003
Okay Susan....the following SQL: SELECT tlnklodgingEvent.EventID, tblLodging.Type, tlnklodgingEvent.Guests, tlnklodgingEvent.NoNights, tlnklodgingEvent.CostPerUnit, tlnklodgingEvent.total, Format([LodgingDate],"ddd") AS Expr1 FROM tblLodging RIGHT JOIN tlnklodgingEvent ON tblLodging.LodgingID = tlnklodgingEvent.LodgingID WHERE (((tlnklodgingEvent.EventID)=9461)); UNION SELECT 9461 AS EventID, tblLodging.Type, 0 AS Guests, 0 AS NoNights, tblLodging.DRate, tblLodging.NDRate, "(Depending on if is Diocesan Event or NonDiocesan Event)" AS Day FROM tblLodging WHERE (((tblLodging.LodgingID) Not In (SELECT tlnklodgingEvent.LodgingID FROM tlnklodgingEvent WHERE (((tlnklodgingEvent.EventID)=9461)) GROUP BY tlnklodgingEvent.LodgingID;))) ORDER BY NoNights DESC, Type DESC; Produces these results: (Note, the Cost per unit and total are showing actual data from the tlnklodgingEvent table...I don't know where you got the numbers from, for your sample results....) EventID Type Guests NoNights CostPerUnit total Expr1 9461 Single 6 1 $50.00 $300.00 Wed 9461 Single 8 1 $50.00 $400.00 Mon 9461 Single 9 1 $50.00 $450.00 Tue 9461 Double 2 1 $35.00 $70.00 Thu 9461 Double 22 1 $35.00 $770.00 Mon 9461 Double 22 1 $35.00 $770.00 Tue 9461 Double 30 1 $35.00 $1,050.00 Wed 9461 Triple 0 0 $21.34 $25.33 (Depending on if is Diocesan Event or NonDiocesan Event) 9461 Tent 0 0 $10.00 $10.25 (Depending on if is Diocesan Event or NonDiocesan Event) 9461 R/V 0 0 $20.50 $20.50 (Depending on if is Diocesan Event or NonDiocesan Event) 9461 Quadruple 0 0 $17.50 $20.50 (Depending on if is Diocesan Event or NonDiocesan Event) 9461 Cabin/Staff 0 0 $20.00 $26.00 (Depending on if is Diocesan Event or NonDiocesan Event) 9461 Cabin 0 0 $18.00 $21.00 (Depending on if is Diocesan Event or NonDiocesan Event) Drew -----Original Message----- From: Klos, Susan [mailto:Susan.Klos at fldoe.org] Sent: Tuesday, May 06, 2003 1:51 PM To: 'accessd at databaseadvisors.com' Subject: [AccessD] FW: Query help please Here is the second part of the message -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030507/09836a53/attachment-0001.html>