Don't understand posted update query answers

T

terryc

Thanks Gina for all your help.

I'm still struggling to get a query that returns what I need. I think I need
to understand joins better. I'm going to search the posts for help. You might
see me post again :)

Gina Whipp said:
Terry,

I believe that is because you have a one to many for phone numbers. Each
person can have many phone numbers. It is not because those archived people
are showing up. To prove that find the name of an Archived person and see
if they are in your final table. They will not show. Now find the name of
someone who has three phone numbers, they show three times? See the
problem? Perhaps you need a way to identify a persons Main number thereby
limiting that Cell table to one number per person.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

terryc said:
Gina, I'm getting so confused myself! :)

The second query is not working correctly. It is giving me people who
should
have been archived and eliminated by the embedded query. I made your 2
suggested changes: change inner to left join and delete the WHERE clause.
The
query now returns all 2500 records.

The False stated is in the first query and when the query is embedded into
the second, the statement came along with the first query. Or so it seems
to
me.

So here's a question: is it possible to have a query that involves 5
tables
return the exact data you want without embedding queries?

You're being so patient - thank you!

Gina Whipp said:
Terry,

I am not so sure the second query is working correctly. You have an
INNER
JOIN which indicates it's only going to show you records that are in both
objects. You would need a LEFT JOIN on that so you could get all the
reocrds...

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID, Cell.Cell, Cell.Email
FROM ([qrymaster_resident/family_address_list] LEFT JOIN Relationships ON
[qrymaster_resident/family_address_list].People_ID =
Relationships.People_ID) LEFT JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID = Cell.People_ID
WHERE ((([qrymaster_resident/family_address_list].Archive)=False))
ORDER BY Relationships.Resident;


....I am also confused as to why you have FALSE in two places. It's
already
set as the Criteria in the first query, so why do you need it again? The
below should work just fine...

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID, Cell.Cell, Cell.Email
FROM ([qrymaster_resident/family_address_list] LEFT JOIN Relationships ON
[qrymaster_resident/family_address_list].People_ID =
Relationships.People_ID) LEFT JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID = Cell.People_ID
ORDER BY Relationships.Resident;


....And lastly, not sure what you mean by showing you records it
shouldn't
be. If it's got a People_ID from the first query (that is not Archived)
then why shouldn't is show you records in your second/final query?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I'm sorry Gina. I shouldn't be mixing 2 problems at the same time.
Original problem:
Here's the 1st query which is running correctly.

SELECT Entity.Last_Name, Trim([Prefix] & " " & [First_Name] & " " &
[Last_Name]) AS Name, Entity.People_ID, Entity.First_Name,
Entity.Prefix,
Entity.Spouse, Address.Address1, Address.Address2, Trim([City] & " " &
[State] & " " & [Zip]) AS Address3, Entity.Archive, Entity.Greeting,
Address.Zip, Address.Address_Type, Address.Phone
FROM Entity LEFT JOIN Address ON Entity.People_ID = Address.People_ID
WHERE (((Entity.Archive)=False));

The above query is then inserted to the following query that is not
working
correctly:

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID, Cell.Cell,
Cell.Email
FROM ([qrymaster_resident/family_address_list] INNER JOIN Relationships
ON
[qrymaster_resident/family_address_list].People_ID=Relationships.People_ID)
LEFT JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID=Cell.People_ID
WHERE ((([qrymaster_resident/family_address_list].Archive)=False))
ORDER BY Relationships.Resident;

The Archive=False is not working correctly. It is giving me records
from
the
relationship table that should be excluded.

Thank you.





:

Terry,

Are you saying now it works or it still not working?

Why does this AND instead of OR?

WHERE (((Entity.Archive)=False) AND ((Entity_type.Entity_Type)="R"))
OR

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

The Where Archive=false is in the
qrymaster_resident/family_address_list
which is embedded into the
qrymaster_resident/family/relationship_list.
When
I run the query by itself, it returns only trues. When the qry is
inserted
into the other query, it returns true and false.

In my new query (qryResident_Family_Mailing_List) I omitted the
archive
criteria. I've corrected it below. I'm trying to avoid embedding a
queries.

