show fields horizontally(different way of showing data) (1 Viewer)

[Dylan Snyder]

Unique Problem here and I am all out of answers so any help would be appreciated.
I have to create an extract to an Enterprise System that relates date differently than what I am used to in Access.
Basically, I have to extract a Parent and Child (one to Many) Table out of Access where(and here is the catch) each reacord of the child is one record of the results set. In other words, instead of stacking the records of the children on top of each other like in an excel file, I basically need to concatenate the children horizontally with only one record per record in the Parent Table.

Below is a simple diagram:

PO Header Table PO Detail Table
PO # PO # SKU
1007343 1007343 102
1007343 103

Results:
1007343 102 103...

or 1007343,102,103...
The format doesn't really matter. what is important is showing this relationship. Any help that could point me in the right direction would be great.

 

[ezfriend]

I am not sure if I understand your post correctly, but here is a suggestion.

Get a recordset of the parent record
For each parent record, get another set of records (child) and just loop through all of them

dim rsParent as recordset
dim rsChild as recordset

set rsParent = currentdb.openrecordset("select * from tblParent")

do while rsParent.eof =false

   set rsChild = currentdb.openrecordset("select * from tblChild where ParentID = " & rsParent.ID
   dim sChildID as String

    do while rsChild.eof =false
       if sChildID <>"" then
           sChildID = sChildID & ", "
       end if
       sChildID = sChildID & rsChild.ID
       rsChild.MoveNext
    loop
    rsChild.Close
    set rsChild = nothing

    debug.print rsParent.ID & ", " & sChildID

   rsParent.MoveNext

Loop

rsParent.Close
set rsParent = nothing

 

[Dylan Snyder]

Thanks for your help,
I can't get the code to work on my end, but this certainly points me in the right direction. I have been avoiding this more advanced VBA but it looks like I'm going to have to learn it. Is there a specific name for this or is there a web tutorial that you know of that I can read to figure out what is going on?

 

[gemma-the-husky]

its just code

its a non-normalised way of showing data - eg, what happens if you have so many values for a given row, that they dont fit in your text box

but to get the information in a string, you have to take the query that would normally show multiple rows of data, and instead process that query with code, extracting the data you want from each row, and storing it in a string. (or some other structure) , You normally do this by iterating a recordset

then use the final string however you need to use it

thats is what EZ's code was doing - iterating a recordset.

 

[Dylan Snyder]

Iterating a recordset. Got It. Thanks for all of your help. I'll study up on it over the weekend and hopefully I'll have a good new tool to use.

 

[gemma-the-husky]

the pseudocode is

open recordset
while not end of recordset
    process a record
    goto next record
end while

the actual code will be something like

sub doit
dim rst as recordset
dim s as string
s = ""
set rst = currentdb.openrecordset("queryname")
while not rst.eof
    s = s & rst!selectedfield & " " 
   rst.movenext
wend

msgbox("Final string: " & s)
end sub

 

[Dylan Snyder]

thanks ez-friend and gemma- I didn't realize that the code you gave me already swept through the tables and created a denormalized recordset. This is great. Thanks again for all of your help.