Example:

AAA
123 John Los Angeles
234 Mary San Jose
BBB
345 David San Franscisco
346 Lee Berkely
333 Ann New York
CCC
555 Linda Houston

How to write a query to become:

AAA 123 John Los Angeles
AAA 234 Mary San Jose
AAA 345 David San Franscisco
BBB 346 Lee Berkely
BBB 333 Ann New York
CCC 555 Linda Houston

Thanks

In article <7a3728a7-0944-4c3c-a134-d73de9f04a49n@googlegroups.com>, miloann
wrote...
>
> Example:
>
> AAA
> 123 John Los Angeles
> 234 Mary San Jose
> BBB
> 345 David San Franscisco
> 346 Lee Berkely
> 333 Ann New York
> CCC
> 555 Linda Houston
>
> How to write a query to become:
>
> AAA 123 John Los Angeles
> AAA 234 Mary San Jose
> AAA 345 David San Franscisco
> BBB 346 Lee Berkely
> BBB 333 Ann New York
> CCC 555 Linda Houston
>
> Thanks

This isn't "normalised" data. I can't conceive a query which would do this,
though a real SQL expert possibly might. But I think you could write a macro
to do this in VBA.

Essentially,

- Where there is only one field in a record, store it in a variable.
- Otherwise, read each record, prepend the current contents of the variable,
and write the updated record out.

I'm very rusty with all this these days, but I rather think that "pure" SQL
doesn't recognise "state" - where actions need to be different depending on
what has happened before. I found VBA straightforward enough - though I do
have a long programming background. The library I used in my own coding is DAO,
which provides VBA with facilities to manipulate Access data structures. I
suspect it's rather out-of-date now, though it all still works. I found a
YouTube introduction which seems likely to be helpful, though I haven't had
time to watch more than a minute or so of it: https://www.youtube.com/watch?v=
5SVOxZCCPWw

Sometimes, of course, it's quicker and easier to manipulate modest amounts of
data by hand - open up Excel, put some music on, and (depending on how much
data there is) you could be done by the end of the CD. Unless of course, you
have to process data being sent to you regularly - in which case I'd be leaning
on the source to "normalise" their mucky data.

One other possibility is to process it using "regular expressions" (massively
powerful). The Unix "SED" stream editor utility could do this, as could "AWK",
and RegEx is built into the editor in my copy of Dreamweaver for example. Then
you'd configure the editor to recognise a sequence of non-whitespace characters
terminated by a newline, and store it, prepending that to any lines which
aren't so matched. I haven't fooled around with the Linx Subsystem for
Windows, but that should have SED. Nor have I fooled around with PowerShell
yet - I wonder if that has Regular Expressions? Ah - yes:
https://learn.microsoft.com/en-
us/powershell/module/microsoft.powershell.core/about/about_regular_expressions?
view=powershell-7.3

I _really_ must learn PowerShell...

HTH

--

Phil, London