Turning Excel data into a 1-M relationship

[Paul Wagstaff]

Happy new year...hope someone can help with this..

The nub of this problem is to populate 2 tables in a 1-M relationship from
an imported Excel spreadsheet. The problem being that there is nothing
within the data that can uniquely identify a record.

My mdb has 3 tables:

tblImport (ID, CustName, Owner1, Process1, Owner2, Process2)
tblComplaints (PK_Complaint_ID, CustName)
tblComplaintReasons (FK_Complaint_ID, Owner, Process)

Step1: Users complete a UserForm (MS Excel) which is emailed as an
attachment to a central recipient (automated thro' Excel)

Step 2: The attachments are saved in C:\Temp\ and then imported into
tblImport (tblImport contains identical fields / columns to the .xls files
(automated thro' the database). So far so good.

I then use the autonumber (from tblImport) to seed the PK_Complaint_ID.
Having a common value in tblImport and tblComplaints, I can populate the
'one' and 'many' tables.

Finally I delete the data from tblImport.

The process is risky to say the least, relying as it does on the uniqueness
of the autonumber ID field in tblImport (e.g. if anyone were to compact the
mdb all hell would break loose).

This has been a sod to explain & I've simplified it a little bit. I'm sure
this problem has reared its head before & I'd appreciate any advice.

Thanks,
Paul

[Red]

I will attempt to help you, but I need a little more info...

1) You are using the autonumber from the import table to set the unique
number of the Complaints table?

2) If y ou answered YES to 1, then, WHY??

3) You mentioned the Complaintreasons table in the beggining of your
request for help, but never mentioned it afterward.. I think you may
have made a typo somewhere.. This is how I understand your
importation process to work (with a couple assumptions):

a)User inputs information into Excel spreadsheet
b)Spreadsheet is imported into import table
c)Complain table is populated from the import table, using the
autonumbers from the import table as unique identifiers for the
complaints table.....
d) The complaintreasons table has a relationship to the complaint
table, using the unique identifier field as the 1-m connection.

right?

~Red

[Red]

I will attempt to help you, but I need a little more info...

1) You are using the autonumber from the import table to set the unique
number of the Complaints table?

2) If y ou answered YES to 1, then, WHY??

3) You mentioned the Complaintreasons table in the beggining of your
request for help, but never mentioned it afterward.. I think you may
have made a typo somewhere.. This is how I understand your
importation process to work (with a couple assumptions):

a)User inputs information into Excel spreadsheet
b)Spreadsheet is imported into import table
c)Complain table is populated from the import table, using the
autonumbers from the import table as unique identifiers for the
complaints table.....
d) The complaintreasons table has a relationship to the complaint
table, using the unique identifier field as the 1-m connection.

right?

~Red

[Paul Wagstaff]

Red,

Yep. Imagine column A data goes into the 'one' table and columns B-M are to
go into the 'many' table. For most records users will only complete maybe
columns A-C (hence lots of empty fields, hence I'd prefer a 1-M)

I need to split the data from tblImport into two tables. Once I populate the
'one' table, I need to go back to tblImport and, for each record that now
exists in tblComplaint, populate the many reasons behind it.

Sounds toturous, and I'm thinking of just loading it in one table and hang
the consequences!

Cheers,
Paul

"Red" <do**********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I will attempt to help you, but I need a little more info...

1) You are using the autonumber from the import table to set the unique
number of the Complaints table?

2) If y ou answered YES to 1, then, WHY??

3) You mentioned the Complaintreasons table in the beggining of your
request for help, but never mentioned it afterward.. I think you may
have made a typo somewhere.. This is how I understand your
importation process to work (with a couple assumptions):

a)User inputs information into Excel spreadsheet
b)Spreadsheet is imported into import table
c)Complain table is populated from the import table, using the
autonumbers from the import table as unique identifiers for the
complaints table.....
d) The complaintreasons table has a relationship to the complaint
table, using the unique identifier field as the 1-m connection.

right?

~Red

[Red]

I can think of 2 seperate ways to do this..

Both require that you completely forget about using that silly
autonumber from the temp table... 1 requires you forget you even need a
temp table ;)

I will give you the way I think would work best for you... This is the
progmatic way, but it works, and work very good too... You may need to
change the X variable to a starting value of 2 if you have a header
line in your report... you may even need to tweak it a bit for your
needs... But, this is how I would handle it

(Copy and paste below)
'*************************CODE STARTS
HERE************************************
Public Function ImportExcelReports(strFileName As String, Optional
strWorkBookName As String) As Boolean
Dim XLS As Object, Wkb As Object, rs1 As Recordset, rs2 As Recordset,
rs3 As Recordset, X As Integer, Y As Integer, LastNumber As Long
On Error GoTo HandleError
ImportExcelReports = False
'Open Excel, and Open your file
Set XLS = CreateObject("Excel.Application")
XLS.Application.Visible = True
XLS.Application.workbooks.Open strFileName

