## Trouble designing a query

## Trouble designing a query

(OP)

hello - I need assistance with a query (or possible VBA). Here's the situation...

I have a table like this: (each X value appears at least twice)

I need to prepare a non-normalized table that shows

Almost all the data comes in groups of exactly 2 Y values, where a XTab can accomplish the goal.

But if a

There are even a few rare cases where a

to list the resulting 6 possible pairings.

I'd really appreciate some help with this.

Many thanks

Teach314

I have a table like this: (each X value appears at least twice)

X Y ========== 1000 1 1000 31 1004 4 1004 18 1023 8 1023 79 1023 83 1101 5 1101 8 etc...

I need to prepare a non-normalized table that shows

**, like this...**__PAIRS__of Y values having the same X valueX Y1 Y2 =================== 1000 1 31 1004 4 181023 8 79 1023 8 83 1023 79 831101 5 8 etc...

Almost all the data comes in groups of exactly 2 Y values, where a XTab can accomplish the goal.

But if a

**of Y values appears, I need to list the resulting 3 pairs (as in X = 1023 above).**__triplet__There are even a few rare cases where a

**of Y values appear, so my final output would need**__quartet__to list the resulting 6 possible pairings.

I'd really appreciate some help with this.

Many thanks

Teach314

## RE: Trouble designing a query

So what's the business case for this requirement?

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Trouble designing a query

PAIRS, then use those as inputs for further work.If the objection is to the non-normalization, I'm fine with that. The only important thing is the

.IDENTIFICATION of PAIRS form the input tableA much better output would be something like...

Thanks for any hints

Teach314

## RE: Trouble designing a query

You have stated two different output results!

This is why I asked for a business case, in order to determine what is required.

This includes the logic needed achieve an end result.You apparently have not solidified your thought process.You must provide a clear, concise, cohesive and complete requirement not just an example, because if I went with your first example, the result would have not REALLY met the requirement behind your second example. And looking at your second example, I might interpret what I think you meant and still miss the mark.I need more than an example now.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Trouble designing a query

I think I misinterpreted your 1st request for a 'business case' as asking for a justification for presenting my output in

non-normalizedform. So, I tried to restate the problem using a normalized output table, requiring a few extra fields as shown above. In fact, either of these suggested outputs would meet my requirements.Skip - you asked me to provide 'the logic needed to achieve an end result', but that is precisely what I'm in need of! I'm fairly certain I can write the code, but I need some hints as to how I could go about achieving my goal.

In the spirit of your '4 Cs approach: Clear, Concise, Cohesive, and Complete', let me have one more shot at being clear. I'll return to the original posting...

a table with 2 Long fields (X and Y), ordered by X, then Y. The PK is (X,Y). X values usually appear exactly twice, matched with 2 DISTINCT Y values. Occasionally, X values are matched with 3, or, rarely, 4 DISTINCT Y values.).INPUT:(I'll go back to theOUTPUT:original output requesthere) I need a table with three fields, X, Y1, Y2. (all Long, PK is (X, Y1, Y2))In the vast majority of cases, there are just 2 Y values associated with the X value, so all of this information is shown in one record.

BUT, if the X value is associated with, say, 3 Y values, then we need n(n-1)/2 = 3 records to hold all of the three possible PAIRS. (the order of records shown in my orignal posting works well, but isn't mandatory).

In very few instances, the X value is associated with 4 Y values, so we'd need n(n-1)/2 = 6 records to hold all of the six possible PAIRS of Y values.

Thanks in advance for any guidance...

Teach314

## RE: Trouble designing a query

## CODE -->

## RE: Trouble designing a query

Teach314