Translating table to format for export to another database

[BSman]

Hi. I'm trying to transfer data from one really badly designed database into a new database, but would like to use query/queries rather than some involved code with recordset(s), etc. The basic problem is that the old database has a main data table with 234 columns (!). I've created queries for about 7 types of subsets within that set of columns. Each query includes up to about 10 individual columns that are specific items. The items are dollar amounts for up to 10 different types of limits for the specific type of insurance, where each column name identifies the specific limit.

I want to import that into a table identifying specifics of limits for each policy as follows:

1. ReferenceID (refers to the policy row in the policies table)

2. LimitID (refers to the specific type of limit)

3. LimitAmount (the value that is in the specific column in the old database)

I have built a cross-reference table that identifies the name of each old database column and the value of LimitID in the new database, but can't figure out if there is a way to relate the column name (not value) in the old table/query and the cross-reference table to possibly use a cross tab query to get the values in the layout I need to add to the Limits table in the new database.

Anyone have any ideas?

 

[formerTexan]

Hi BSMan,

Any table with 234 columns certainly deserves a design review.

 

[BNPMike]

It is not clear to me what your problems is. It is very simple to move data from one table to another using queries.

What I suspect you are trying to do is write a generic query which you can then run for different sets of columns.
Access/SQL is very poor at this - you can't create variable names. People expect you to write VBA.
Personally I find it quicker just to cut and paste SQL from QBE and manually edit each query. It's not elegant but it's quick and safe.

 

[BSman]

I was trying to create a single query that would help me avoid writing a bunch of queries to translate this mess into the database I designed to take its place, but I've ended up just writing individual queries. While I didn't think there was an "elegant" solution, I thought it would be worth asking. I do realize that another approach would be to create a recordset and process each record, but individual queries turned out to be easier.

Actually, such a badly designed database does NOT deserve a design review. It deserves (and got) a completely new, properly designed database without reference to the old. The old database gets my award for the most poorly designed database I've ever seen, and I've been designing and developing database applications for over 20 years.

By the way, it is possible to create variable references to controls on forms and reports. The trick is that the controls should have some common name that ends in increasing numbers. An example would be txtMyData01, txtMyData02, txtMyData03, and so on. You can refer to each in a loop, for example, by concatenating the number (1, 2, 3) that is translated to text to the beginning part of each control's name (txtMyData).

 

[PHV]

Something like this ?
i = 2
MsgBox Me(txtMyData03 & Format(i, "00")).Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BSman]

Yep, except it would be
MsgBox me(txtMyData & Format(i,"00")).value

It ends up as
me.txtMyData02
when i=2

 

[PHV]

Oops, sorry for the typo:
MsgBox Me("txtMyData" & Format(i, "00")).Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244