Query Criteria

[combine21]

I am fairly new to access and was needing some help on criteria in a query. In my database I have a specific group of cattle (group A) in which some are still in this group and some are not due to health. This specific group of cattle (group A) move frequently in which their movement is recorded as a new record every move. If that cow is pulled from the group and no longer in group A it is noted in that record of movement as DIS. In my query I am wanting to pull together all of the collected cattle from group A whether they are currently still there or not. I want my query to find the record which contains DIS per animal, and also the cattle that are still in group A that dont have a record of DIS. I was using like"DIS" in the criteria to find the ones that have been pulled, but what do to find the ones still in group A???

Help please....

[weekend00]

use
where [fieldname] is null
instead of
were [fieldname] = "DIS"

(I suggest you use =, not like)

[weekend00]

I remember maybe you were using query design mode, so, just put null instead of like "DIS", in the same place

[combine21]

I think I need to explain my queries a little more in depth. My query comes from 3 other queries combined. For the specific field in which Im trying to get data from come from records on a different table called movement, which has every movement by a specific cow throughout its life. If a cow who was in Group A was pulled due to health, it is noted in the comments of that specific record entry as DIS. I have a query which lists all the cattle from Group A that were pulled in which I can specify is the field "movenote" in the criteria as =DIS. This pulls the one specific record out of all their movement. In the query im trying to create I want all the cattle that were ever in Group A. And in this query I want a movenote just like the other query. Except in the case there will be those that have been pulled (DIS) and those that haven't. Those that havent been pulled should have a blank value in movenote. Is this possible? And my fear is that if I can get a list of all these animals, the ones that dont have any movement entry of DIS, it will return all their movement records that were blank in the comments. I want one entry per animal.

[weekend00]

did you mean that an animal may have some entries with blank comments and one with DIS? in this case you need a sub query to get it done.

Suppose you tblAnimal table has these fields:
animalID movement comments ......

Following queries will give the animalID of those animals which have no entry has DIS in comments.

query1: animalwithDIS
select animalID from tblAnimal where comments="DIS" group by animalID

query2: AnimalwithoutDIS
select tblanimal.animalID from tblanimal left join animalwithDIS on tblanimal.animalID=animalwithDIS.animalID where animalwithDIS.animalID is null group by tblanimal.animalID

[combine21]

I have the first query already together taht contains all the pulled animals. Im confused as to how to set up the second query. From tblanimal ill pull AnimalID into the design. Ill join the tblanimal.animalID to animalwithDIS.animalID together?

[combine21]

For this query ive had to pull my tbl_movement twice into the query design. The one tbl contains comments which pertain to the animal getting into Group A in the first place which in that specific entry at that time had SPF in its comments. Later on when the animal was pulled from group A the comments on that entry read DIS notifying when it was pulled from the group. So in the query that I have designed to gather all the pulled animals from group A, their movement is noted twice from the same table. The first field from tbl_movement1 looks for SPF which gives me all the animals brought into the group. Then my second field from tbl_movement2 looks for DIS. So out of all the cattle, the query shows the ones brought into group A but were pulled. I know you tried explaining how to create a query to get the ones without DIS, but im afraid if I put null it will still show records for cattle that have been pulled but happened to have a record that didnt have comments during their movement while in the group.

[weekend00]

My second query won't pull any record for animal with any record that comments "DIS".
if an animal has many records, and any one of the records has comments "DIS", then this animal won't be pulled out.

[combine21]

Well I decided to go about getting the 2 queries a different way. Is it possible taht if I have the same names for both queries, even if their data comes from different ways to combine them? Could I merge the fields together to create the one ultimate query?

[weekend00]

you can use UNION to put the records from two queries toghter.

select a,b,c from t
union
select a,b,c from t1

[shexe]

you can do a union query but you need to make sure that both queries have the same fields and field names. meaning if qry1 has CowName and qry2 does not, you need to create a field in qry2 as CowName, even if there is no data the headings need to match.

[combine21]

Both queries have the exact same field names. How I got the data into the query differs from each tho. Is that a problem? And I have no experience with union queries. How do i join them? My queries are qry_SPF_disposition_all and qry_SPF_current.

[shexe]

weekend00 answered that in an earlier post.

SELECT qry_SPF_disposition_all.Field1, qry_SPF_disposition_all.Field2, etc.
FROM qry_SPF_disposition_all

UNION SELECT

qry_SPF_current.Field1,qry_SPF_current.Field2, etc.

FROM qry_SPF_current
ORDER BY Field1;