automating repititious queries

  • Thread starter Thread starter Tcs
  • Start date Start date
T

Tcs

I have a table of customer history, out of which I need to pull the LATEST
records for that customer. This will be for a particular month, and will
contain a variable number of records for that month, and I will store the
results in a separate table.

In order to accomplish this, I figure that I need to start with last month (Jul)
and going back to preceding months, only adding the customer to MY table if that
customer isn't already there.

All this appears to be easy enough. But I need to extract the same data, for
the PRECEDING month, and then the next preceding month, etc., on back to January
1999.

QUESTION 1 - Is this 'separate query for each month' the way? Or is there an
easier way? If so, what is it?

Assuming I need to query my history one month at a time, is there some way that
I can automate this? I'm thinking of a small table that would contain the month
and year I want. My code would read thru this table, in the order I tell it
(reverse) so I don't have to create and maintain some 50+ queries. (Not to
mention adding another query each month.)

QUESTION 2 - Can I do this? Does this make sense? If I can, how would I?
(I've done VERY LITTLE such coding.) Would I need to have the code run a query
first to return a result set which would become the record set which I would
read/cycle thru? (So I wouldn't have to query the 400 for each month, I would
just query the result set.) Could you help me with some code to help get me
started?

Backend - AS/400 running DB2 v5r2m0.
Frontend - Access 2000 sp3 on XP Pro sp1

Thanks in advance, any help is appreciated.

Tom
 
I can think of two ways maybe:

1. if it's the exact same data then why not use a between and clause with
your query. This way you only get the months you need and you can store it
as a parameter in another table or in a box.
2. Write some code that uses the old COBOL control break method. Sort the
records by account and date. When you "see" the customer number change in
the do...while loop then start writting to the table until you've reached
the approrate number of records then move on to the next account number.

Hope this helps.
 
Back
Top