Creating/Editing Tables

[yeatmanj]

I've searched and read and searched and read. The one thing that is consistant is that no one knows or wants to answer this question.

My database is normalized, however, if I want to export all records from the database where so many 1-Many relationships exist I end up with repetition of the master record for each of the relational records. I need a method to export all the records of one master-relationship into one row in Excel.

Please help. It isn't that I want to do this, it is that the people that tasked me to create this database want this. So please, if you have any ideas on how to take multiple records and export them into a single row, I would really appreciate them.

 

[ejstefl]

Well, in order to do this you'll need to have experience with Excel Automation, manipulating recordsets, and loops.

The basic idea is that you'll need to open up a recordset containing each master, or parent record. You'll need to loop through it, and for each parent record you'll need to open another recordset with all the child records.

You'll need to loop through that recordset, putting the data in columns as you go. Once you get to the end of that recordset, you can close it, proceed to the next record in your Parent recordset, and repeat.

You'll also need to hope you don't have more child records than columns available in Excel!!

Good luck!

 

[pbaldy]

I agree that it's possible. Depending on the complexity of the fields, it could be a simple function called by a query. For instance, if you had a list of teachers (the "one" table) and the subjects they taught (the "many" table), a simple function could be called from a query based on the teacher table that concatenated whatever subjects they taught into one field ("Math, English, Geography").

If it's more complicated, like invoice details with multiple fields per "many" record, you're definitely into automation, as already mentioned.

 

[gemma-the-husky]

Why not simply export your data into a normal spreadsheet, and then manually run a transpose to get the column data into rows.

 

[yeatmanj]

ejstefl, thanks for the idea.

pbaldy, ditto for concurring.

Gemma, Because the users are not always computer literate. And, what the client asks for the client gets.

 

[yeatmanj]

Alright, I have managed to work out a method for doing this. I will gladly post up the code as soon as it is fully functional. To help with that, could someone please tell me what is wrong with the syntax of this statement. It will be executed through something like DoCmd.RunSQL

DoCmd.RunSQL "create table Discharges (DischargeDate1 date(mmddyyyy), " _
& "Program1 varchar, Eligibility1 boolean, Cap1 currency, Phase1 varchar, " _
& "SRFA1 boolean, DischargeDate2 date(mmddyyyy), Program2 varchar, " _
& "Eligibility2 boolean, Cap2 currency, Phase2 varchar, SRFA2 boolean, " _
& "DischargeDate3 date(mmddyyyy), Program3 varchar, Eligibility3 boolean, " _
& "Cap3 currency, Phase3 varchar, SRFA3 boolean, DischargeDate4 date(mmddyyyy), " & _
& "Program4 varchar, Eligibility4 boolean, Cap4 currency, Phase4 varchar, SRFA4 boolean)"

 

[pbaldy]

If you put into a query and try to run it, you'll be led through the problems:

create table Discharges (DischargeDate1 date,
Program1 varchar, Eligibility1 yesno, Cap1 currency, Phase1 varchar,
SRFA1 yesno, DischargeDate2 date, Program2 varchar,
Eligibility2 yesno, Cap2 currency, Phase2 varchar, SRFA2 yesno,
DischargeDate3 date, Program3 varchar, Eligibility3 yesno,
Cap3 currency, Phase3 varchar, SRFA3 yesno, DischargeDate4 date,
Program4 varchar, Eligibility4 yesno, Cap4 currency, Phase4 varchar, SRFA4 yesno)

 

[ions]

Maybe I am mis-understanding exactly what you need but ...

You can import into Excel using MS Query this will give you what you need.

Start to Import MS Access database the usual way in Excel. At the end of the Query Wizard it will ask you if you want to view or edit data in MS Query. Choose that and you can basically build a query in Excel the same way you can in Access.
So you can choose what fields to show accross the joins.

(NOTE: I usually go right to the MS Query when the Query Wizard shows up by pressing Cancel, which asks you if yout wnat to go to MS query)

 

[yeatmanj]

Yes, you are misunderstanding. Take two tables, one has a master record, the second has many child records. Make those appear in excel in a single row with a single export function without having to manipulate the data manually.

 

[ions]

Ahhhh .... that is a tough one. Excuse my original post.

 

[yeatmanj]

Here it is. This is the code that I used to manipulate the 1-many into a 1-1 for exporting to excel or other reporting purposes. Feel free to help yourself to this code. I'm not particular. If you have better methods to suggest I'm always open for improvements. Keep in mind that this function will not work if your total new fields will create more than 255 fields total in a table. To my knowledge that is the limit of fields in an access table.

This is a function that I will be passing a facilityID to.

Public Function DischargePivot(strFacility As String) As Variant
Dim rstDischarge As ADODB.Recordset, sqlDischarge As String
Dim sqlBuildString As String
Dim strField1 As String, strField2 As String, strField3 As String, strField4 As String
Dim strField5 As String, strField6 As String, bytVal As Byte
Dim counter As Variant, strTbxValue As String
Dim rstDischarges As ADODB.Recordset, sqlDischarges As String


'SQL for pulling the many child records WHERE the children match the Parent
sqlDischarge = "SELECT tblDischarge.FacID, tblDischarge.DisDate, tlkpProgram.[Program Code], tblDischarge.Eligibility, " & _
"tblDischarge.Cap, tlkpPhase.[WO Type], tblDischarge.SRFA FROM tlkpPhase " & _
"INNER JOIN (tlkpProgram INNER JOIN tblDischarge ON tlkpProgram.ProgramTypeID = tblDischarge.Program) " & _
"ON tlkpPhase.PhaseID = tblDischarge.Phase Where FacID ='" & strFacility & "';"

