Multiple Columns for same field

Hi,
I tried asking this before - it seems I might have stumped the experts. I'll try again.
On an Excel spreadsheet, I have multiple columns for ProductOrdered for each customer - this spreadsheet is given to us from our customer from their website download. What's the best way I can import this into my Access database? - it has an Orders and an Order Details table that is very similar to the Northwind database tables.
Here's what my Excel spreadsheet looks like:
Order Number    CustomerName  Address          City              State  ProductName     Qty  ProductName         Qty
1023                   Sarah Jones     123 Main St     Birmingham  AL      Welcome Basket   1   Thankyou Basket     2
1024                  Bob Wallace       542 23rd St    Chicago        IL       Thankyou Basket   1
How would I pull in the Order Details and Order information into my database?
Any assistance would be greatly appreciated!
best regards,
wdelaney


wdelaney05Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
let us start with this

Sub importXl()
Dim sPath As String
sPath = "C:\xFile.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "XlTable", sPath, True
End Sub

Sub GetData()
Dim rs As DAO.Recordset, rsArr As Variant, j As Integer, x As Integer
Dim strRs As String, rsOD As DAO.Recordset, rsO As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("XlTable")
Set rsO = CurrentDb.OpenRecordset("Orders")
Set rsOD = CurrentDb.OpenRecordset("Order Details")

If rs.EOF Then Exit Sub
rs.MoveFirst
    For j = 0 To rs.Fields.Count - 1
        If InStr(rs.Fields(j).Name, "product") Then
        strRs = strRs & rs.Fields(j).Name & ","
        End If
    Next
    strRs = Left(strRs, Len(strRs) - 1)
    rsArr = Split(strRs, ",")

Do Until rs.EOF
        With rsO
            .AddNew
            !OrderID = rs("order Number")
            !customer = rs("Customername")
            !Address = rs("Address")
            !City = rs("city")
            !State = rs("State")
            .Update
        End With
        For x = 0 To UBound(rsArr)
        With rsOD
            .AddNew
            !OrderID = rs("order number")
            If x = 0 Then
            !Product = rs("ProductName")
            !Quantity = rs("Qty")
            Else
            !Product = rs("Productname" & x)
            !Quantity = rs("Qty" & x)
            End If
            .Update
            If x < UBound(rsArr) Then If IsNull(rs("Productname" & x + 1)) Then Exit For
        End With
        Next
    rs.MoveNext
Loop
End Sub
0
 
Rey Obrero (Capricorn1)Commented:
wdelaney05,
Is the format consistent, with the first 5 fields as (Order Number,   CustomerName,  Address , City , State)
0
 
wdelaney05Author Commented:
Yes. there are a few more fields that I didn't add such as ShipToName, etc. - I wanted to make my inquiry as simple as possible.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
so basically, it is the productname and qty that are repeated per order number.
up to how many times are these fields repeated?
0
 
wdelaney05Author Commented:
Capricorn1,
Sorry, I left work - I am viewing this question at home now. Wow, thanks for the great code - I can't wait to try it at work tomorrow - I'll let you know how it goes.
Re: up to how many times are these fields repeated?
The ProductName and Qty fields are repeated up to 27 times; there are 27 products in total that a customer can order. Very rarely do they order all 27 products, though. The products are gift baskets and most customers usually order less than a handful. On rare occasions, a commercial client will order one of all 27 products.

I know this is a dumb question but where would I put your code?

Best regards,
wdelaney
0
 
Rey Obrero (Capricorn1)Commented:
you can place the codes in a module, or in a forms module.
0
 
GRayLCommented:
You can also try this code.  Link to the Excel spreadsheet and accept the default name 'Sheet' .This also presumes two empty tables Orders and Details exist in the same mdb as the linked table Sheet1.  Orders contains the fields OrderNumber, CustomerName, Address, City, and State.  Details contains the fields OrderNumber, ProductName,  and Quantity

Public Sub CreateTables()

Dim mysql as String, mysql1 as String, mysql2 as String, i as Integer

