Solved

Multiple Columns for same field

Posted on 2006-11-20
36
227 Views
Last Modified: 2012-05-05
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


0
Comment
Question by:wdelaney05
  • 17
  • 11
  • 8
36 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17983693
wdelaney05,
Is the format consistent, with the first 5 fields as (Order Number,   CustomerName,  Address , City , State)
0
 

Author Comment

by:wdelaney05
ID: 17983879
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17984276
so basically, it is the productname and qty that are repeated per order number.
up to how many times are these fields repeated?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 350 total points
ID: 17984439
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
 

Author Comment

by:wdelaney05
ID: 17984800
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17984830
you can place the codes in a module, or in a forms module.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 150 total points
ID: 17988203
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17988222
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
 

Author Comment

by:wdelaney05
ID: 17988232
thanks, GRayL,
where do I put your code from above?
Regards,
wdelaney
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17988581
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
 

Author Comment

by:wdelaney05
ID: 17988655
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
 

Author Comment

by:wdelaney05
ID: 17989966
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17989988
I took care of that in my response.
0
 

Author Comment

by:wdelaney05
ID: 17989996
GRayL,
Ah! Right, so, on to testing your code, then!
I will start now.
Regards,
wdelaney
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17990007
<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
 

Author Comment

by:wdelaney05
ID: 17990247
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
 

Author Comment

by:wdelaney05
ID: 17990343
capricorn1,
My file is posted on www.ee-stuff.com under  Question ID: 22067503 .
Best regards,
wdelaney
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17990427
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:wdelaney05
ID: 17990522
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17990555
just post if you encounter any problem

good luck!!!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17990652
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17990671
The second last docmd.runSQL needed msql attached.
0
 

Author Comment

by:wdelaney05
ID: 17990783
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17990970
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
 

Author Comment

by:wdelaney05
ID: 17991137
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
 

Author Comment

by:wdelaney05
ID: 17991415
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17991616

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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17991654
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
 

Author Comment

by:wdelaney05
ID: 17991838
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
 

Author Comment

by:wdelaney05
ID: 17991858
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17991956
if you want to try to import the data from the excel file

you will call the sub
     ImportXl
0
 

Author Comment

by:wdelaney05
ID: 17995734
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
 

Author Comment

by:wdelaney05
ID: 17995843
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17995851
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17996170
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
 

Author Comment

by:wdelaney05
ID: 17998001
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now