Need Help with Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve Haack - 19 Jul 2006 19:02 GMT

I need some help setting up a query. Here are the details:

I have the following tables:

tblLots (this is a site in a campground)
ID: AutoNuber (primary key)
LotNumber (the number of the lot in the park)
LotSteeet (the number and street for the address of the lot)
LotOwner (Lookup in People table)
LotRenter (Lookup in People table)
note: I do not store the city,st,zip since they never change in the park.

tblPeople (people who own or rent in the park)
ID: AutoNumber (primary key)
FirstName
LastName
Spouse

Then I have a query for updating information about a given lot. In the
query, I prompt for the lot number and then display that record. I have it
return the fields from the lot record, but I also use the following
expression to return a "display" name for the current lot owner (and it works
just fine):

CurrentOwner: [LastName] & ", " & [FirstName] & (" and "+[Spouse])

What I am not sure how to do is to build a similiar display name for the lot
renter. Basically, I have a lot record which has two related people records,
and in the query, I need to be able to get both of the people records and I
am not sure how to get the other one.

Thanksk for assistance,
Steve

Reply to this Message

Lucas Kartawidjaja - 19 Jul 2006 19:48 GMT

Hi Steve

If I understand it correctly, you should be able to get the result that you
want by using this query:

SELECT B.[LastName] & ", " & B.[FirstName] & " and "+ B.[Spouse] AS
[CurrentOwner],
C.[LastName] & ", " & C.[FirstName] & " and "+ C.[Spouse] AS [CurrentRenter]
FROM tblLots A, tblPeople B, tblPeople C
WHERE A.LotOwner = B.ID AND A.LotRenter = B.ID;

Hopefully that works.

Lucas

> I need some help setting up a query. Here are the details:
>
[quoted text clipped - 29 lines]
> Thanksk for assistance,
> Steve

Reply to this Message

Steve Haack - 19 Jul 2006 21:27 GMT

This is returning a dataset with the colums "CurrentOwner" and
"CurrentRenter" but with no records. If I take out the WHERE statement, then
I get a list of records with the each person associated with each other as
the owners and renters like this (but with many more):

CurrentOwners CurrentRenters
Jim Jim
Jim Joe
Jim Bob
Joe Jim
Joe Joe
Joe Bob
Bob Jim
Bob Joe
Bob Bob

Am I doing something wrong?

Steve

> Hi Steve
>
[quoted text clipped - 44 lines]
> > Thanksk for assistance,
> > Steve

Reply to this Message

Steve Haack - 19 Jul 2006 21:30 GMT

Also, how do I enter this SELECT query into the grid of a query that I
already have so that my Prompt for the lot number works? Or how do I include
that Promot into this query?

Steve

> This is returning a dataset with the colums "CurrentOwner" and
> "CurrentRenter" but with no records. If I take out the WHERE statement, then
[quoted text clipped - 64 lines]
> > > Thanksk for assistance,
> > > Steve

Reply to this Message

Lucas Kartawidjaja - 19 Jul 2006 21:36 GMT

There are several ways that I could think of. First you can try to edit the
query that you have, by clicking on the SQL view (if you need help, just
posted the query and we could try to work it out). Or you can try:

SELECT B.[LastName] & ", " & B.[FirstName] & " and "+ B.[Spouse] AS
[CurrentOwner],
C.[LastName] & ", " & C.[FirstName] & " and "+ C.[Spouse] AS [CurrentRenter]
FROM tblLots A, tblPeople B, tblPeople C
WHERE A.LotOwner = B.ID AND A.LotRenter = B.ID AND A.ID = [Lot Number];

Lucas

> Also, how do I enter this SELECT query into the grid of a query that I
> already have so that my Prompt for the lot number works? Or how do I include
[quoted text clipped - 70 lines]
> > > > Thanksk for assistance,
> > > > Steve

Reply to this Message

Lucas Kartawidjaja - 19 Jul 2006 21:40 GMT

Sorry another typo, try this instead (I guess time for a coffee break =):

SELECT B.[LastName] & ", " & B.[FirstName] & " and "+ B.[Spouse] AS
[CurrentOwner],
C.[LastName] & ", " & C.[FirstName] & " and "+ C.[Spouse] AS [CurrentRenter]
FROM tblLots A, tblPeople B, tblPeople C
WHERE A.LotOwner = B.ID AND A.LotRenter = C.ID AND A.ID = [Lot Number];

Lucas

> There are several ways that I could think of. First you can try to edit the
> query that you have, by clicking on the SQL view (if you need help, just
[quoted text clipped - 82 lines]
> > > > > Thanksk for assistance,
> > > > > Steve

Reply to this Message

Lucas Kartawidjaja - 19 Jul 2006 21:31 GMT

Opps. Sorry about that. I have a typo on the WHERE clause. It should be:

SELECT B.[LastName] & ", " & B.[FirstName] & " and "+ B.[Spouse] AS
[CurrentOwner],
C.[LastName] & ", " & C.[FirstName] & " and "+ C.[Spouse] AS [CurrentRenter]
FROM tblLots A, tblPeople B, tblPeople C
WHERE A.LotOwner = B.ID AND A.LotRenter = C.ID;

Hopefully it works now.

Lucas

Reply to this Message

Steve Haack - 19 Jul 2006 22:53 GMT

OK, This last version does work. Now, obviously in this case, every lot has
an owner, but not all lots have a renter.

This query is returning me only those lots that have and owner and a renter.

When our book keeper has to do maintenence on either the owner or renter of
a lot (either we have an owner that sold his lot someone else, or they have a
new renter) I want to have a form come up for displaying and changing the
info. What will happen is the form will prompt for a lot number, and then
display the information (right now I have a form based on a query which takes
the lot number as an input from the user). Most lots do not have a renter, so
I need to be able to get the renter information only when there is a renter.
What your query is doing is returning only the cases where there is both and
owner and a renter. Does this make sense?

Thanks so much for your assistance here.

> Opps. Sorry about that. I have a typo on the WHERE clause. It should be:
>
[quoted text clipped - 76 lines]
> > > > Thanksk for assistance,
> > > > Steve

Reply to this Message

Lucas Kartawidjaja - 20 Jul 2006 04:04 GMT

Oh ok. I totally forgot about that scenario. We can actually modify the query
to:

SELECT B.[LastName] & ", " & B.[FirstName] & " and "+ B.[Spouse] AS
[CurrentOwner],
C.[LastName] & ", " & C.[FirstName] & " and "+ C.[Spouse] AS [CurrentRenter]
FROM (tblLots A
LEFT JOIN tblPeople B ON A.LotOwner = B.ID)
LEFT JOIN tblPeople C ON A.LotRenter = C.ID
WHERE A.ID = [Lot ID];

Basically I change the join to outer join. It should address the problem.

Hopefully that works.

Lucas

> OK, This last version does work. Now, obviously in this case, every lot has
> an owner, but not all lots have a renter.
[quoted text clipped - 93 lines]
> > > > > Thanksk for assistance,
> > > > > Steve

Reply to this Message