How can I construct a variable column name?

[HRGED]

Access2007 VBA
I currently have the following code:

 If Cust!UTC17 = "1" Then
        U17 = Cod!Cod1
    Else
    If Cust!UTC17 = "2" Then
        U17 = Cod!Cod2
    Else 

etc. etc
Is there any way I can reduce it to something like

 U17 = "Cod!Cod" & Cust!UTC17 

Cust!UTC17 contains a number
Cod contains multiple columns named COD1, COD2, COD3 etc.
I want to use the number contained in Cust!UTC17 to decide which columns value to assign to U17
Any advice much appreciated as my searching has not revealed a solution.

[twinnyfo]

To "answer" your question, yes it should be possible.

Much of it depends on how you are trying to assign these values. It is also unclear how "Cod" plays into this? Is this another table? How is that Table related to your first table? How do you know which record in table Cod to use? OR--are you trying to assign a particular field in Table Cod as a record source for a control names U17?

So, before we can guide to any more definite solutions, we need a little more information.

[HRGED]

I have a transaction table and need to update each row with up to 10 values from another table (COD) containing up to 30 possible values (columns Cod1 to Cod30) . The 2 tables have a one to one relationship. The problem is that the selection of the 10 columns from the 30 availble varies from client to client. I have another table (Cust) which has one row per client with 10 columns,(UTC17 to UTC26) one for each of the columns in the transaction table that needs to be updated. Each of the 10 columns in the Cust table has a number corresponding to the source column in the COD table.
The flow is:
1. Read a transaction
2. Get the COD row with the same ID
3 Get the Cust row for the transaction
4. For each Cust!UTCnn get the Cod!Codnn
e.g. if Cust!UTC17 contains 5 the value in Cod!Cod5 is selected.
5. Update UTCnn in the transaction with the value from step 4

I hope this has made the process a bit clearer. I acknowledge that the table design leaves a bit to be desired but, except for the Cust table, they are out of my control.

[jforbes]

I think Twinnyfo is thinking of a slick way to do it things in one line, but a quick and dirty way to do this is:

Select Case Cust!UTC17 
    Case "2" 
        U17 = Cod!Cod1
    Case "1" 
        U17 = Cod!Cod2
End Select

http://msdn.microsoft.com/en-us/library/cy37t14y.aspx

[twinnyfo]

First, I am going to recommend you look through this thread: Database Normalization.

