looping through a record set to get variables and using them to get data from multiple tables

[Abuba3000]

Hi All,

Trying to find a VBA solution to the following and think I need to use DAO but not sure how to.

I have a table that has a daily summary of transactions (sas.summary) including date, transaction reference and transaction type.
I also have a table for each transaction type (approx 250 tables with names like sas.x001) that has the transaction reference, status, comments .

What I want to do is loop through the summary recordset using the transaction reference (TRANS_REF) as the criteria and the transaction type (TRANS_TYPE) to determine which table it need to search in. I want to put all the data from the summary table (DATE, USER, TRANS_REF and TRANS_TYPE) and the found results (STATUS and COMMENTS) from the transaction tables, in to a table called tbl_master.

I'm guessing I would need to create a reference table with a column for the transaction reference and the table it relates to.

Any help you can provide will be appreciated as the only way I can think of doing it with my limited knowledge involves chaining about 500 sql queries together

 

[welshgasman]

I don't think your data is normalised, and that is why you are having such a headache?
The transactions should all be in one table with a column to identify transaction type.
Then you would just join the summary table with the transaction table.

I'd be looking at combining all the transaction type tables into one with that new column.
That would mean about 250 separate queries unless you write some code to loop around the table names and use a little VBA to run an append query 250 times.
Then link as mentioned and go from there.?

HTH

 

[Abuba3000]

Thanks for the response Welshgasman, you are correct that the data is not normalised. I want to avoid raising queries that combine the transaction type tables if possible as some of them have a few million entries as they are back end tables to the system we use. There are also occasions where some of the transaction types are not used for several days so I would not want to pull them in daily if they are not being used.

 

[welshgasman]

OK.
Try this approach then.
Sort the summary table by transaction type.
Read the first record and create an outer loop until end of file
Create an inner loop
Determine the transaction type and open that table.

Do your process
Read the next record. If transaction type is the same repeat inner loop. If not the same close the transaction table and start the inner loop again.

That is how I would do it, however I am a relative novice at Access, so someone might be able to offer a better way.

HTH

 

[Micron]

The way I read this, the summary tables (1 side) would need to have a field to identify the related transaction table (many side) OR the transaction tables would need a property to identify the join. If the trans tables had the info in a field, it would really slow things down as you investigate each table for the necessary information. Best to have this on the 1 side.

The sql to join the two entities would be broken up into segments, such as the SELECT portion, the JOIN portion and the CRITERIA (if applicable). You could loop through the summary field that identifies the required transaction table and concatenate the sql segments using a variable for that table name. It certainly would help if every transaction table had the same field names so that the sql construct isn't dependant on which transaction table is involved. If that's not possible but the common field between them still is, you could write the JOIN sql portion in a transaction table field and grab it from the applicable table. However, to use a variable multiple times in a SELECT statement would require a huge effort in concatenation, so I would use an alias name for each table, then it only becomes involved once. Having said this, putting sql portions in a table might be more expedient.

I'm not seeing an inner loop based on this (perhaps incorrect) scenario.

 

[welshgasman]

Micron,
My understanding is that the transaction ref is the link, but due to the transaction types being held in separate tables(250 of them?), the o/p needs to know what table to interrogate.?

 

[Micron]

I realize that. I'm saying something has to be common between the 1 side and the many so that the commonality identifies the needed table from the collection of 250 tables. If it can't be a field, it could be a property, such as the table name or custom property. If neither, then I have no idea.