mysql="INSERT INTO Orders (OrderNumber, CustomerName, Address, City, State) " _
& "SELECT OrderNumber, CustomerName, Address, City, State FROM Sheet1;"
docmd
docmd.RunSQL mysql

mysql1="INSERT INTO Details (OrderNumber, ProductName, Quantity) " 
mysql2="SELECT OrderNumber, ProductName, Quantity FROM Sheet1;"
mysql=mysql1 & mysql2
docmd.runSQL mysql
for i=1 to 26
  mysql2="SELECT OrderNumber, ProductName" & i & ", Quantity" & i & " FROM Sheet1;"
  mysql=mysql1 & mysql2
  docmd.runSQL
  i=i+1
next i

mysql="DELETE * FROM Details Where Isnull(ProductName);"
domcd.runSQL mysql

End Sub

Orders should now contain the five fields from the linked table Sheet1
Details should now contain each of the ProductName - Quantity pairs from each record in Sheet1.

To view the tables, use this query:

SELECT Orders.*, Details.* FROM Orders INNER JOIN Details ON Orders.OrderNumber = Details.OrderNumber
ORDER BY Orders.CustomerName,Details.ProductName;

0
 
GRayLCommented:
Should have been" "Link to the Excel spreadsheet and accept the default name 'Sheetn'."  If this is the first linked spreadsheet in you mdb, it will be Sheet1.  Use that number in the code.
0
 
wdelaney05Author Commented:
thanks, GRayL,
where do I put your code from above?
Regards,
wdelaney
0
 
GRayLCommented:
In a code module, like capricorn1's.  If it is in a form module, a command button named cmdCreateTables - onclick event code would call this sub.

Private Sub cmdCreateTables

>Enter the code here<

Notice this subroutine is private in a form module.


If you enter it  in a standard module, you could call it from the immediate pane.  Type CtlG and:

CreateTables

followed by Enter.
0
 
wdelaney05Author Commented:
Re: If you enter it  in a standard module, you could call it from the immediate pane.  Type CtlG and: CreateTables followed by Enter.

Ah, that's what I need!

Many thanks,
wdelaney
0
 
wdelaney05Author Commented:
capricorn1,
Your code works well - I just tested it.
I have one inquiry: I'm assuming that the first step I have to do is to import the Excel spreadsheet as external data and name the new table "xlTable" - I assumed it was an Access destination table. Is this correct? the code didn't work if xlTable was not populated first.
Also, Access can't deal with the repetition of the same column name from the spreadsheet. After the first occurrence of "Product Name" then it started naming the xlTable fields generic names such as "Field72", "Field73", etc. Since the generic names are not named "Product Name", the Order Details table only had the first occurrence of Product Name dropped into it even though some customers had multiple products ordered.
Best regards,
wdelaney

0
 
GRayLCommented:
I took care of that in my response.
0
 
wdelaney05Author Commented:
GRayL,
Ah! Right, so, on to testing your code, then!
I will start now.
Regards,
wdelaney
0
 
Rey Obrero (Capricorn1)Commented:
<I'm assuming that the first step I have to do is to import the Excel spreadsheet as external data and name the new table "xlTable" - I assumed it was an Access destination table. Is this correct? the code didn't work if xlTable was not populated first.>

that is correct.

if you are going to do more than one import, you need to delete the XLTable
do you need to delete the XLTable from codes?


re; <Product Name>
the field will be imported as Product Name,Product Name1,Product Name2
this is the case for me. (what is happening when i import the excel file)

can you post the excel file here http://ww.ee-stuff.com


0
 
wdelaney05Author Commented:
GRayL,
there is an error when I run your code. The debugger stops at the statement docmd found before the statement
docmd.RunSQL mysql. the errmessage states "Compile error: Invalid use of property".
Do you know what this means?

0
 
wdelaney05Author Commented:
capricorn1,
My file is posted on www.ee-stuff.com under  Question ID: 22067503 .
Best regards,
wdelaney
0
 
Rey Obrero (Capricorn1)Commented:
wdelaney,

i am importing it correctly,  Product Name was imported

