Link to home
Start Free TrialLog in
Avatar of W D
W DFlag for United States of America

asked on

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


Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

wdelaney05,
Is the format consistent, with the first 5 fields as (Order Number,   CustomerName,  Address , City , State)
Avatar of W D

ASKER

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.
so basically, it is the productname and qty that are repeated per order number.
up to how many times are these fields repeated?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W D

ASKER

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
you can place the codes in a module, or in a forms module.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of W D

ASKER

thanks, GRayL,
where do I put your code from above?
Regards,
wdelaney
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.
Avatar of W D

ASKER

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
Avatar of W D

ASKER

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

I took care of that in my response.
Avatar of W D

ASKER

GRayL,
Ah! Right, so, on to testing your code, then!
I will start now.
Regards,
wdelaney
<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


Avatar of W D

ASKER

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?

Avatar of W D

ASKER

capricorn1,
My file is posted on www.ee-stuff.com under  Question ID: 22067503 .
Best regards,
wdelaney
wdelaney,

i am importing it correctly,  Product Name was imported

Product Name,Product Name1,Product Name2

i am using Access 2003 _ Office Professional 2003
Avatar of W D

ASKER

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
just post if you encounter any problem

good luck!!!
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
The second last docmd.runSQL needed msql attached.
Avatar of W D

ASKER

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

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
Avatar of W D

ASKER

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.




Avatar of W D

ASKER

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

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
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.
Avatar of W D

ASKER

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

Avatar of W D

ASKER

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.
if you want to try to import the data from the excel file

you will call the sub
     ImportXl
Avatar of W D

ASKER

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.
Avatar of W D

ASKER

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





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
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?
Avatar of W D

ASKER

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