'create/open recordset
Set rstDischarge = New ADODB.Recordset
rstDischarge.Open sqlDischarge, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'Determine max number of fields to pivot from 1-many to 1-1 and enumerate them in string variables.
'All should end in same number
strField1 = "DischargeDate1"
strField2 = "Program1"
strField3 = "Eligibility1"
strField4 = "Cap1"
strField5 = "Phase1"
strField6 = "SRFA1"

'Set beginning counter to match Field Names
counter = 1

'Begin sql string that will ultimately create the new table to compile the records
sqlBuildString = "create table Discharges (FacID varchar, " & strField1 & " date, " & strField2 & " varchar, " & strField3 & " YesNo, " & strField4 & " currency, " & strField5 & " varchar, " & strField6 & " YesNo"

'Move to beginning of recordset
rstDischarge.MoveFirst

'Set Do Loop Until EOF
Do Until rstDischarge.EOF
    
    'As your counter goes up to 10 it will change the secondary function to pass to for
    'creating new field names for each of the child records
    'part of the secondary function is changing the number at the end of the field name
    'and you have to change the Right() statement once it goes to 10 or greater
    If counter < 10 Then
        strField1 = CounterLess(strField1)
        strField2 = CounterLess(strField2)
        strField3 = CounterLess(strField3)
        strField4 = CounterLess(strField4)
        strField5 = CounterLess(strField5)
        strField6 = CounterLess(strField6)
    Else
        strField1 = CounterMore(strField1)
        strField2 = CounterMore(strField2)
        strField3 = CounterMore(strField3)
        strField4 = CounterMore(strField4)
        strField5 = CounterMore(strField5)
        strField6 = CounterMore(strField6)
    End If

    'add next section of sql string that will be run
    sqlBuildString = sqlBuildString & ", " & strField1 & " date, " & strField2 & " varchar, " & strField3 & " YesNo, " & strField4 & " currency, " & strField5 & " varchar, " & strField6 & " YesNo"
    
    'advance counter
    counter = counter + 1
    
    'Move to next record in recordset for child records
    rstDischarge.MoveNext
Loop

'set closing portion of sql statement
sqlBuildString = sqlBuildString & ");"

'Run SQL
DoCmd.RunSQL sqlBuildString

'Reset to beginning of recordset to loop through and insert records
rstDischarge.MoveFirst

'Create sql string for recordset of table that was just created
sqlDischarges = "Select * From Discharges;"

'create/open recordset
Set rstDischarges = New ADODB.Recordset
rstDischarges.Open sqlDischarges, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'Start AddNew to be updated after all child records have been inserted into new table
rstDischarges.AddNew

'Create Key value
rstDischarges!FacID = rstDischarge!FacID

'Reset Field Enumeration
strField1 = "DischargeDate1"
strField2 = "Program1"
strField3 = "Eligibility1"
strField4 = "Cap1"
strField5 = "Phase1"
strField6 = "SRFA1"

'Reset Counter
counter = 1

'Set Do Until EOF for record insertion
Do Until rstDischarge.EOF
    
    'Insert first set of records in first group of fields
    rstDischarges.Fields.Item(strField1) = rstDischarge.Fields.Item(1)
    rstDischarges.Fields.Item(strField2) = rstDischarge.Fields.Item(2)
    rstDischarges.Fields.Item(strField3) = rstDischarge.Fields.Item(3)
    rstDischarges.Fields.Item(strField4) = rstDischarge.Fields.Item(4)
    rstDischarges.Fields.Item(strField5) = rstDischarge.Fields.Item(5)
    rstDischarges.Fields.Item(strField6) = rstDischarge.Fields.Item(6)
    
    'Run through changing names as with creating the table
    If counter < 10 Then
        strField1 = CounterLess(strField1)
        strField2 = CounterLess(strField2)
        strField3 = CounterLess(strField3)
        strField4 = CounterLess(strField4)
        strField5 = CounterLess(strField5)
        strField6 = CounterLess(strField6)
    Else
        strField1 = CounterMore(strField1)
        strField2 = CounterMore(strField2)
        strField3 = CounterMore(strField3)
        strField4 = CounterMore(strField4)
        strField5 = CounterMore(strField5)
        strField6 = CounterMore(strField6)
    End If
    
    'Move to next record
    rstDischarge.MoveNext

    'advance counter
    counter = counter + 1

'Repeat
Loop

'Update recordset and close all open recordsets
rstDischarges.Update
rstDischarges.Close
rstDischarge.Close
End Function

That function will require two other functions to handle the Field name enumeration. This first function will handle the numbers 0-9.

Public Function CounterLess(strField As String) As String
Dim bytVal As Byte, strVal As String, strTbxValue As String
Dim strFieldName As String

'Strip Number from Field Name
strFieldName = Left(strField, Len(strField) - 1)
'Strip Name from Field to get number
strTbxValue = Right(strField, 1)
'Conver string to number and add 1
bytVal = Val(strTbxValue) + 1
'Rebuild string for field name
CounterLess = strFieldName & CStr(bytVal)

End Function

And This function will handle the numbers from 10-99

Public Function CounterMore(strField As String) As String
Dim bytVal As Byte, strVal As String, strTbxValue As String
Dim strFieldName As String

'Strip Number from Field Name
strFieldName = Left(strField, Len(strField) - 2)
'Strip Name from Field to get number
strTbxValue = Right(strField, 2)
'Conver string to number and add 1
bytVal = Val(strTbxValue) + 1
'Rebuild string for field name
CounterMore = strFieldName & CStr(bytVal)

End Function