Crosstab Query Output

[J_Cat]

Hello,

I have a puzzle that I can't quite figure out.

Components:
1. Qty - In Parentheses ()
2. LnNum - separated by Commas
3. Item - Unique Item with many line (order) numbers and different quantities.


I have a unique Item that splits into Multiple records based on my order qty (in parentheses).

Item 5/20/2013 5/21/2013 5/22/2013 5/23/2013 5/24/2013
1A , () , () , () 48 (1) , ()
1A 42, 43 (2) 44, 45 (2) , () , () 51, 52 (2)

Any idea how I can get it to ignore the qty difference and just combine all the data for a unique part into one record like so?

Item 5/20/2013 5/21/2013 5/22/2013 5/23/2013 5/24/2013
1A 42, 43 (2) 44, 45 (2) , () 48 (1) 51, 52 (2)

**Check the attachments (below) for a cleaner look at the output. **

My transform statement reads as follows

IIf(IIf(IsNumeric(Max([LnNum]))=True,"",Max([LnNum]))=IIf(IsNumeric(Min([LnNum]))=False,Min([LnNum]),""),Min([LnNum]),IIf(IsNumeric(Max([LnNum]))=True,"",Max([LnNum])) & ", " & IIf(IsNumeric(Min([LnNum]))=False,Min([LnNum]),"")) & " (" & ([OrdQ]) & ") " AS Line


Any ideas as to how I can effect my desired result, would I need to employ an arrays to do what I want and, if so, what might that look like? Many thanks!

JO

 

[Guus2005]

The JET engine would say "Your query is too complex" and i have to agree.
Your question doesn't make any sense to me. It is too complex. Try splitting it up in smaller pieces or use a different example.

Item 5/20/2013 5/21/2013 5/22/2013 5/23/2013 5/24/2013
1A , () , () , () 48 (1) , ()
1A 42, 43 (2) 44, 45 (2) , () , () 51, 52 (2)

I don't know what i am looking at.

Please rephrase the question.

HTH

 

[J_Cat]

Hi Guus,

I wish that these text fields would accept tables, it would make the examples a lot easier.

My starting recordset looks like this:

Part Qty Line Date
1A.... 1... 42....5/20
1A.... 1... 43... 5/20
1A.... 1... 44... 5/21
1A.... 1... 45... 5/22
1A.... 1... 46... 5/23
1A.... 1... 47... 5/23
1A.... 1... 48... 5/24
1A.... 1... 49... 5/25
1A.... 1... 50... 5/25


(And unfortunately this text field ruined my spacing so the table example doesn't come through without all the "."'s - the attachments show the crosstab structure)

When I create my Transform (Crosstab) Query my results appear as:

Part.... 5/20....... 5/21..... 5/22.... 5/23........... 5/24 .....etc..........
1A.................... 44(1).... 45(1)
1A .....42, 43 (2)........................ 46, 47 (2)

The solution I am looking for is to collpse the double row into:

Part.... 5/20......... 5/21....... 5/22....... 5/23 5/24 .....etc..........
1A..... 42, 43(2)... 44(1)..... 45(1)...... 46, 47 (2)..........

Thanks for your interest.

 

[J_Cat]

AhA! Got it - I was making my Transform statement waaayyy too complicated. So I changed it to this and everything works. (KISS: Keep It Simple, Stupid)

Max([LnNum]) & " " & Sum([OrdQ])

 

[Guus2005]

glad you figured it out!