Subquery

[combine21]

I am trying to use a subquery in which the movement of cattle from field number to field number will appear and I can make a new entry if needed. I made a query which contains all the animals and all their movements. How can I make the subquery just have the records for that specific animal pop up?

[ajetrumpet]

one thing to point out.

if you are using different fields to track movement of herds rather than records, the database may become overly burdensome in the near future. I would consider changing if you can.

on another note, there really isn't enough information about your actual setup for someone to give a clear answer. Assume that noone on this board has any knowledge about herding cattle.

[oldman]

There are two possible solutions depending on what you are really looking for Combine. If you have a record date with each animal's location so that a move from field A to field B today would have today's date on it, try setting up a table for each animal with its ID in it and then use the ID in your movement table. Now when you go into a form to move an animal, you enter today's date and the unique animal identifier. That data goes into a second table along with the animal's new location ID. Now create an easy to use form where you get to pick an animal ID and use that ID to draw records from the movement table. Sort the movement query by date and the results will make some sense. I do this kind of thing all the time. By sorting by date, the most recent move, and the current location will be at the bottom of the list, as we are used to reading things. The select criteria is provided quite simply. You set up the form to contain a list box and tell the list box wizard that you want to use the data elsewhere. Then you use the query builder to build a query based on fields in loaded forms, the one where your list box is located should be open when you build it. Just pick the field name off the list and you will be in business. The button that runs your query from the form simply runs the query that relies on which animal you have highlighted in the list box and sorts it by date.