Reorganize MS Access Table

[Seth]

I have a table of the structure below:

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

You may see that a delivery can have multiple products. I will like to
reorganize the 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 will
greatly appreciate if someone can show me a way (query, etc) to
organize the table into this form. Please not each delivey may have a
different number of products.

Thanks.

[Lyle Fairfield]

Don't

With the design you have you may associate any number of Products with
any Delivery independently of (unneeded) fields Product1, Product2 etc.
With your proposed design you will be limited by the number of these
fields that you decide to create; worse still you will not be able to
summarize and organize your data.

What you are suggesting is not consistent with good db design,
normalization, efficiency, effectiveness or even Santa Claus.

You have good design. Be Happy! Stick with it.

[Tim Marshall]

Seth wrote:

You may see that a delivery can have multiple products. I will like to
reorganize the 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 will
greatly appreciate if someone can show me a way (query, etc) to
organize the table into this form. Please not each delivey may have a
different number of products.

Seth, you do NOT ever want to make your table this way. If you do,
forget Access and any other database application forever and use Excel
for the rest of your life.

Now, on the other hand, if you wish to display data in a format you
mention above, you will need to look into making a crosstab query. Look
up help on this topic and see if that will satisfy your needs.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Seth]

Thank you Tim for the quick response. Yes, I won't change the original
table. What I need is to create a query that will display the data in
the second format. I will check crosstab query if that helps. Thanks
again.

[Seth]

Thanks Lyle,
Thank you for the quick response. I won't change the original table.
What I meant was to create a query that will display the data in the
second format. I will appreciate any suggestion on that. Thanks again.

[Tim Marshall]

Seth wrote:

I will check crosstab query if that helps. Thanks
again.

Write back if you need any clarification on cross-tabs. I don't use
them very often, but many others here on cdma will be able to help you.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Seth]

I have tried the crosstab; 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 associatedwith the appropriate delivery).
Thanks for your help guys.

[Bob Quintal]

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

I have tried the crosstab; 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 associatedwith the
appropriate delivery). Thanks for your help guys.

Find the Fconcatfld() function at http://www.mvps.org/access/
http://www.mvps.org/access/modules/mdl0008.htm

may do exactly what you want, or at least give pointers on how to
roll your own function.

--
Bob Quintal

PA is y I've altered my email address.