Dynaset within a Dynaset 1

[grndpa]

Access 2003 is new to me. I've dabbled in mySQL and PHP5.

What I'm trying to bend my mind around is how to embed a query/SQL while walking my dynaset.

The task is to take data from a variety of sources and map them into another format to be rendered into a comma-delimited-file. In addition, a number of "columns" in each "row" require translation from the source value (for example reason-code in the source is "TRF" corresponds to "XFER", in the .csv, "abc" to "def", etc.)

In his 27 Sep 06 post, gol4 mentioned "bookmark" but didn't elaborate.

What can you suggest?

One final note ... due to the number of Excel source tables, the SQL Select and from clauses are going to be very messy -- so I'd like to avoid a sub-query if there is an option.

I think I can muddle my way through everything else, but I'm absolutely stumped on what I suspect is a very simple embedded inquiry.

Regards

Grandpa Brian

 

[Hmadyson]

What I think that you are asking is how to put together a query that uses lookups to translate different values? Please let me know if this is not the case.

How I would do it, is to create a table with all of the lookup translations. For instance my table called tblLookup will have columns FieldName, OldValue, and NewValue.

Your data may look like
FieldName OldValue NewValue
Status TRF XFER

Then I would create individual queries for each field. So my query for Status would look like:

Select OldValue, NewValue from tblLookup where FieldName='Status'

I would name these qlkp[FieldName] so the one above would be called qlkpStatus.

Then I would join these queries into my main query. I would put left joins between the main data and the query matching on OldValue, so that if there is no data, you row does not disappear. Then I would show the NewValue rather than the OldValue

Here is an example, assume table basic has a field called Status

Select a.field1, a.field2, b.newvalue as status from basic as a left join qlkpStatus as b on a.status=b.oldvalue;

Hopefully I did not throw you off with my use of aliases on tables, but I find that helps me type less.

Please let me know if you need more assistance.

 

[grndpa]

I think I follow most of what you recommend, Hmadyson. Thank you. I'm a little afraid of, if you'll excuse my mainframe terminology, "currency" on my main table row while I got out multiple times to different tables.

I'm also mildly concerned about performance. Under this scenario, I would have anywhere from 10 to fifteen sql calls per each row of my main table.

I like your idea of "FieldName OldValue" NewValue". What do you think of setting up object arrays, one for each field, and populating them from the table you describe before "walking" the main table? Then doing a simple array function, rather than a table access, for the translation.

 

[Hmadyson]

I think that updating data a field at a time, is a lot faster than updating data a record at a time. Maybe that is not what you are saying that you are doing, but that is what is sounds like to me. Good luck with your project.

Please let me know if I can provide more assistance.