Can you help me understand your Cod Table a bit better? Does it only have 1 record, but 30 separate values? E.g., your table looks like this (just for example's sake):

Cod1   Cod2  Cod3    ...  Cod30
Apple  Pear  Orange       Blue

If this is the case, and if your Customer Table has 10 COD columns, I would restructure your tables such that you have the following:

Table COD:

Field    Description
CODKey   PK, Autonumber
CODDesc  Description of COD

This Table (for now) would have 30 records.

I would remove the 10 UTC columns form your Cust Table.

Create a new Table based on Cust and Cod:

Field   Description
CustID  FK to Cust Table
CODKey  FK to COD Table

This Table would have up to 10 records for each Customer. however, it gives you more flexibility, because if you ever have to add an 11th COD to a customer, you don't have to restructure your entire DB. Additionally, this is a more efficient design, in that when you have a Customer that has fewer than 10 CODs, there is no wasted space.

Later on, when you want to display the CODs belonging to each Customer, a simple join query will do the trick.

This is a standard DB structure that is tried and true and complies with the best principles of design.

However, if you want to continue with the old structure, we can work toward a solution. however, keep in mind that any such solution will be pregnant with future problems once any of your data changes.

[twinnyfo]

@jforbes,

I was initially thinking more along the lines of:

Me("COD" & Cust!UTC17)

But, as you can see above, I think there are more serious issues with the DB design.

[HRGED]

My introduction to the hallowed principles of 'the key, the whole key, and nothing but the key' was around 1980 and I certainly wouldn't design tables like this given a free hand.
The application is a data transform which takes table extracts from a 'real database' (I can't access it directly for security reasins), imports them and converts them to
an horrendous multi-record format text file of which this transaction table is but one of about 40 record types. It's not my design and I'm sure that the guys who originally designed this retired with the IBM 360.
I have constructed the file using Access without a drop of VBA just lots of queries but it's all hard coded and each new client needs a new database. I'm trying to build a single database which will process multiple clients. The original version of the COD file is how you advise but it contains 100s of Ks of records so the flattened version is much smaller and faster. The flat Cust table could be normalised at the expense of ease of maintenance as I haven't built any maintenance routines, customers are added directly into the table.
Thanks for your help.

[twinnyfo]

Well, let's see if I can use a Commodore 64 to find a solution....

I will still need just a bit of information to assist. I am still not sure I understand how COD relates to Cust. You say COD has 100K+ records?

If I understand you correctly, If you have Cust1234, their 10 UTC17-UTC26 could be (for example) 1, 3, 5, 7, 9, 13, 14, 21, 22, 28. And I understand that you want to reference from the COD Table the corresponding fields: COD1, COD3, COD5, COD7, COD9, COD13, COD14, COD21, COD22, COD28. I get that.

But, the challenge for my understanding is, if COD has 100K records, are we saying we want the 100K records associated with COD1, COD3, COD5, COD7, COD9, COD13, COD14, COD21, COD22, COD28? Or, just a specific record within that table corresponding to the Cust.

Again, this is very possible, I am just having problems grasping the data flow.

Perhaps several concrete examples in how it is used (no proprietary or personal information, please).

[HRGED]

I was big iron (well, medium sized) in the days of the Commadore 64.
What we are trying to do is to undo good database design.
In the original database each transaction row has up to 30 Cod rows with the same transaction ID.
Lets say each transaction represents the purchase of an item (the legendary widget).
The customer asks us to collect certain information with each purchase and each customer has different pieces of information they want collected to aid their analysis of the data. Lets say one customer asked us to record the weather at time of purchase and the purchasers hair colour and another customer wants us to collect the colour of their shoes and their star sign (these examples are of course complete fantasy. The original database version of the COD table has one row for each customer, purchase and piece of data. The flattened version has them in columns 1 and 2 for example.
The recipient of the output file has specified that for the first customer the weather must be in column UTC20 and the hair colour must be in UTC18 wheras for the second customer the shoe colour must be in UTC17 and the star sign must be in UTC26.
If we had just one customer there would be the same number of transaction rows as Cod rows and just one row in Cust. The one row in Cust defines which columns should be mapped to which columns in the transaction table.

[twinnyfo]

The tables you are using... Is this close to what they look like:?

COD
 
 Field  Description
Trans  Transaction ID
COD1   Your first COD Data
...
COD30  Your last COD data

Customer
 
 Field   Description
 Trans   Transaction ID (same ID as in COD?)
Fields  Other Fields
UTC17   This is number that refers to the COD?
...
UTC26   Last COD reference

Now, is the intent to move the data from COD1-COD30 into the UTC17-UTC26 Fields, or are you trying to create another flat file in which you have the Customer with the corresponding data from those specified COD numbers? Example:

Customer  UTC17    UTC18   UTC19   ...  UTC26
ABC123    Green    Fast    Orphan  ...  Nike
YYZ2112   Morning  Boring  Legos   ...  Snowflake

(I get the impression this data is kinda scattered and unique to each record?)

Is this data going to then be stored somewhere or is this exercise in futility just so you can send a report? Saving it might be easier, but will take up tons of space. A report is not going to be able to save anything.

Either way, I think you will have to use VBA to create the data, whether it is through the use of a home-made Function (which might be pretty easy) or modifying a recordset/Array. The Function method would be slow, but I think we could control it better.

Let me know if I am getting closer to understanding your data. I don't want to dive in the deep end, only to find out this is the kiddie-pool side....

[HRGED]

I've attached an Excel spreadsheet with example rows from my tables.
There are 3 transactions and 3 Cod rows. Transactions Field3 has the same value as COD Field2 so there is a one to one relationship.
The columns UTC17 to UTC26 in the Transactions rows were blank but I have populated them with the correct entries from COD.
Cust contains just one row, there is a one to many relationship with COD on CustCd.

Hopefully this will make it clearer.

Thanks

[twinnyfo]

To further clarify, Transactions, COD and Customers (as shown in the Spreadsheet) are existing tables in your DB?

Can we also assume that you are trying to convert all records into the Transactions table?

Here is an outline of the VBA code you will want to build:

Create a recordset based on the Customers Table. Include all fields.

You will cycle through each record in this recordset, one customer at a time.

For each record in the Customers recordset, build another Query, based on the COD table, with the Criteria:

WHERE CustCd = '" & RecordsetName1!CustCd & "'"

This will produce a list of all records in the COD table belonging to that Customer.

Cycle through these records one at a time. For each record, you will want to do this:

Using the values in UTC17-UTC26, use that as an index for the fields in the COD table. This is important!!!!! In your COD Table, what is the number of the first field that has the COD Values? As shown in your Spreadsheet, it looks like the first field (COD1) is the 4th field. When Access numbers fields, it starts counting at 0, so, your 4th field is actually referred to as RecordsetName2.Fields(3). I hope this makes sense. However, if we are trying to calculate the actual field from your example, UTC17 is looking for COD3, which would evaluate to RecordsetName2.Fields(6): 4 is the first COD, 5 is the 2nd, etc.

So, to copy the values to the Transactions table, you would cycle through UTC17-UTC26, and if there is a value, the corresponding Field from the second recordset will be in field (RecordsetName1.UTC17 + 3)

You can also automate the finding of these values. I am going to straw-man this out, as I am freehanding it.....

'Create First Recordset
'Create second recordset based on CustCd
For intCounter = 17 To 26
    strCODValue = SecondRecordset.Fields(FirstRecordset("UTC" & intCounter) + 3)
    'Assign this value to the Transactions Table
Next intCounter

This is very confusing--even for me as I am trying to work through it. So, you will want to go slow, and make sure that you are able to append all the data associated with the customer to the transactions table first. Then work through adding the COD values. Keep your breakpoints active and walk through the code step by step.

I am more than happy to work through the more complexified aspects of this code, but hopefully you have enough experience to at least get the ball rolling. We will gladly troubleshoot and provide additional advice.

[HRGED]

Sorry for the delay in replying. I didn't know columns could be referred to postionally instead of by column name. I'll give that a try and let you know.
Thanks for all your time, knowledge, and, especially, patience.

[twinnyfo]

Hey, this stuff can make you go gray or bald--or both. I'm just going gray. I've learned a lot over the past few years of little stuff that some of the geniuses here know about. They have been gracious enough to share, so I am glad to help others when I can.