Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    JunctionTable (2003)

    Edited by HansV to insert <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags around data to preserve spacing - see <!help=19>Help 19<!/help>.

    Hi, this is probably easy if you know how. I have a table which gives me an Id and several fields 1,2,3,4... with a value in each cell (horizontally), as:
    <pre> 1 2 3 4
    IdCar a b c d
    </pre>

    What I want to do is create a procedure where I can automatically fill in a junction table with the Id number, the fields and the values like this:
    <pre> Period Cost

    IdCar 1 a
    IdCar 2 b
    IdCar 3 c
    </pre>

    Hope that makes sense - I'm just not sure how to do this - can you h elp?

    Regards.

    Ina

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: JunctionTable (2003)

    See NormalizeDenormalize - it's a free download (although a $1 donation is appreciated by the author) that demonstrates how to convert one to the other.

    Feel free to post additional questions if necessary.

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: JunctionTable (2003)

    Hi - thanks for this. I'll try it out.

    Ina

  4. #4
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: JunctionTable (2003)

    Hi - re this query - this is the Normalise Mod: it stops at If Not IsNull(rs1("Value" & i)) Then saying "not found in this db", wiping out my original table values. I have tried using the exact same code and renaming my tables and fields, but the same thing happens.... not terrific in code so this is difficult for me anyway. Can you help? Cheers.


    Option Compare Database
    Option Explicit
    '**********************************
    'Created by Roger Carlson *
    'Roger.Carlson@spectrum-health.org*
    'Rog3erc@aol.com *
    '**********************************


    Sub NormalizeTable()
    'this is the main subroutine which calls the others
    CreateNormalizedTable ("table1")
    NormalizeIt "Table2", "table1"
    End Sub

    Function NumberOfFields(DenormTable As String) As Integer
    Dim dbs As DAO.Database, tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(DenormTable)
    ' Count fields in Fields collection of TableDef object.
    NumberOfFields = tdf.Fields.Count
    'MsgBox NumberOfFields
    Set dbs = Nothing
    End Function

    Sub CreateNormalizedTable(NormTable As String)
    On Error GoTo Err_CreateNormalizedTable

    Dim db As DAO.Database
    Dim tblNew As DAO.TableDef
    Dim fld As DAO.Field
    Dim IndexNumber As Integer
    Set db = CurrentDb

    'delete table
    db.TableDefs.Delete NormTable

    ' Create the table and a field
    Set tblNew = db.CreateTableDef(NormTable)
    Set fld = tblNew.CreateField("ID", dbInteger)
    tblNew.Fields.Append fld
    Set fld = tblNew.CreateField("Value", dbInteger)
    tblNew.Fields.Append fld

    ' Append table to TableDef collection
    db.TableDefs.Append tblNew

    Exit_CreateNormalizedTable:
    Exit Sub

    Err_CreateNormalizedTable:
    If Err.Number = 3265 Then
    Resume Next
    Else
    MsgBox Err.Description
    Resume Exit_CreateNormalizedTable
    End If
    End Sub

    Sub NormalizeIt(DenormTable As String, NormTable As String)
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim FieldCount As Integer, i As Integer

    FieldCount = NumberOfFields(DenormTable)
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(DenormTable) 'table with denormalized format
    Set rs2 = db.OpenRecordset(NormTable) 'table with normalized format

    rs1.MoveFirst

    Do While Not rs1.EOF
    For i = 1 To FieldCount - 1
    If Not IsNull(rs1("Value" & i)) Then
    rs2.AddNew
    rs2!ID = rs1!ID
    rs2!Value = rs1("Value" & i)
    rs2.Update
    End If
    Next i
    rs1.MoveNext
    Loop

    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: JunctionTable (2003)

    Have you checked to make sure that Table2 has a field named "Value1"? The full code will recreate the tables each time, so yes, it wipes out any values. If it's failing, it may be in the way you're calling the routine, so show us the code you're using for that purpose.

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> I just realized that it sounds like you are trying to run this code as is, which won't do you much good, since it is merely an illustration of HOW to do something like this, not prebuilt code you can just run without changes.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: JunctionTable (2003)

    Hi, yes I realised that and have tried putting in my own values, but I'm a code "virgin" so find using modules a bit difficult. Also, the code does not seem to be able to deal with the fact that I actually want a 3 field return - the Id, a period (which is the field header in your example) and a cost therefore I need to take a table

    Id Period1 Period 2 Period 3

    A 20 30 40

    and show

    Id Period Value

    A Period 1 20
    A Period 2 30
    A Period 3 40

    This code doesn't seem to help me with that as it returns just the Id and the value.

    I've tried uploading a zip showing what I'm dealing with, but it doesn't seem to be getting through.

    The main difficulty is that I am not yet experienced in VBA.

    If you have any other suggestions, I'd be grateful indeed.

    Ina

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: JunctionTable (2003)

    Make sure that the zip file is less than 100 KB and that you don't preview your post after specifying the attachment.

    See <post#=401925>post 401925</post#> for instructions on how to reduce the size of the zipped database.

  8. #8
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: JunctionTable (2003)

    Hi thanks - here it is compacted .

    Kind regards.

    Ina

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: JunctionTable (2003)

    See the attached version. I created an empty table tblNormalized with 3 fields IdNumber, Period and Value.
    The module basConvert contains a procedure based on the one from NormalizeDenormalize that will fill tblNormalized.
    The field names in HistoryTest contain trailing spaces. I took the liberty of removing those when copying to tblNormalized.
    To run the procedure, open the module, click anywhere in NormalizeTable, then press F5.

  10. #10
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: JunctionTable (2003)

    Thank you very much!! I'll have a shot of it tonight.

    Kind regards.

    Ina

  11. #11
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: JunctionTable (2003)

    Hi, your code works a dream in the original database, however when I try and run it on the extended table in my own database it is breaking at Dim dbs as DAO.Database - as I've said, I'm not into this lark yet - can you advise what's going wrong, or will I just export all my tables into the database you sent??

    Regards.

    Ina

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: JunctionTable (2003)

    The code needs a reference to the DAO library.

    In the "real" database, activate the Visual Basic Editor (Alt+F11), then select Tools | References...
    Scroll down until you see Microsoft DAO 3.6 Object Library, then tick the corresponding check box and click OK.
    The code should now work.

  13. #13
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: JunctionTable (2003)

    Thanks for your response - unfortunately when I click Alt F11, I just get taken out of the VBA window into the modules window, where the option Tools References doesn't seem to be available - thanks for your patience - I'm sure it's easy when you know how!

    Ina

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: JunctionTable (2003)

    If you are already in the Visual Basic Editor, you don't need to press Alt+F11. This was only meant for if you were in the Access window. The idea is that you need to be in the Visual Basic Editor to be able to select Tools | References...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •