By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Info from multiple record in one query field?

P: 90
Quick Example to explain requirements:

Default query behaviour
Expand|Select|Wrap|Line Numbers
  1. Order              Item
  2. 12345             <tr><td>Manuf0 Model0 Type0</td><td>Description0</td></tr>
  3. 12345             <tr><td>Manuf1 Model1 Type1</td><td>Description1</td></tr>
  4. 12346             <tr><td>Manuf0 Model0 Type0</td><td>Description0</td></tr>
Query behavior I want (but doubt is possible)
Expand|Select|Wrap|Line Numbers
  1. Order              Item
  2. 12345             <tr><td>Manuf0 Model0 Type0</td><td>Description0</td></tr><tr><td>Manuf1 Model1 Type1</td><td>Description1</td></tr>
  3. 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.

Jan 20 '09 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 2.5K+
P: 2,545
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.

Jan 20 '09 #2

Expert 2.5K+
P: 2,653

P: 90
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
Jan 21 '09 #4

P: 90
It was nothing to do with what I thought. I changed the 'Max' to 'Last' and it works brilliantly.

Thanks again =)
Jan 21 '09 #5

Expert Mod 15k+
P: 31,494
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().
Jan 28 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.