Improving an existing procedure

[simon4amiee]

Hi Guys again,

As some of you are more than helpful I thought I would ask the question here.

I have currently taken on a procedure which I feel is very long winded and was hoping for some inspiration here to streamline it.

Using just one table I have to run a query which has a date parameter only in the criteria. I run this query many times every week for the same dates (which are WEEK COMMENCING FROM AND TO DATES). Its that simple and nothing to difficult.

What I get is the following

Dates No of Records

w/c 23/06 25
w/c 30/06 35
w/c 07/07 38
w/c 14/07 35
w/c 21/07 25
w/c 28/07 22

What I have to do is run this every report every week until the number of records are the same 3 weeks in a row. For example using the last one (w/c 28/07) as an example if I run this every week on the same day and I get the following records (week 1 = 22, week 2 = 24, week 3 = 25, week 4 = 25, week 5 = 26, week 6 = 26, week 7 = 26) I would stop running this report on week 7.

I then copy and paste all the records onto separate spreadsheets every week and send to the relevant personnel.

I also make a log on a separate spreadsheet of the number of records.

What I would like is to ONLY send out the additions (eg w/c 28/07 = 22 records, if the following week there is 24 records I want to just send out the additions and not the whole lot again, this is creating work for the people we send to as they have to then identify the additions manually.

Any ideas would be great I have my own such as make table, append (using primary key so only new ones go in etc), and a possible crosstab for the log.

 

[spikepl]

So what prevents a record having a date ? If you send out additions then a date would identify such records by having a date different from the previous records.

 

[simon4amiee]

So what prevents a record having a date ? If you send out additions then a date would identify such records by having a date different from the previous records.

The query itself is quite complex but in essence a whole range of criteria needs to be met to appear on this report. Our coding team update data routinely, and only when something is coded would the criteria needed to be met. So initially we may get 20 records but then as the coding gets done more may appear until we are at a stage when all the coding is completed and there will be no more additions, thus the 3 week rule when the records remain unchanged.

 

[spikepl]

I dont get it. You say that you issue records, and some time later add some new records not issued prevously. An issue date would id the new ones.