Append data form multiple rows to one and then delete??? 1

[WB786]

I have been given an Excel spreadsheet that has the consumer name in a repeating fashion for each of their orders.

I need to be able to put all of the repeats into just one row of a table.

Example File:

Joe Smith 555-555-5555 $500 CogOrder1
Joe Smith 555-555-5555 $600 CogOrder2
Joe Smith 555-555-5555 $700 CogOrder3

Needed Output:
Joe Smith 555-555-5555 $500 CogOrder1 $600 CogOrder2 $700 CogOrder3

etc....

Thanks,

WB

 

[Blorf]

This is pretty easy, but to best help you, are you familiar with make table and append and update queries? If so, I will give advice in general, otherwise, I will give details.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[BNPMike]

Well I'm interested in what you're going to say Blorf because this is not easy, at least it isn't if wabeg has variable numbers of repeats.

Is it always CogOrder1/2/3, wabeg?

 

[Edski]

Ditto.

IF it could be done using SQL then yeah, I agree Blorf might say it is pretty easy. It can be done using vba code, but there is no IF condition on this method. It could be done. But I wouldn't say it is easy!

 

[WB786]

What I want the coding to do is take the table where the data is stored that also has an ID column as well and create another table where the data will be dumped to.

My reference is using ADO. So, I know this much. The only part is how to do the While Loop stuff to combine the data.

Also the CogOrder are not always 3 deep. Some could be 4 or 5 or 6, but I can adjust that in the coding if needed.

Thanks for the help.

WB

 

[BNPMike]

It's not a simple as that. You actually have a varying length structure that are you trying to store in a a table which of course is fixed length. This means you need to alter the table everytime a 'record' contains more fields the maximum you have encountered so far. Alternatively you could always use a table of maximum estimated size and then throw an excpetion if you encounter a record bigger than that.

Whilst Access/Jet is a file system it's actually principally a relational database ie it assumes you are using relations. As you're not, I suggest you just use VB or VBA and write to a file.

 

[WB786]

I am not too worry about the varying length of the data but before noon today I need to have some sort of coding in place to start testing a sample data that currently only has 4 or 5 records for each customer.

I just need help in what the coding will look like.

Thanks.

WB

 

[Blorf]

This is easy with sql. Here is how I have done this.

Ok, first a make table query from your table with just the name, and a blank field as "" so it is a string. Your default setting in Access will determine what the string length is. Most times people leave it at 50, so that should be OK.

Next, an update query, where you link the new table to your data table by the Name field, and you update your blank field as [blank field] & [datatable].string.

Thats it.

Hope this helps, and again, if you need more info, let me know.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[WB786]

Blorf,

Would that work? The data table can have a customer with varying amount of orders, one cust could have 3 orders then next could have 4 orders and etc.

That's why I was thinking more of coding and looping and putting the data into a new table with columns like custname amount1 order 1 amount2 order2 etc...

Thanks,



WB

 

[Blorf]

Yes it will work, because when you link them, for each occurance, there is an update. Even if some have 1 and others have 10, the 1 will get updated with 1 record, the 10 with 10 records.

Only think you need to make sure of is that the make table is unique by customer, and that the update query sorts the data in the right way, so it gets updated in the correct sequence, ie order1, order2, order3 instead of order2, order1, order3 or some random order.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[WB786]

Blorf,

I did what you said but it put all of the data in that blank field but i need the data to go to different fields so one record will have all of the data in just one row with it's own column headings. See my example below

Example File:
ID Name PhoneNumber Amount Order
1 Joe Smith 555-555-5555 $500 CogOrder1
1 Joe Smith 555-555-5555 $600 CogOrder2
1 Joe Smith 555-555-5555 $700 CogOrder3
2 Mike Smith 555-555-5556 $100 CogOrder1
2 Mike Smith 555-555-5556 $200 CogOrder2
2 Mike Smith 555-555-5556 $300 CogOrder3
2 Mike Smith 555-555-5556 $400 CogOrder4

Needed Output:
ID Name PhoneNumber Amount1 Order1 Amount2 Order2 Amount3 Order3 Amount4 Order4

1 Joe Smith 555-555-5555 $500 CogOrder1 $600 CogOrder2 $700 CogOrder3

Thank you for the help so far,

WB

 

[Blorf]

Ohhhhhh. I misunderstood, sorry.

Any way, I see that you have order1amt, and order1, order2, order3. Is a field for order1 that just has order1 in it really necessary?