'Set your wokb pointer to the worksheet you want to grab the data
from
If strWorkBookName <> "" Then
Set Wkb =
XLS.Application.XLS.Application.activeworkbook.wor ksheets(strWorkBookName)
Else
Set Wkb =
XLS.Application.XLS.Application.activeworkbook.wor ksheets(1)
End If

'Get the last used number from your Complaint table'
Set rs1 = CurrentDb.OpenRecordset("SELECT
Max(tblComplaints.PK_Complaint_ID) AS ID FROM tblComplaints;")
'Set up your recordsets for entry
Set rs2 = CurrentDb.OpenRecordset("tblComplaints")
Set rs3 = CurrentDb.OpenRecordset("tblComplaintReasons")

'This is where you add the data
Do While Wkb.cells(X, 1) <> ""
rs1.Requery
If rs1.RecordCount < 1 Then GoTo HandleError
LastNumber = rs1("ID")
'Add your customer to your complaints table
rs1.Edit
rs1("PK_Complaint_ID,") = LastNumber
rs1("CustName") = Wkb.cells(X, 1) ' This adds column 1 to
the name of the customer
rs1.Update
'Now add their complaints.. this assumes that there are no more
complaints after the first empty workbook cell
Y = 2
Do While Wkb.cells(X, Y) <> ""
rs2.Edit
rs2("FK_Complaint_ID") = LastNumber
rs2("Owner") = Wkb.cells(X, Y)
rs2("Process") = Wkb.cells(X, Y + 1)
rs2.Update
Y = Y + 2 ' Add 2 to y, because from your info, it
seems the 'owner' repeats/changes for each Process
Loop
X = X + 1
Loop

XLS.Application.Quit
ImportExcelReports = True
GoTo EndMe


HandleError:
MsgBox "There has been an error, please call your database
administrator!"
ImportExcelReports = False
GoTo EndMe

EndMe:

End Function
'*************************CODE ENDS
HERE************************************

[Red]

Also, you may need to fix it, as google has screwed up my lines.. I'm
horrible at making 'line continuations' ;)

And, as usual, make SURE YOU USE A BACKUP TO TEST! =P

[Paul Wagstaff]

Red

Having slept on it, looking up and then incrementing a max(Id) value is
miles better - I'll work through thias today.

Thanks for the advice / code.

Paul.
"Red" <do**********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I can think of 2 seperate ways to do this..

Both require that you completely forget about using that silly
autonumber from the temp table... 1 requires you forget you even need a
temp table ;)

I will give you the way I think would work best for you... This is the
progmatic way, but it works, and work very good too... You may need to
change the X variable to a starting value of 2 if you have a header
line in your report... you may even need to tweak it a bit for your
needs... But, this is how I would handle it

(Copy and paste below)
'*************************CODE STARTS
HERE************************************
Public Function ImportExcelReports(strFileName As String, Optional
strWorkBookName As String) As Boolean
Dim XLS As Object, Wkb As Object, rs1 As Recordset, rs2 As Recordset,
rs3 As Recordset, X As Integer, Y As Integer, LastNumber As Long
On Error GoTo HandleError
ImportExcelReports = False
'Open Excel, and Open your file
Set XLS = CreateObject("Excel.Application")
XLS.Application.Visible = True
XLS.Application.workbooks.Open strFileName

'Set your wokb pointer to the worksheet you want to grab the data
from
If strWorkBookName <> "" Then
Set Wkb =
XLS.Application.XLS.Application.activeworkbook.wor ksheets(strWorkBookName)
Else
Set Wkb =
XLS.Application.XLS.Application.activeworkbook.wor ksheets(1)
End If

'Get the last used number from your Complaint table'
Set rs1 = CurrentDb.OpenRecordset("SELECT
Max(tblComplaints.PK_Complaint_ID) AS ID FROM tblComplaints;")
'Set up your recordsets for entry
Set rs2 = CurrentDb.OpenRecordset("tblComplaints")
Set rs3 = CurrentDb.OpenRecordset("tblComplaintReasons")

'This is where you add the data
Do While Wkb.cells(X, 1) <> ""
rs1.Requery
If rs1.RecordCount < 1 Then GoTo HandleError
LastNumber = rs1("ID")
'Add your customer to your complaints table
rs1.Edit
rs1("PK_Complaint_ID,") = LastNumber
rs1("CustName") = Wkb.cells(X, 1) ' This adds column 1 to
the name of the customer
rs1.Update
'Now add their complaints.. this assumes that there are no more
complaints after the first empty workbook cell
Y = 2
Do While Wkb.cells(X, Y) <> ""
rs2.Edit
rs2("FK_Complaint_ID") = LastNumber
rs2("Owner") = Wkb.cells(X, Y)
rs2("Process") = Wkb.cells(X, Y + 1)
rs2.Update
Y = Y + 2 ' Add 2 to y, because from your info, it
seems the 'owner' repeats/changes for each Process
Loop
X = X + 1
Loop

XLS.Application.Quit
ImportExcelReports = True
GoTo EndMe


HandleError:
MsgBox "There has been an error, please call your database
administrator!"
ImportExcelReports = False
GoTo EndMe

EndMe:

End Function
'*************************CODE ENDS
HERE************************************