Adding a Query to a Query?

[Bob Vance]

I have a Query1 that say has 18 Horses Filtered , I have another query2 that
has 9 of those horses same horses filtered
can I add Query2 to Query1 and get an astrix(*) in query1 to show which
horses are not Filtered or Filtered either way, so 9 Horses will have an
astrix in Query1

 

[Ken Snell [MVP]]

Perhaps this (assuming that both queries have the same type and number of
fields; else replace the .* with specific fieldnames that you want to show):

SELECT Query1.*, "*" AS NotInOtherQuery, "Query1" AS "QueryName"
FROM Query1 LEFT JOIN Query2 ON Query1.HorseID = Query2.HorseID
UNION ALL
SELECT Query2.*, "*" AS NotInOtherQuery, "Query2" AS "QueryName"
FROM Query1 RIGHT JOIN Query2 ON Query1.HorseID = Query2.HorseID

 

[Bob Vance]

Thanks Ken could I use something like this instead, so as to enter * into
TblHorseInfo.Holding, and I could use another Excute to clear "" Holding
when not in holding??
CurrentDb.Execute "UPDATE tblHorseInfo " & _
"SET Holding = "*" " & _
"WHERE HorseID = " & lngID, dbFailOnError

 

[Douglas J. Steele]

I would recommend strongly against storing that information in a table. As
fellow MVP John Vinson likes to say, "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."
(Yes, I realize you're not storing a total, but the principle's the same)

If you're determined, you can use

CurrentDb.Execute "UPDATE tblHorseInfo " & _
"SET Holding = "*" " & _
"WHERE HorseID IN (SELECT DISTINCT HorseID " & _
"FROM Query1) ", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)