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]

On 2003-02-03 17:49, Russell Hauf wrote:

• 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

Did you make the new table? You told me the field types, but if you haven't made the new table as described above, you need to do so before running the code.

Let me know if this doesn't make sense,

Russell

 

[dtaylor]

Hi Russell,
It all makes sense and that is what I have done - but since there are multiples of the same customer I do not see how I can set the primary key to the customer field. Does primary not require unique values?

The data type is text.

Thank You for your Time and Interest!

Dan

 

[Russell Hauf]

Dan,

Do you not want ONE customer per record, with the (up to) 10 contracts in the same record? If so, you need to create a table of UNIQUE customers, like I said above. If you don't have unique customers, what you want to do won't work. I just re-read your original post, and you said that you want

a single customer line and each of the grouped contracts as a field on the same line - contract field 1, 2 , 3 ect???

So, you need to create a new table with unique customers. To do this open a new query. Add your customers table. Select only the customer field. Click the "Totals" button on the query design toolbar, or go to View-Totals. It should add a new line in your query that says "Total:". Leave this as Group By, and then go to Query on the menu bar, and select "Make Table Query". Run the query. Then open the table you just made in design view and add the fields Contract1-Contract10. While in Design View, make Customer the Primary Key. Then run the code.

Make sense?

Hope so,

Russell


EDIT: Added bold, above. Also, please let me know if you get this resolved or not - I know we can get it done!
This message was edited by Russell Hauf on 2003-02-04 15:48

 

[dtaylor]

Russell,
Yes - just call me a dumbass! I created the table but put ALL my customer numbers in ...read your post has *** backwards... I am with you on creating the table...

Ok - I now have a table that has one record per customer and primary set to customer. The table contains a field for each contract ...Contract1, ect... which are empty fields.

All are text fields, and on my main (old) table customer and contract are text fields.

I ran the code and get an error message of 0 with no err descrp.

I changed all constants to my table and field names.

Does the old table need to be a certain amount of columns?


Thanks for hanging in!

Dan

 

[Russell Hauf]

No, the # of columns in the old table shouldn't matter. Did you set a reference to ADO? Also, is the field in your old table named "contract"? If not, you'll need to change both cases of this line:<pre>rstNew.Fields(cstrFldContract & lngI) = rstOld.Fields(cstrFldContract)</pre>
To:<pre>rstNew.Fields(cstrFldContract & lngI) = rstOld.Fields("TheContractFieldName")</pre>
You're not dumb - just a lot going on.

If this isn't the problem, then step through the code (put your cursor somewhere in the procedure and keep hitting the F8 key) and let me know where the error occurs.

Also, I forgot one line of code. Paste in the line here that is not in your code:<pre>Sub TestRstSortOrder()
On Error GoTo HandleErr</pre>
HTH,

Russell
This message was edited by Russell Hauf on 2003-02-04 16:52

 

[dtaylor]

Russell,
OK - stepping through it stops at the first iteration.

For lngI = 1 To 10
If IsNull(rstNew.Fields(cstrFldContract & lngI)) Then


Thanks

Dan
This message was edited by dtaylor on 2003-02-04 17:12

 

[Russell Hauf]

Are the names of your contract fields "Contract1", "Contract2",...,"Contract10"?

Make sure that you have the right table names in the constants above the procedure. Old table = your current table. New=one you made with Unique accounts and the Contract1-10 fields.

Also, can you tell me what error you get?

Oh, I just found another oversight. Please add the line in bold below:

<pre>ExitHere:
On Error Resume Next
rstOld.Close
rstNew.Update
rstNew.Close
Set rstOld = Nothing
Set rstNew = Nothing
Exit Sub</pre>

-rh

 

[dtaylor]

Hi Russell,
I am not getting an error message... before adding the on error resume next I got an error message box but no descrep just error number 0.

Yes my fields are named - Contract1, Contract2 ect...

3 customers did have thier first contract copied into field Contract1 but then the code exits..


Thank You,

Dan

Edit - While stepping through it seems that the customer variable is showing the first to last record of table -
When mousing over variable shows customer number....

could this be part of the reason why it stops after 3 customers?
This message was edited by dtaylor on 2003-02-04 17:40

 

[Russell Hauf]

Hmm. Can you post the code you are using? It works for my sample table (doesn't it always work like that?).

Also, how about this?

Make a table from your original table that has just 2 fields: Customer and Contract (whatever their names may be), create a new database, import that new (2 field) table into it, and send it to me (zipping it first, of course)? If the customer name/id is not specific, then I probably wouldn't know what it means (regarding confidentiality). Again, it would just be a database with one table and nothing else. Then I could take a look at it...oh, you could also export the 2-field table to a text file, zip it and send it if that would be easier.

Another thing you could do is when you step through your code, tell me what the values for each recordset's fields are in the step before it exits (if you can send me the table, then there's no need for this).

-rh
This message was edited by Russell Hauf on 2003-02-04 17:57

 

[Russell Hauf]

Hi Dan,

Got your table. There are empty strings in several of the fields of your "new" table, so try this:

Open the "New" table (tblCustomersNew), and delete all of the ContractX columns (where X is 1-10). You can do this in Design View or while the table is open - just select the columns (or "Rows", if in Design View), right-click, and delete. Then go into Design View and re-add the fields. Do not put in a Default Value.

Then run the code again. It will take several minutes. I put a line in my code under "ExitHere", just above "Exit Sub" like this:

MsgBox "All Done"

Just to let me know when it was finished. Even with the table you sent me, it took well over a minute.

HTH,

Russell