Product Name,Product Name1,Product Name2

i am using Access 2003 _ Office Professional 2003
0
 
wdelaney05Author Commented:
I'm using Access 2002, capricorn1.
What I'm trying to do now is to link the spreadsheet and then name it xlsTable, so that I can try and use your code with a linked spreadsheet. Once I do this, then I get Product Name, Product Name1, Product Name 2, etc. when the linked spreadsheet table is opened in Access.
It looks like I may need Product Name, Product Name1, Product Name 2, etc. in my Order Details table, though. I will have to add lines to your code referencing them as such.....
I'm going to try this now...
regards,
wdelaney
0
 
Rey Obrero (Capricorn1)Commented:
just post if you encounter any problem

good luck!!!
0
 
GRayLCommented:
How about:

Public Sub CreateTables()

Dim mysql as String, mysql1 as String, mysql2 as String, i as Integer

mysql="INSERT INTO Orders (OrderNumber, CustomerName, Address, City, State) " _
& "SELECT OrderNumber, CustomerName, Address, City, State FROM Sheet1;"
docmd
docmd.RunSQL mysql

mysql1="INSERT INTO Details (OrderNumber, ProductName, Quantity) "
mysql2="SELECT OrderNumber, ProductName, Quantity FROM Sheet1;"
mysql=mysql1 & mysql2
docmd.runSQL mysql
for i=1 to 26
  mysql2="SELECT OrderNumber, ProductName" & i & ", Quantity" & i & " FROM Sheet1;"
  mysql=mysql1 & mysql2
  docmd.runSQL mysql
  i=i+1
next i

mysql="DELETE * FROM Details Where Isnull(ProductName);"
domcd.runSQL mysql

End Sub
0
 
GRayLCommented:
The second last docmd.runSQL needed msql attached.
0
 
wdelaney05Author Commented:
GRayL,
there is still an error when I run your code. The debugger still stops at the statement docmd found before the statement
docmd.RunSQL mysql. the errmessage states "Compile error: Invalid use of property".

Also, the code may not work now because when I link the spreadsheet, Access renames the columns appearing after the first occurrence of Product Name to Product Name1, Quantity1, etc.

Regards,
wdelaney

0
 
GRayLCommented:
I know, that is why I continually change mysql2.  Remove the line  - docmd - that is by itself.

Public Sub CreateTables()

Dim mysql as String, mysql1 as String, mysql2 as String, i as Integer

mysql="INSERT INTO Orders (OrderNumber, CustomerName, Address, City, State) " _
& "SELECT OrderNumber, CustomerName, Address, City, State FROM Sheet1;"
docmd.RunSQL mysql

mysql1="INSERT INTO Details (OrderNumber, ProductName, Quantity) "
mysql2="SELECT OrderNumber, ProductName, Quantity FROM Sheet1;"
mysql=mysql1 & mysql2
docmd.runSQL mysql
for i=1 to 26
  mysql2="SELECT OrderNumber, ProductName" & i & ", Quantity" & i & " FROM Sheet1;"
  mysql=mysql1 & mysql2
  docmd.runSQL mysql
  i=i+1
next i

mysql="DELETE * FROM Details Where Isnull(ProductName);"
domcd.runSQL mysql

End Sub
0
 
wdelaney05Author Commented:
GRayl,
I used your latest code and I get an error message stating "Run-time error 3075: Syntax error (missing operator) in query expression Order Number". I'm assuming that it's referencing the statement & "SELECT OrderNumber, CustomerName, Address, City, State FROM Sheet1;" in the first mysql statement. I'll have to keep playing with it; it seems like it should work....

capricorn1,
Your code works now for me. I had to set up a permanent Access table called xlTable and renamed the generic fields to ProductName1, Quantity1, etc. and then I copied/pasted the data from the spreadsheet into xlTable.
I didn't have to create Product Name1, Product Name 2, etc. in my Order Details table.




0
 