If not, you can do a cross tab easy. You can use the cross tab query wizard if the cogorder actually has a 1 or 2 or 3 after it as your column source and the amount as your data.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[WB786]

Actually just drop the number after the CogOrder, I just used that to indicate that it is the first order and then next one is the second order and not that it actually has a number after it.

So, would the crosstab still work to create a new table in my example above?

Thanks,


WB

 

[Blorf]

Sadly no. You will infact need some VB code to add a number count to each record by Name. What is your table name and list the structure. I can post the code and SQL you need pretty quick. Sorry I misunderstood originally.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[WB786]

The coding needs to utilize ADO syntax in the module. The table name is tblCustOrders, the temp table name is tblCustOrdersTmp the fields are as follows:

tblCustOrders
AccountID (Unique)
Name
State
E-mail
Homephone
Workphone
Initial_Amt_Owed
Order_Date
OrderName
OrderAmount

tblCustOrdersTmp
AccountID
Name
State
E-mail
Homephone
Workphone
Initial_Amt_Owed
Order_Date
OrderName1
OrderAmount1
OrderName2
OrderAmount2
OrderName3
OrderAmount3
OrderName4
OrderAmount4
OrderName5
OrderAmount5

The current data has no more than 5 order entries. I can add more fields to the temp table if the order data that I get has more fields.

Let me know if you need anymore info.

Thanks!!!



WB

 

[Blorf]

Here is the code. Turns out that VB was not actually needed, but I put the three SQL queries in VB so you could just cut and past one thing. Any way, here you go. Hope it works for you.

ChaZ

Function Makeit()
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT DISTINCT tblCustOrders.AccountID, tblCustOrders.Name, tblCustOrders.State, tblCustOrders.[E-Mail], tblCustOrders.Homephone, tblCustOrders.WorkPhone, tblCustOrders.Initial_Amt_Owed, Space(100) AS [String] INTO Temp_Hold FROM tblCustOrders;"
DoCmd.RunSQL "UPDATE tblCustOrders INNER JOIN Temp_Hold ON tblCustOrders.AccountID = Temp_Hold.AccountID SET Temp_Hold.[String] = Trim([string]) & ([ordername] & Space(15-Len([ordername]))) & Right('0000000000' & [orderamount],10);"
DoCmd.RunSQL "SELECT Temp_Hold.AccountID, Temp_Hold.Name, Temp_Hold.State, Temp_Hold.[E-Mail], Temp_Hold.Homephone, Temp_Hold.WorkPhone, Temp_Hold.Initial_Amt_Owed, Mid([string],1,15) AS OrderName1, Val(Mid([string],16,10)) AS OrderAmount1, Mid([string],26,15) AS OrderName2, Val(Mid([string],41,10)) AS OrderAmount2, Mid([string],51,15) AS OrderName3, Val(Mid([string],66,10)) AS OrderAmount3, Mid([string],76,15) AS OrderName4, Val(Mid([string],91,10)) AS OrderAmount4, Mid([string],101,15) AS OrderName5, Val(Mid([string],116,10)) AS OrderAmount5, Mid([string],126,15) AS OrderName6, Val(Mid([string],141,10)) AS OrderAmount6, Mid([string],151,15) AS OrderName7, Val(Mid([string],166,10)) AS OrderAmount7, Mid([string],176,15) AS OrderName8, Val(Mid([string],191,10)) AS OrderAmount8, Mid([string],201,15) AS OrderName9, Val(Mid([string],216,10)) AS OrderAmount9, Mid([string],226,15) AS OrderName10, Val(Mid([string],241,10)) AS OrderAmount10 INTO tblCustOrdersTmp FROM Temp_Hold;"
DoCmd.SetWarnings True
End Function




Ascii dumb question, get a dumb Ansi

 

[WB786]

WOW!! That's great!!

I will give it a try. Will let you know how it turns out!!

Thanks,

WB

 

[Blorf]

Cool. Hope it works.

Ascii dumb question, get a dumb Ansi

 

[WB786]

That worked! Except for one problem. I needed the Orders and Amount to be put into their own columns.

Column: Order1 OrdAmt1 Order2 OrdAmt2 etc...

Thanks,


WB

 

[Blorf]

That is how I set it up. What fields did it produce for you if not seperate order names and amounts?

ChaZ

Ascii dumb question, get a dumb Ansi