MER - ACROSS

[Guest]

I have a 2 field table: ID and item
this table is a "child" table. . . therefore the ID field can have the same
value multiple times. Additionally, there is a limit of 4 itmes per customer.


ID item

3401 82320
3401 79321
3401 86732

3402 52350
3402 49325
3402 76512
3402 91517

I want to have the result of a query list each ID uniquely once, and all the
items associated with it in that same output record:


ID item_1 item_2 item_3 item_4
3401 82320 79321 86732
3402 52350 49325 76512 91517

There used to be a 4GL named FOCUS. In Focus, you could write:
Table File MAINTABLE
List item across
by ID
end

and it would give me what I am looking for.
How do I do this in access?

 

[Douglas J. Steele]

Try a Crosstab query. Go to create a new query, and select the Crosstab
Query Wizard when prompted. It should walk you through the steps.

 

[Guest]

I found the cross tabs (yuo have to use the menu) but I could only get
counts of the across field. I could not get simple "printing" of the field
values as in my example.

Please share coding examples

The wizard is not sufficient

 

[Douglas J. Steele]

Sorry, I see what you mean. It can be done (by basing the crosstab query on
another query that numbers the elements so that you've got

ID label item

3401 item1 82320
3401 item2 79321
3401 item3 86732

3402 item1 52350
3402 item2 49325
3402 item3 76512
3402 item3 91517

and then putting label across the top, and Max (or Min) of the value as the
value. However, that's not a simple approach.

If you've got a limited number of items per ID (say no more than 4), you can
do multiple left joins between the table and itself and put conditions to
ensure that no value appears more than once, but that's a royal pain to set
up.

Bottom line is that there is no equivalent command to what's in FOCUS.

Why do you need this? (It is an unusual requirement) Perhaps we can suggest
an alternative.

 

[Douglas J. Steele]

Okay, so Michel proved me wrong. It is relatively simple to create the query
on which to base the crosstab. <g>