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. :-)

--
Steve Schapel, Microsoft Access MVP


colin_e wrote:
> 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
> .
> .
>
> After import I need to transpose this and add it to a table in the form:
>
> Key Item Name 1 Item Name 2...
> --- ----------- -- -------------
> Key 1 Value 1 value 2
>
>
> I have seen reference to using crosstab queries, but Access refuses to
> define and run a crosstab query with only two source columns, and anyway
> crosstabs require a numerical column to be able to aggregate as the value of
> the crosstab.
>
> Do I have no choice but to write VBA loops to grind through all the data
> mechanically, or is there a neat way of achieving this?
>
> Regards: Colin

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


"Steve Schapel" wrote:

> 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. :-)
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> colin_e wrote:
> > 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
> > .
> > .
> >
> > After import I need to transpose this and add it to a table in the form:
> >
> > Key Item Name 1 Item Name 2...
> > --- ----------- -- -------------
> > Key 1 Value 1 value 2
> >
> >
> > I have seen reference to using crosstab queries, but Access refuses to
> > define and run a crosstab query with only two source columns, and anyway
> > crosstabs require a numerical column to be able to aggregate as the value of
> > the crosstab.
> >
> > Do I have no choice but to write VBA loops to grind through all the data
> > mechanically, or is there a neat way of achieving this?
> >
> > 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.

--
Steve Schapel, Microsoft Access MVP


colin_e wrote:
> 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".

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

"Steve Schapel" wrote:

>
> 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.

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?

--
Steve Schapel, Microsoft Access MVP


colin_e wrote:
> 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?