SELECT Entity.People_ID, Trim([Prefix] & " " & [First_Name] & " " &
[Last_Name]) AS Name, Entity.Spouse, Entity.Archive,
Entity_type.Entity_Type,
Address.Address1, Address.Address2, Trim([City] & " " & [State] & "
" &
[Zip]) AS Address3, Address.Phone, Cell.Cell, Cell.Email,
Relationships.Relationship, Relationships.Resident,
Relationships.Resident_ID
FROM (((Entity INNER JOIN Address ON Entity.People_ID =
Address.People_ID)
INNER JOIN Cell ON Entity.People_ID = Cell.People_ID) INNER JOIN
Entity_type
ON Entity.People_ID = Entity_type.People_ID) LEFT JOIN Relationships
ON
Entity.People_ID = Relationships.People_ID
WHERE (((Entity.Archive)=False) AND ((Entity_type.Entity_Type)="R"))
OR
(((Entity_type.Entity_Type)="M")) OR
(((Entity_type.Entity_Type)="F"));



:

I do not see a WHERE Archive = False in your previous statement...
What
did
you change? Maybe copy/paste what you have changed it to?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Spoke to soon. Where archive = false isn't working. Getting true
and
false
records.

:

Oops, forgot, did you try a LEFT JOIN?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know,
information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

With the cell table i get 185 records. Without I get 747. Is
this
what
you
need?

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID, Cell.Cell,
Cell.Email
FROM ([qrymaster_resident/family_address_list] INNER JOIN
Relationships
ON
[qrymaster_resident/family_address_list].People_ID =
Relationships.People_ID)
INNER JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID =
Cell.People_ID
ORDER BY Relationships.Resident;


:

Terry,

Let's just fix the query then. Can you copy/paste what you
have
that
is
not
working for you here? Because I honestly thing combining the
tables
Click to expand...
Click to expand...
Click to expand...
 
G

Gina Whipp

Terry,

While you are looking around the boards, have a look at

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

Pay special attention to Section 6!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
terryc said:
Thanks Gina for all your help.

I'm still struggling to get a query that returns what I need. I think I
need
to understand joins better. I'm going to search the posts for help. You
might
see me post again :)

Gina Whipp said:
Terry,

I believe that is because you have a one to many for phone numbers. Each
person can have many phone numbers. It is not because those archived
people
are showing up. To prove that find the name of an Archived person and
see
if they are in your final table. They will not show. Now find the name
of
someone who has three phone numbers, they show three times? See the
problem? Perhaps you need a way to identify a persons Main number
thereby
limiting that Cell table to one number per person.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

terryc said:
Gina, I'm getting so confused myself! :)

The second query is not working correctly. It is giving me people who
should
have been archived and eliminated by the embedded query. I made your 2
suggested changes: change inner to left join and delete the WHERE
clause.
The
query now returns all 2500 records.

The False stated is in the first query and when the query is embedded
into
the second, the statement came along with the first query. Or so it
seems
to
me.

So here's a question: is it possible to have a query that involves 5
tables
return the exact data you want without embedding queries?

You're being so patient - thank you!

:

Terry,

I am not so sure the second query is working correctly. You have an
INNER
JOIN which indicates it's only going to show you records that are in
both
objects. You would need a LEFT JOIN on that so you could get all the
reocrds...

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID, Cell.Cell,
Cell.Email
FROM ([qrymaster_resident/family_address_list] LEFT JOIN Relationships
ON
[qrymaster_resident/family_address_list].People_ID =
Relationships.People_ID) LEFT JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID = Cell.People_ID
WHERE ((([qrymaster_resident/family_address_list].Archive)=False))
ORDER BY Relationships.Resident;


....I am also confused as to why you have FALSE in two places. It's
already
set as the Criteria in the first query, so why do you need it again?
The
below should work just fine...

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID, Cell.Cell,
Cell.Email
FROM ([qrymaster_resident/family_address_list] LEFT JOIN Relationships
ON
[qrymaster_resident/family_address_list].People_ID =
Relationships.People_ID) LEFT JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID = Cell.People_ID
ORDER BY Relationships.Resident;


....And lastly, not sure what you mean by showing you records it
shouldn't
be. If it's got a People_ID from the first query (that is not
Archived)
then why shouldn't is show you records in your second/final query?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I'm sorry Gina. I shouldn't be mixing 2 problems at the same time.
Original problem:
Here's the 1st query which is running correctly.

