Transpose Row- Orientated Data after Import

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know transposing data is a commonly-asked question in the forums, but
although I have read a lot of the answers I have not found exactly the
solution I need.

I am importing data from 2-column HTML Table , in the form:

Item Name 1: Value 1
Item Name 2: Value 2
 
Colin,

Leaving aside for the moment the question of *why* you want to do this
(i.e. take normailised data and transfer it into an Access table (where
Access thrives on normalisation) in a denormalised state!)... Can i
just make sure I understand that you want the table to end up with only
one record, with the field name corresponding with the value in one of
the original columns? Well, you asked the question about "VBA loops",
and yes, this would be an option. But the focus of this newsgroup is
macros, which have nothing to do with VBA, and you could also do it with
macros using a series of OpenQuery actions to run a series of Update
queries. The other approach is to move the data into Excel, where
transposing is much more simply achieved, and then import to your Access
table after that. But the 'why' is still plaguing me. :-)
 
Hi Steve,
Your "why" question is entirely valid. I'll try to explain the reasoning.

Why 1
------
Some existing data formats are inherently column-organised. Imagine
importing the headers from a large mailbox. Each mail header looks like:-

Date: xxxxxxxxxx
Subject: xxxxxxxxxxx
To: xxxxxxxxxx
From: xxxxxxx

but your required "MyMails" table would look like:-

ID Date Subject To
From Body......
-- ------- ---------- --------
-------- ----------
01 01/01/2006 Access Question xxxxxxx xxxxxxxx
........
02 01/01/2006 Another Question xxxxxxx xxxxxxxx
........

Why 2
------
When designing a form-like data collection method in either a spreadsheet
or an HTML mail template(yes I know ideally this would BE an online form, but
sometimes this is not an option) there is often some combination of
information that applies to the whole sheet, then repeated tabular
information. I.e:-

Name: xxxxxxx
Submission Date: xxxxxxxx
Reason for Claim: xxxxxxxxxx

Claim Items

No Description Date
--- ----------- -----
01 Description 1 01/01/2006
02 Description 2 01/02/2006

The header (and maybe footer) needs to be pulled out and transposed as
above, then the body imported into a separate table in a one-many
relationship. I know about transposing in Excel, but I'd like to minimise the
number of external conversion trips here, HTML->Excel->Access looks
unnecessarily complicated.

If this is completely invalid (i.e. there's a far better way of doing it)
please let me know, but it seems to be a reasonable approach.

BTW- I had not grasped the clear distinction between Macros and VBA. The
latest Access docs seem to downplay Macros and recommend VBA for new work,
the action builder offers both, and the DoCmd object blurs the boundaries.
Also I didn't see an "Access.VBA" forum, although I need to re-check this. I
read "Access.Macros" as meaning "Access.Scripting_and _Coding".


Regards: Colin
 
Colin,

Thanks for the further explanation.

In your original question, you used the term "grind through all the data
mechanically". Probably this is an overstatement of the case. Using a
VBA procedure to loop through the records and update the fields in the
"transposed" table is definitely the approach I would take here, and I
don't think it would grind too much.

To repeat my earlier question, does the second table end up with only
one record? Or if more than one, it is not clear from your example how
you would know which record the original data goes to.
 
Hmm, HTML whitespace-compression mucked up my carefully laid out text
examples...

Basically, taking the example of a set of email-type files to import, each one
consisting of a column-orientated header plus a tabular body, I would want
to import these as:

* Each mail header becomes a new row in a "mails" table, with fields of
"From", "to", "Subject" etc. The incoming data would need to transposed, and
the key fields would be (say) the date/time of receipt of the email (i'm sure
you'd have an autonumber primary key).

* Each row in the body of the mails becomes a row ina "mailbody" table. This
table links to the corresponding header in the "mails" table in a one/many
relationship.

Does this make sense?

Regards: Colin
 
Colin,

Thanks. However, I am quite used to seeing newsreader-wordwrapped
example. :-) I think I have already understood perfectly. I just was
trying to alert you to the need to identify which 'From' goes with which
'To', etc, if the data being processed includes more than one record.

Have you had a go at the code yet?
 
Back
Top