Challenging Access Problem

I posted this in Queries earlier... thought I would try here as well.

The scenario - I use MS Access to access an advantage database system from a program we use. I can either import or link to each table in the program's database via OBDC drivers.

The problem - I need to track the status changes of our customers. Tracking customer status changes are not recorded in the advantage db. The TRDATA table contains the information. From now on, I want my access db to track the changes and save it in access for report purposes. The status's available are A, B, I, K, L, O, P, W, X and Y.

The solution - So far, I have queried the TRDATA table to pull the Account #, Buyer, and status. I made a crosstab query which would kind of aggregate the data into a readable form. If the account is active, there would now be a 1 in the column A. I then made another query which would then update a table. From here, I'm unsure of what to do. Obviously, there needs to be a comparison of some sort. If only account 1234 changes from an A to a Y, then I want a new table to show all the other accounts where they stand and a change of Y for the status with the date it changed. I hope I'm explaining myself well enough!! I’m very unsure of where to go from here.

I wouldnt work of a crosstab, you want to log changes (on a daily basis?) on certain columns... Then do so... Simply log the changes...

Running an outerjoin query to find the unmatched columns and add them to your change table and include a timestamp. Over time you should see all the changes...

If you apply a multi field index set to No duplicates, you can simply append the data from TRDATA to your Access table. If an identical record already exists, it won't append. If the record has changed, it will be accepted.

Thats nice and all Nieleg, but what about if the record is changed back?

namliam said:

I wouldnt work of a crosstab, you want to log changes (on a daily basis?) on certain columns... Then do so... Simply log the changes...

Running an outerjoin query to find the unmatched columns and add them to your change table and include a timestamp. Over time you should see all the changes...

Click to expand...

Thank you for your help. I understand what you are saying. Sometimes I overcomplicate the problem and don't see the easiest solution. I can set the outerjoin query to update my change table with the latest changes.

How would I pull it into a report? I'll have a table with acct#'s duplicated. Is this where I could use the crosstab to show what status each account has been?

But what would you do with recuring statusses (Spelling?)

I dont know enough of the need and the recordset, so cannot really give a founded advice.
Yes a crosstab may be the way to go, or a sub report or a "group by" report.

namliam said:

Thats nice and all Nieleg, but what about if the record is changed back?

Click to expand...

I assume you have a date/time for the change of status? In which case the record won't be identical even if the status reverts to a previous value.

namliam said:

But what would you do with recuring statusses (Spelling?)

Click to expand...

Strictly speaking it should be stati.

I want to thank both of your for you help. I have found a wealth of knowledge thanks to this forum. All of the Access books in the world can't teach me what I have learned from help from all the members.