Info from multiple record in one query field?

[mandanarchi]

Quick Example to explain requirements:

Default query behaviour

Order              Item
12345             <tr><td>Manuf0 Model0 Type0</td><td>Description0</td></tr>
12345             <tr><td>Manuf1 Model1 Type1</td><td>Description1</td></tr>
12346             <tr><td>Manuf0 Model0 Type0</td><td>Description0</td></tr>

Query behavior I want (but doubt is possible)

Order              Item
12345             <tr><td>Manuf0 Model0 Type0</td><td>Description0</td></tr><tr><td>Manuf1 Model1 Type1</td><td>Description1</td></tr>
12346             <tr><td>Manuf0 Model0 Type0</td><td>Description0</td>

The table tags are for an external program (and to make things easier for me) and are created on the fly by the query.

There are 3 tables at work there;
Invoice - OrderNo(pk)
Order - OrderNo(fk) & ProdID(fk)
Product - ProdID(pk) & manuf & model & type & description

As a start, is this even possible with queries? It has to be done automatically, no user interaction necessary.

Thanks
Mandi

[Stewart Ross]

Hi mandi. You would need to use some form of bespoke coded solution for this, as queries cannot selectively pivot columns the way you describe. The closest built-in facility is the crosstab query, but these would not achieve the effect you require (as crosstabs in Access pivot single column values across a set of headings drawn from all heading values in the whole dataset).

As you are dealing with real-world order data I am assuming that the many-side product lines for any one order number are truly multiple and potentially unlimited (even if there is some form of practical limitation on the size of an order), not just the simple example of two entries for one reference you show.

On the bright side if you develop a bespoke code solution it can certainly be run on an entirely automated basis.

-Stewart

[FishVal]

Producing a List from Multiple Records.

[mandanarchi]

@FishVal
Exactly what I want to do that, thanks!
But I'm getting the reserved error. I think it's possibly because not all of our orders have multiple items on them. I'm going to see if I can include an IIf to fix that.

Thanks FishVal

[mandanarchi]

Aha!
It was nothing to do with what I thought. I changed the 'Max' to 'Last' and it works brilliantly.

Thanks again =)

[NeoPa]

Hi Mandi.

Last() should work equally well, but I can't imagine a scenario where Max() wouldn't work too :S

Any string value, compared with another string value, which is the same as the first except in that it has extra characters at the end, must surely be less than the second value. Hence the Last() one must also be the Max().