Recommendation On Creation of this Table

[rochy81]

Hi, Sorry to bother you again. I'm not sure if you remember me or not but I was the one who asked you for help on a script to normalize an excel spreadsheet to an access database. I want to add another field to that table using that script you wrote for me called "Number". I tried to modify it but I don't understand the script.



I thought I could just add OutRS.Fields(3) = .Fields(FieldIX).Value
or change the FieldIX to equal 3 or both but that did not work.

Can you help me out once more?

Thank you for your help again!

For FieldIX = 2 To .Fields.Count - 1
If Len(.Fields(FieldIX) & "") > 0 Then
OutRS.AddNew
OutRS.Fields(1) = .Fields(1)
OutRS.Fields(2) = .Fields(FieldIX).Name
OutRS.Fields(3) = .Fields(FieldIX).Value
OutRS.Update

[rochy81]

I played with the script again and it looks like the script below.

It's strange but all of the values are matching up correctly but it's not under the correct field. For example, part numbers would appear under quantity.

Set InRS = db.OpenRecordset("XLsheetTest", dbOpenDynaset)
Set OutRS = db.OpenRecordset("Test", dbOpenDynaset)
With InRS
Do While Not .EOF
For FieldIX = 3 To .Fields.Count - 1
If Len(.Fields(FieldIX) & "") > 0 Then
OutRS.AddNew
OutRS.Fields(1) = .Fields(1)
OutRS.Fields(2) = .Fields(2)
OutRS.Fields(3) = .Fields(FieldIX).Name
OutRS.Fields(4) = .Fields(FieldIX).Value
OutRS.Update
End If
Next FieldIX
.MoveNext
Loop

[RuralGuy]

The destination table would need an additional field added first. The .Fields(index) is zero based so .Fields(4) is actually the 5th field. What value from the source were you planning to put in this new field? You were not thinking of naming the field Number were you because that is a reserved word in Access.

[rochy81]

Oops, yeah, I was using Number as a field. I'll Change it to Order. It's a simple integer. That field would be the 2nd field in the excel file. It would be the 3rd when it's been imported into access though because the AutoId field is added which is first.

So it should look like
ID
Date
Order
Part
Quantity

After I ran the script it actually came out as
ID
Date
Quantity
Order
Part

I attached a test Excel sheet. Please look at Sheet2.

Thanks again!

[RuralGuy]

So you need something like this attachment?

[rochy81]

Yeah, oh that's weird. I modified the script that way too but my fields came out jumbled.

Thanks!

also what is that ModGetOpenFile script?

[RuralGuy]

I used the design view of the table to arrange the fields. ModGetOpenFile is another issue I was working on for a poster and using your db to do some stuff. Sorry, just ignore it.