SELECT Entity.Last_Name, Trim([Prefix] & " " & [First_Name] & " " &
[Last_Name]) AS Name, Entity.People_ID, Entity.First_Name,
Entity.Prefix,
Entity.Spouse, Address.Address1, Address.Address2, Trim([City] & " "
&
[State] & " " & [Zip]) AS Address3, Entity.Archive, Entity.Greeting,
Address.Zip, Address.Address_Type, Address.Phone
FROM Entity LEFT JOIN Address ON Entity.People_ID =
Address.People_ID
WHERE (((Entity.Archive)=False));

The above query is then inserted to the following query that is not
working
correctly:

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID, Cell.Cell,
Cell.Email
FROM ([qrymaster_resident/family_address_list] INNER JOIN
Relationships
ON
[qrymaster_resident/family_address_list].People_ID=Relationships.People_ID)
LEFT JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID=Cell.People_ID
WHERE ((([qrymaster_resident/family_address_list].Archive)=False))
ORDER BY Relationships.Resident;

The Archive=False is not working correctly. It is giving me records
from
the
relationship table that should be excluded.

Thank you.





:

Terry,

Are you saying now it works or it still not working?

Why does this AND instead of OR?

WHERE (((Entity.Archive)=False) AND
((Entity_type.Entity_Type)="R"))
OR

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

The Where Archive=false is in the
qrymaster_resident/family_address_list
which is embedded into the
qrymaster_resident/family/relationship_list.
When
I run the query by itself, it returns only trues. When the qry is
inserted
into the other query, it returns true and false.

In my new query (qryResident_Family_Mailing_List) I omitted the
archive
criteria. I've corrected it below. I'm trying to avoid embedding
a
queries.

SELECT Entity.People_ID, Trim([Prefix] & " " & [First_Name] & " "
&
[Last_Name]) AS Name, Entity.Spouse, Entity.Archive,
Entity_type.Entity_Type,
Address.Address1, Address.Address2, Trim([City] & " " & [State] &
"
" &
[Zip]) AS Address3, Address.Phone, Cell.Cell, Cell.Email,
Relationships.Relationship, Relationships.Resident,
Relationships.Resident_ID
FROM (((Entity INNER JOIN Address ON Entity.People_ID =
Address.People_ID)
INNER JOIN Cell ON Entity.People_ID = Cell.People_ID) INNER JOIN
Entity_type
ON Entity.People_ID = Entity_type.People_ID) LEFT JOIN
Relationships
ON
Entity.People_ID = Relationships.People_ID
WHERE (((Entity.Archive)=False) AND
((Entity_type.Entity_Type)="R"))
OR
(((Entity_type.Entity_Type)="M")) OR
(((Entity_type.Entity_Type)="F"));



:

I do not see a WHERE Archive = False in your previous
statement...
What
did
you change? Maybe copy/paste what you have changed it to?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know,
information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Spoke to soon. Where archive = false isn't working. Getting
true
and
false
records.

:

Oops, forgot, did you try a LEFT JOIN?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know,
information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

With the cell table i get 185 records. Without I get 747.
Is
this
what
you
need?

SELECT [qrymaster_resident/family_address_list].People_ID,
[qrymaster_resident/family_address_list].Name,
[qrymaster_resident/family_address_list].Spouse,
[qrymaster_resident/family_address_list].Address1,
[qrymaster_resident/family_address_list].Address2,
[qrymaster_resident/family_address_list].Address3,
[qrymaster_resident/family_address_list].Archive,
[qrymaster_resident/family_address_list].Zip,
[qrymaster_resident/family_address_list].Phone,
Relationships.Relationship,
Relationships.Resident, Relationships.Resident_ID,
Cell.Cell,
Cell.Email
FROM ([qrymaster_resident/family_address_list] INNER JOIN
Relationships
ON
[qrymaster_resident/family_address_list].People_ID =
Relationships.People_ID)
INNER JOIN Cell ON
[qrymaster_resident/family_address_list].People_ID =
Cell.People_ID
ORDER BY Relationships.Resident;


:

Terry,

Let's just fix the query then. Can you copy/paste what
you
have
that
is
not
working for you here? Because I honestly thing combining
the
tables
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query 6
Add Fields from Record 1
Append two tables into a query 1
Is there a quick way to create an Update query? 3
Convert datatype using query 7
Easy Query Question 2
Compare Two Tables 1
Append query - Append to table with Autonumber field 2

Top