Update a 2nd Table

[Seth]

I have one table of the form:

Delivery Product
101 A
101 B
101 C
102 A
102 E
103 C
104 A
104 E
104 C

I need to update a 2nd table with the data from the first table to look
like this:

Delivery Product1 Product2 Product3
101 A B C
102 A E -
103 C - -
104 A E C

So that there is only one line of record for each delivery.

I have tried crosstab query; it doesn't give me exactly what I want -
it lists all the products for column headings (about 760) in this case.
So I will have more than 780 column headings. I need a way to update a
new table so that it looks like the 2nd table shown above: the column
headings are already there, so the query grabs the the products for a
delivery and puts them under a column heading (doesn't matter under
which field as far as it is associated with the appropriate delivery).

Thanks for your help guys.

[Lyle Fairfield]

Seth
You can do this with recordset scanning and manipulation, of course
(you can do almost anything with recordset scanning and manipulation).
Before writing such code one would have to know the exact structure of
the second table. How many product fields are you going to allow and,
thus, create? (This question, which may be hard to answer, is why we
generally don't do what you are trying to do; if we say four product
fields then what happens when a delivery has been one of five, or
twenty-three products? [And when there is only one product many of us
are not enthusiastic about those empty fields].) As you point out, the
crosstab query gives you 780 columns which is not what you want. (Maybe
someone who is more expert at crosstabs than I will suggest a simple
solution for that!).
If I had to do this, I suppose I would write code to create the
temporary table with enough products fields to handle the maximum count
of products to delivery. But I am a compulsive code jockey; it's
unlikely that you are and therefore if I misjudged or misunderstood the
tiniest characteristic of your data, my code would probably not work;
then what?
I ask, again, for you to reconsider your needs and ask yourself is a
report (or form) showing something like
Delivery Product
--------------------------
--------------------------
101
---------------------------
A
B
C

--------------------------
--------------------------
102
---------------------------
A
E

woudn't meet your needs.

If not then if you post the exact structure of your 2nd table, I
(someone else will beat me to it, I hope) will suggest code to
accomplish what you want. You should also decide if the 2nd table is to
be a temporary or permanent table, and you should specify (you may
already have) the version of Access you are using and anything else we
should know, for example that you are using MS-SQL and not JET.

[Seth]

Thanks again Lyle,

I have decided to have the 2nd table have 15 product fields (about the
maximum per delivery) possible. And you are right, some of the product
fields may be empty but that is fine. I am using Access 2003. Thanks
for your help.

[paii, Ron]

"Seth" <se***********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I have one table of the form:

Delivery Product
101 A
101 B
101 C
102 A
102 E
103 C
104 A
104 E
104 C

I need to update a 2nd table with the data from the first table to look
like this:

Delivery Product1 Product2 Product3
101 A B C
102 A E -
103 C - -
104 A E C

So that there is only one line of record for each delivery.

I have tried crosstab query; it doesn't give me exactly what I want -
it lists all the products for column headings (about 760) in this case.
So I will have more than 780 column headings. I need a way to update a
new table so that it looks like the 2nd table shown above: the column
headings are already there, so the query grabs the the products for a
delivery and puts them under a column heading (doesn't matter under
which field as far as it is associated with the appropriate delivery).

Thanks for your help guys.

Add a position column to your order table.

Delivery Product Position
101 A 1
101 B 2
101 C 3
102 A 1
102 E 2
103 C 1
104 A 1
104 E 2
104 C 3

You would set the position during data entry.
As a plus you can limit products per order using max position.

The cross tab would look like this

TRANSFORM First(Order.Product) AS [The Value]
SELECT Order.Delivery
FROM Order
GROUP BY Order.Delivery
PIVOT Order.position In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);

Order 1 2 3 4 ... 16
101 A B C
102 A E
103 C
104 A C E

[Lyle Fairfield]

Well, this is hastily cobbled together code. As such it may not be
efficient and it may have errors. Obviously, it does more than you need
(such as delete and create the tables). But on my machine it creates
the Table that you describe.
Of course, as you specify that you are using Access 2003, it uses ADO.
No doubt, the champions of DAO, (almost everyone), will create and
post entirely different modules (using DAO only) that will do the job
much more quickly and in many fewer lines of code (even allowing for
the removing of table deletion, table creation and the populating of
the original table). Then you will really have SOMETHING!
My advice is to try running the code as it is (assuming you do not have
tables named FirstTable or SecondTable), and, if it works, examine
SecondTable to see if it is what you want. The code is probably
self-explanatory but you could ask any question you want about it.

Public Sub Seth()
Dim r(2) As ADODB.Recordset
Dim a As Variant
Dim z As Long
a = Array(0, 1)
For z = 0 To UBound(r)
Set r(z) = New ADODB.Recordset
r(z).ActiveConnection = CurrentProject.AccessConnection
Next z
With CurrentProject.AccessConnection
On Error Resume Next
.Execute "DROP Table FirstTable"
.Execute "DROP Table SecondTable"
On Error GoTo 0
.Execute "CREATE TABLE FirstTable (Delivery INTEGER, Product
TEXT (10))"
.Execute "CREATE TABLE SecondTable (Delivery INTEGER)"
For z = 1 To 15
.Execute "ALTER TABLE SecondTable ADD COLUMN Product" &
CStr(z) & " TEXT (10)"
Next z
End With
With r(0)
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "SELECT * FROM SecondTable WHERE False"
End With

With r(1)
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "SELECT * FROM FirstTable WHERE False"
.AddNew a, Array(101, "A")
.AddNew a, Array(101, "B")
.AddNew a, Array(101, "C")
.AddNew a, Array(102, "A")
.AddNew a, Array(102, "E")
.AddNew a, Array(103, "C")
.AddNew a, Array(104, "A")
.AddNew a, Array(104, "E")
.AddNew a, Array(104, "C")
.UpdateBatch
.Close
.Open "SELECT DISTINCT Delivery FROM FirstTable"
While Not .EOF
r(0).AddNew Array(0), .Collect(0)
With r(2)
If .State = adStateOpen Then .Close
.Open "SELECT Product FROM FirstTable WHERE Delivery =
" & r(1).Collect(0)
While Not .EOF
r(0).Update .AbsolutePosition, .Collect(0)
.MoveNext
Wend
End With
.MoveNext
Wend
End With
r(0).UpdateBatch
End Sub

[Seth]

Thanks so much Lyle for your help. I got the number of products reduced
to less than 10 so I worked arround it (at least for now) with the
crosstab query. I haven't tried your code yet - I will let you know the
results when I try it. Thanks again for your help.