wdelaney05Author Commented:
capricorn1,
Since I get external data first to populate xlTable, how come I need the pointer (spath) to the spreadsheet file?
Here's the code at the beginning of the module that has the reference to the spreadsheet:
Dim sPath As String
sPath = "C:\Documents and Settings\wdelaney\Desktop\Wall Candy Arts DB Project\ImportSpreadsheetTest.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "xlTable2", sPath, True

best regards,
wdelaney
0
 
Rey Obrero (Capricorn1)Commented:

that is required in the command line


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "xlTable2", sPath, True
                                               ^                       ^                          ^              ^       ^
docmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames)

sPath is the FIleName
0
 
Rey Obrero (Capricorn1)Commented:
you will only use that command line if you are not going to link to the excel file
that is called importing the excel data to a table.
0
 
wdelaney05Author Commented:
capricorn1,
Re: you will only use that command line if you are not going to link to the excel file
that is called importing the excel data to a table.

ok, that makes sense to me. But the data isn't imported into xlTable2 automagically from the ImportSpreadsheetTest
spreadsheet when I run the code so I was wondering how it fit into the process. The only way that I get data into xlTable2 when I run the code is if I beforehand manually copy/paste the data from ImportSpreadsheetTest into xlTable2 or manually File/Get External Data/Import into Existing Table (xlTable2).

Oh well, it's only a small matter - I can live with manually importing data into xlTable2. The most important thing was to get the data from xlTable2 into Orders and Order Details.

regards,
wdelaney

0
 
wdelaney05Author Commented:
capricorn1,
Sorry, I made a confusing statement. I meant, the only way the code runs successfully is if I manually import the data into xlTable2 first.
Many, many thanks for all of your help.
0
 
Rey Obrero (Capricorn1)Commented:
if you want to try to import the data from the excel file

you will call the sub
     ImportXl
0
 
wdelaney05Author Commented:
capricorn1,
thanks! Apparently Access was unable to append all of the data to the table and I forgot to check into it - I think that's why I resorted to copying/pasting.

The message that I got was:
Microsoft Access was unable to append all the data to the table.
The contents of the fields in 82 record(s) were deleted, and 0 record(s) were lost due to key violations.
* If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.
* If records were lost, either the records you pasted contain primary key values that already exist in the destination

I think some of the order comments in the spreadsheet are too long for Access to deal with, they must be over 255 characters. I'll take a look at all of the datatypes too. I'll have to look into Order Number in the tables to see if duplicates are allowed, etc.
0
 
wdelaney05Author Commented:
Capricorn1,

I have found my answer. Apparently, the bill to zipcode and the ship to zipcode are causing Access some problems. Since orders can be from Canada and the US, zipcodes can be either like this: 53202 or like this: L2H 3N7 or like this: 90650-7373. I'll have to format the zipcodes in the Excel file before I run the import code.
All in all, I think I'll wrap up this question. Thank you so much for both of your responses and hanging in there in with me! I am eternally grateful.
Best regards,
wdelaney





0
 
Rey Obrero (Capricorn1)Commented:
1. before the import, the Xltable must be deleted
2. if you are importing data to new table, duplicates will be allowed,
    more than 255 characters will raise error. (possibly because the default field type for tables is text,
    try to change the default field type to Memo before importing) Tools>Options >Tables/Queries Default Fieldtype
0
 
GRayLCommented:
You said: "Syntax error (missing operator) in query expression Order Number" - yet there is no space in the field name in the query.  You aren't using spaces in table, field, and variable names are you?
0
 
wdelaney05Author Commented:
Sorry, didn't see your last comments until now. I'm not sure if I can still keep adding comments after I've submitted points...
capricorn1, re: before the import, the Xltable must be deleted
Ah. I see. I just caught on to that now.

GRayL,
Re your comments about spaces in the field names: yes, there was a space discrepancy. I've been testing your code. So far, I can get the Orders table to populate automatically from Sheet1, there's another syntax error with the INSERT statement into Orders Detail, once I get that figured out it should work well.
In hindsight, I shouldn't have been so hasty in assigning points. You both deserve equal points for promptness, professionalism and working code.

Best regards,
wdelaney
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.