Appending only records which have changed.

[Miquel1]

Hi all,


I'm trying to append to a table records from a query where only the records which have changed since the last time the query was run are appended.

I have a query which counts and displays the number of animals in a specific colony (based on a table CALLED "MostRecentColony" where the fields are [PREVIOUS_COLONY], [CURRENT_COLONY] and [ANIMAL_ID]), excluding those whose [CURRENT_COLONY] is recorded as "Dead".

SELECT MostRecentColony.[CURRENT_COLONY], Count(MostRecentColony.[CURRENT_COLONY]) AS [CountOfCURRENT_COLONY]
FROM MostRecentColony
GROUP BY MostRecentColony.[CURRENT_COLONY]
HAVING (((MostRecentColony.[CURRENT_COLONY])<>"dead"));

I want to have a table which displays this data and is appended to, but only when the colony size (ie [CountOfCURRENT_COLONY] has changed since the last time the query was run. In this way I will be able to keep a running track of how colony sizes have changed over time.

Any ideas?

M

[rpeare]

why would you do that? if you are keeping a history table that has the colony size over time you do not need to append this information to another table. Or are you saying that you are not keeping track (in your tables) of the colony size over time you are just updating the same field with a 'new' value every time you do a colony count?

[Miquel1]

Ah, I made a typo - the MostRecentColony is not a table, but a query. The source for this query is the table MainColonyMembership, which records the date animals were moved and where they were moved to. MostRecentColony tells me which animal is in which colony at any one time. Then my CountOfMostRecentColony (code above) runs off this to tell me how many animals are in each colony at present.
So I can press a button to find out how many animals are in Group XX now, but if I wanted to know how many were in Group XX last month, I'd need a query that appends to a table the colony size whenever an animal changes colony. This is easy to do, but only if I want to append every colony & size whenever an animal changed colony - I can't work out how to append only the ones which have changed. Am I going about this the wrong way?

[rpeare]

No you wouldn't need to do that at all if you are recording individuals being introduced to a colony and what date they were introduced to that colony you can produce a report by month that shows how many were added to the colony and the colony size (running sum), or at worst produce a report that shows the population at the beginning of the 'current' month and the population at the end of the current month. In this case there's no reason to append information to a temporary table or history table since you're already recording everything you need.