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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)