design question for day by day tracking of accounts in a certain state

[alexfittyfives]

Hi,

I've recently taken over responsibility for a daily report that tracks the total volume and value of accounts in particular state (suspended). The report needs to simply give details of the volume and value of accounts moving into and out of this suspended state day by day.

We are sent list of accounts that are suspended on a daily basis. Traditionally this list has simply been imported into a new table in an access database each day and compared to the previous days table.

This is a horrible solution for reasons of scale if nothing else. Accounts can potentially move in and out of the suspended state a number of times so the I was thinking of a table that stored a start date and end date for each instance of an account becoming suspended.

This idea has met considerable resistance at work, is there a better way?

thanks

Alex

 

[lespaul]

what kind of details are in the report and what results do you need to extract from them?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ceh4702]

What might be a nice idea is a program that copied each record over to a temporary table every time they become suspended. It can be triggered whenever the suspended flag is turned on or something like that. I am thinking a process that runs every time a field is changed. Then you run a process that copies the temp data down to a pc or a server for your report to be based on or just run the report based on the temp table and then you can export the table elements and delete them as you are doing it.

Of course if they want something done a certain way sometimes you just have to do it their way. If you do it your way you may get too much information. It makes me wonder if they want a snapshot in time or just a list of anyone who is suspended in the last 24 hours.

This really begs for some kind of program that runs at night or early in the morning. It would be nice if you could totally automate the process so as you walk in the door it is sitting on the printer.

If you do not like my post feel free to point out your opinion or my errors.