[AccessD] FW: Query help please

Here is the second part of the message


 
 
I need to create a query that will for any event show all the records from
tlnklodgingEvent and all records from tblLodging that are not used in
tlnklodgingEvent
For example:
EventID        LodgingType        Guests        NoNights        CostPerUnit
Total        Day
9461          Single                    8                    1
$50.00           $400        Mon
9461          Single                    9                    1
$50.00           $400        Tue
9461          Single                    6                    1
$50.00           $400        Wed
9461          Double                  22                   1
$50.00           $400        Mon
9461          Double                  22                   1
$50.00           $400        Tue
9461          Double                  30                   1
$50.00           $400        Wed
9461          Double                    2                   1
$50.00           $400        Thur
This of course is easy.  The rest of the query has to look like this even
though there are no records in tlnkEvent:
I think I need the query to put in the EventID which can be gotten from
forms!frmEvent!InvoiceNo
(9461)        Triple
$21.34 or $25.33 (Depending on if is Diocesan Event or NonDiocesan Event)
(9461)        Quadruple
$17.50 or $20.50                                        "
(9461)        Cabin
$18.00 or $21.00                                        "
(9461)        Cabin/Staff
$20.00 or $26.00                                        "
(9461)        RV
$20.50 or $20.50                                        "
(9461)        Tent
$10.00 or $10.25                                        "
 
I got the following query to do this for me.  But then I found out that if
there is no lodging, the report still has to show all of the entries in
tblLodging.  Also if an event has not Singles or doubles, then singles and
doubles still have to show on the report.  This query doesn't seem to do all
that for me.
 
SELECT IIf(IsNull([EventId]),[forms]![frmevent]![invoiceno],[EventID]) AS
Invoiceno, tblLodging.LodgingID, tlnklodgingEvent.EventID, tblLodging.Type,
tblLodging.NDRate, tblLodging.DRate, tlnklodgingEvent.CostPerUnit,
IIf(IsNull([costperunit]) And
[forms]![frmEvent]![txtDiocesan]="NonDiocesan",[ndrate],IIf(IsNull([costperu
nit]) And
[forms]![frmEvent]![txtDiocesan]="Diocesan",[drate],[costperunit])) AS Rate,
Weekday([lodgingdate],1) AS LodgingDay, tlnklodgingEvent.Guests,
tlnklodgingEvent.NoNights, tlnklodgingEvent.total, tblLodging.Ordr
FROM tblLodging LEFT JOIN tlnklodgingEvent ON tblLodging.LodgingID =
tlnklodgingEvent.LodgingID;

I sure hope someone out there can help me.  TIA
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030506/4e2eaccd/attachment-0001.html>