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
is
not
a good solution.
--
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 set the tables up separately for the very reason you
mention.
But I
can't
get my queries to produce valid data and I got tired of
fighting
with
the
extra table. I thought one less table is one less problem.
I
struggle
with
joins, and unupdateable queries.
I want to be able to pull from entity, entity_type,
addresses,
cell
and
relationship tables and come up with a complete record.
Entity_types
are
codes for whether the entity is a resident, board member,
contributor
or a
family - a method to sort for different lists. Relationship
is
one
to
many
as
a resident can be related to many. The primary key in all
the
tables
is
peopleID. In the relationship table I also have a field
residentId
which
is
the resident's peopleID.
Last night I experimented with a query within a query.
Thanks
for
you
help.
:
Terry,
