HELP!

[Gary Dolliver]

Hi all,
I am hoping for some help on this one, as I am baffled and in a crunch. I
tried posting in the queries section, but have had little luck and have had
great luck in here and think this is more of a coding question as well.
I have inherited some funk data that has come to me in a strange format. It
is
a single table full of multiple orders, however, each order takes up at least
3 or more records in the table. There are no distinguishing or identifying
fields to link these together, however, in field 1, there are only three
fields that break apart the orders, /SOHDR (order header info), /SOLI (line
item info, sometimes there will be more than one), and /SOSUM (order summary
line). There is nothing to link these together as an order, I can only go
off of the order that they appear in the table and use the /SOHDR as the
opening line and /SOSUM as the closing line.
What I am wanting to do is to take a field from the /SOHDR field and insert
it into the next /SOSUM record (for the corresponding order) and then
perform this for the next /SOHDR and /SOSUM records. I was thinking I could
create some type of function, or would an update query work?
Please help, thanks!
-gary

 

[Jeff Boyce]

Gary

Queries work off of set data. The data you describe places DIFFERENT data
elements in the same columns and spreads "records" out over multiple rows.
This is not a relational database design, and you won't be able to use the
relational features/functions Access offers.

One approach might be to create a procedure that steps through each "row" of
input data and decides what output to write. Your goal is to end up with
well-normalized data, not a copy of the multi-row-per-record input you now
have.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP