Array?For Each? Transpose.....

[dtaylor]

Hello All,
Is this possible?......

I have a table that is grouped by customer number and contract number. For each customer there can be from 0 - 10 contracts -so because of the contracts there are multiple customer number entries.

What I am trying to do is create a table that has a single customer line and each of the grouped contracts as a field on the same line - contract field 1, 2 , 3 ect???

An array? For Each? Is this too much to ask? Any suggestions, links, tidbits to get on the right direction....

Thanks

Dan

 

[Russell Hauf]

Hi Dan,

May I ask why you want to do this (what's your ultimate goal)?

And what do you mean when you say "grouped by"? Are there other fields in the table? Also, can 2 (or more) customers share a contract?

Thank you,

Russell

 

[dtaylor]

Hi Russel,
Thanks for the interest!.........

My ultimate goal is to reduce the number of lines and make my MIS dept happy (if that's possible... This table consists of udpates to be performed on the customer setups in our mainframe AOPS system. For the updates to be automated there needs to be one line per customer.

My table has 156,000 customers but 359,000 lines due to there being multiple contracts per customer.

What I mean by grouped (sorry wrong term..) is the unique contracts for each customer.

Sample:
Cust Fld Contract Fld
custA contract1
custA contract2
custA contract3
custB contract1
custB contract2
custC contract1
custD contract1
custD contract2
custD contract3
custE No Contract Linked

Result (hopefully)
Cust Fld ContFld 1 ContFld2 ect.
custA contract1 contract2

Yes there are other fields in the table - roughly 45 fields.

And Yes two customers can share the same contract - not prevelant but there are occurrance's.



Thanks Russell!
Dan

 

[Parra]

Hi Dan from So. CAL.

Hey, this can easily be done in Excel with a Pivot Table.

Just copy the results of your query into excel and create a pivot table.

If you don't know how to create a P.T. Let me know and I'll give you some instructions.

By the way, I know access can do this, but I just don't know how in Access.

 

[Parra]

Wait you can try, Forms, New, Pivot Table Wizard.

Let me know if this helps you.

 

[Russell Hauf]

How exactly do you copy 359,000 rows into Excel?

You can base a pivot table off of an Access table, but that still won't solve the problem, which is to enable his MIS dept. to automate updates...

So...what you are left with is an ugly coding situation. Are you sure that there are no more than 10 contracts per customer? I've done stuff like this before - let me know about the max. number of contracts per customer and I'll try to dig up my code...

-Russell

 

[dtaylor]

Hi Para in Los Angeles -- where in LA northern or southern or the valley?

Pivot tables will not work. I have never been able to pivot more than 25,000 lines in excel (not sure if it is excel or my pc settings) and a pivot table will add a field for each contract linked to a customer. Could be thousands of columns usign a pivot..ouch!

Forms-New-Pivot Table Wizard just opens a instance of pivot table in exel...

Russel - the data will be exported as a text file and there is just a little too much data for excel - so no need for excel.

Yes there are no more than 10 contracts per customer.

I knew this would be ugly - but I hope a great learning oportunity!

Anything you have will be most appreciated!

Thanks!

Dan

 

[Parra]

OK, why don't you try a crosstab query.

The customer name will be a "row heading" and contract a "column heading".

Lets see if that works for you.

By the way, I'm from Los Angeles, its a nice 88 degrees, the sun is shining bright and I got a nice breeze coming in the window.

But try the cross tab query.

Let me know if it works.

 

[dtaylor]

Hi Para - yea its nice today..I am in Long Beach.

CrossTab query will not work - since the number of contracts per customer will change (could be 1,2,3 or 10 or nothing) and cross tab allows for first and last field but not all in betweeen.

Thanks

Dan

 

[Russell Hauf]

Hi Dan,

I haven't forgotten about you, I've just been busy (1st of the month and all).
There are a few things you can do before I finish up (well, before I start) the code that will be necessary to do this.

• Create a new table from your current table - select only the customer and group by it or select distinct (so run a make-table query where you get a table of unique customers)
• Go into design view, and add fields Contract1 - Contract10
• let me know what type of fields customer and contract are

I'll get back to you today or tomorrow with the code (it is actually not that difficult, but I just have to find the time to write it).
If you've already solved this, or don't need it anymore, please let me know so I can save the time.

Thank you,

Russell
This message was edited by Russell Hauf on 2003-02-03 17:50