Make-Table Query

[conormooney]

I have a main table:
Tbl_herd (Ear_tagNumber, Breed, Sex, DOB, DAM's Ear Tag Number, Deceased)

DAM=Mother

Each mother has a record in this table as well. I want the query to search through allthe DAM's and from this, search through all the Ear-Tag Numbers and pull out their record, so at the end i will have a table with all the DAM's Record

Any ideas?

 

[dcx693]

I think I have an idea of what you want, but can you post some sample data with the output you'd like?

 

[neileg]

Create a select query returning just [DAM's Ear Tag Number] and use Is Not Null as a criterion. Create a new query, add in the first query and Tbl_herd. Join the query [DAM's Ear Tag Number] to [Tbl_herd].[Ear_tagNumber] with a left join (ie return all records in the query and only those from the table that match). Uncheck the Show box on [DAM's Ear Tag Number]. Run the query. Voila!

You may want to limit the results based on the [Deceased] field, either for the dams or the offspring.

 

[neileg]

Why a make table query, by the way? There's no need. Just save the query and run it every time you need to know all the dams. Otherwise it will be out of date as soon as you add one more record to your main table.