How to read a table record by record and process and update another table

  • Thread starter Karen Middleton
  • Start date
K

Karen Middleton

Jeff

Sorry, there is a problem is posting back to the newsgroup I have to
post as a new messsage everytime I reply back.

Albert first of all many thanks for your help I admit your posting
almost gave me what I was looking for. Very good update on using .net
languages in Yukon so perhaps Access is ahead of Yukon ):-

Jeff I am answering your question I have a staging table to a fact
table in the database the raw data is loaded into the staging table
which is almost not exactly similar in structure to the fact table.
Now from the staging table I want to read each record by record and
load the fact table. While this load happens I want to check for
various things business rules like if the transaction is in a
paticular company code to do different things and then do unit of
measure conversion looking up into a UOM conversion table and then do
currency conversion at different rates by looking up another tables.

I cannot unfortunately do all this in a query. I am little bit amazed
and surprised that Access queries cannot even accomodate multiple SQL
statements for action queries in one query and it is so painful to
write multiple queries for each of these tasks. I could have done all
this seamlessly in SQL with T-SQL despite all its limitations this was
I was after similar to opening a cursor against a SQL query and keep
updating a target table based on various complex business rules.

Albert - My only question to you is you have provided a nice example
of a read only cursor in your above example we can also update cursors
in T-SQL can you kindly give me a variant of the above whereby I can
update the authors table itself from the recordset while looping is it
possible ?

Many thanks
Karen
 
J

Jeff Boyce

Thanks for the info, Karen. I don't know if this will help, but a possible
approach might be to "chain" together individual queries to accomplish the
tasks you are facing. Rather than try to complete all validation and
loading in a single query, a sequence of queries, each doing its part,
building on the previous query(s) may still get you there...

Best of luck

Jeff Boyce
<Access MVP>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top