Append Query

[conormooney]

I have two tables which i want to use in my query.

tbl_herd (AnimalID, EarTagNumber, EarofTagging, Colour, Breed, Sex, DOB, Deceased, Status)

tbl_deceased/moved (AnimalID, EarTagNumber, EarofTagging, Colour, Breed, Sex, DOB, Deceased, Status)

I want my query to search through table herd and select all those animals which are deceased and put them in my table decease/moved and i also want them to select from the status field, which gives a drop down menu of In Herd, Left Herd, Restricted and select all those animals that are 'Left herd' and put their records into my table deceased/moved.

any ideas?

 

[pbaldy]

How about

INSERT INTO tbl_deceased/moved
SELECT * FROM tbl_herd
WHERE Deceased = True

and

...
WHERE Status = "Left herd"

Of course, we could have a discussion about whether you really need these in a separate table. Generally you would not, but you know your situation best. I assume you'd also want to delete them from the first table after appending to the second.

 

[dcx693]

Why do you keep the info in 2 separate tables? Looks like the structure is exactly the same. Why not just use the status field that you have to differentiate instead of moving data between tables?

 

[conormooney]

well i need a record of all the animals i keep but after a while the main table will get too full and i want just the animals i have currently in the herd in that table.

 

[conormooney]

yes i want to delete them from the old table. where am i supposed to enter the code you gave me into? im very new to this

 

[dcx693]

well i need a record of all the animals i keep but after a while the main table will get too full and i want just the animals i have currently in the herd in that table.

The main table will get too full? Well, I understand that one large table will perform more slowly than two smaller tables, but how many records are we expecting? Is it a performance issue you're worried about? Or an aesthetic one?

 

[conormooney]

its not really that its too full, i just want them in two separate tables.

 

[dcx693]

That's a valid preference. Just realize that it's just more functionally complicated. If you kept them all in one table, you could just use a saved query to view just the category or categories you wanted.