I have an order details file with the following layout (simplified):

orders_products_id orders_id model name quantity
1 6805 B12 Bananas 1
2 6805 A12 Apples 2
3 6806 C1 Cucumber 4
4 6807 B12 Bananas 4
5 6807 D06 Oranges 1
6 6807 G04 Pineapple 1

This file I would like to have converted as shown below (one line per
orders_id). There will never be more than three product lines per order.

orders_id model_1 name_1 quantity_1 model_2 name_2 quantity_2 model_3
name_3 quantity_3
6805 B12 Bananas 1 A12 Apples 2
6806 C1 Cucumber 4
6807 B12 Bananas 4 D06 Oranges 1 G04 Pineapple 1


Can this be done in Access??
Thanks for any help.

Regards/John

John Jessen wrote:
I answered a problem request somewhat similar to this in:

http://groups-beta.google.com/group/...037792c8c20bcc

Post back if that doesn't help.

James A. Fortune

The global hook example in the previous chapter demonstrates the
process of DLL injection. To recap, whan a global hook is set, the
system will inject (that is, map or load) the DLL containing the hook
procedure into the address space of any process that receives a hooked
message, thus enabling the hooked thread to call the hook procedure.

This has some interesting implications beyond its intended use. In
particular, there is nothing that requires the injected DLL to contain
only hook procedures--we can put any code that we want into the DLL.
Thus, we can force any process in the system to run code of our own
choosing!
Win32 API Programming with Visual Basic
Steven Roman
O'Reilly 2000

You don't need a crosstab: the following simple select query does it:

SELECT Orders.orders_products_id, [orders_id] & " " & [model_name] & " " &
[quantity] AS LineTotal FROM Orders;

--

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net


"John Jessen" <John_jessen2003 (AT) yahoo (DOT) com> wrote

Thanks Tony. I am sorry about the bad presentation of example data , but it
is difficult to show here. The example is a little more complicated, so
please see the below where I have shown the fields underneath each others:

Table: Orderdetails
Field1: orders_products_id
Field2: orders_id
Field3: model
Field4: name
Field5: quantity

From this I would create an output with one line per orders_id, showing
model, name and quantity as model_1, name_1 and quatity_1 for the first
occurence, and then model_2, name_2 and quantity_2 for the second occurence
(if existing) and finally model_3, name_3 and quantity_3 for the third
occurence (if existing).

I don't know if it can be done in MS-Access aty all, or some kind of
programming language with a loop is required.

Thanks for your help.

John