<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<META content="MSHTML 5.00.3502.4856" name=GENERATOR></HEAD>
<BODY>
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT size=2><FONT
color=#0000ff><SPAN class=609155118-06052003>Here is the second part of the
message</SPAN><BR><BR></FONT></FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003>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</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003>For
example:</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN
class=984463817-06052003>EventID
LodgingType
Guests
NoNights
CostPerUnit
Total Day</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN
class=984463817-06052003>9461 Single
8
1
$50.00 $400
Mon</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003>9461 Single 9
1
$50.00 $400
Tue</SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003>9461 Single 6
1
$50.00 $400
Wed</SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>9461 Double 22
1
$50.00 $400
Mon</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003>9461 Double 22 1
$50.00 $400
Tue</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003>9461 Double 30 1
$50.00 $400
Wed</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>9461 Double 2 1
$50.00 $400
Thur</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003>This of course is easy. The rest of
the query has to look like this even though there are no records in
tlnkEvent:</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003>I think I need the query to put in the
EventID which can be gotten from
forms!frmEvent!InvoiceNo</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>(9461)
Triple
$21.34 or $25.33 (Depending on if is Diocesan Event or NonDiocesan
Event)</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>(9461)
Quadruple
$17.50 or
$20.50
"</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>(9461)
Cabin
$18.00 or
$21.00 "</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>(9461)
Cabin/Staff
$20.00 or
$26.00
"</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>(9461)
RV $20.50
or
$20.50
"</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003>(9461)
Tent $10.00
or
$10.25
"</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT> </DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003>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.</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN
class=984463817-06052003></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT> </DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003>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([costperunit])
And [forms]![frmEvent]![txtDiocesan]="Diocesan",[drate],[costperunit])) AS Rate,
Weekday([lodgingdate],1) AS LodgingDay, tlnklodgingEvent.Guests,
tlnklodgingEvent.NoNights, tlnklodgingEvent.total, tblLodging.Ordr<BR>FROM
tblLodging LEFT JOIN tlnklodgingEvent ON tblLodging.LodgingID =
tlnklodgingEvent.LodgingID;<BR></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003><FONT size=2><SPAN
class=984463817-06052003><FONT size=2><SPAN class=984463817-06052003><FONT
size=2><SPAN class=984463817-06052003>I sure hope someone out there can help
me.
TIA</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV></BODY></HTML>