Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2010
    Posts
    9

    Unanswered: combine data in 5 different tables into one field in a 6th table

    I have the following problem:

    I maintain a simple log to keep track of certain bookings throughout the day at a large American law firm with branches throughout the world. Every single "booking" must be designated some sort of charge code (either internal or client related).

    For simplicity sake, my LOG table has the following fields:

    logID (primary)
    VendorID (from Vendor table)
    EmployeeID (from Employee table)
    logDATE
    logTIME
    logTYPE (from VenType table i.e.: cab, food, badge, courier)
    chargeCODE (here is the problem field)

    I need to be able to generate a unique "chargeCODE" that is generated from either the CLIENT tables or the INTERNAL tables.

    The client charge code is made up of a unique client/matter number (e.g. xxxxxxx - xxxxx being a 7digit client number - 5digit matter number).

    The internal charge codes are put together according to many factors and here is my problem. The number needs to look like this: xx xx xx xxx - xxxxx each group of "xx" being: company location dept practice - matter (each of which are stored in a seperat table e.g. Comps, Locs, Depts, etc).

    I am thinking I need some sort of tick-box asking: client or non-client. Then I want drop down choices that correspond to my needs e.g. 2 drop downs for a client and 5 for a non-client. Then, depending on the choice the user makes, this new number combination should be stored in my LOG table e.g. 20 50 02 205 - 67501.

    I've been working on this for weeks now. I've tried every combination of fields, forms, queries I can think of. Any ideas?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This does not sound like it should be stored as a flattened code. You can format that code as a single value with a query later if/when necessary.

    I'd approach it from storing the id's for each of your tables that comprise the number, assuming there isn't already an authority for creating and manging these identifiers already. Though I suspect the latter is more likely...

    How do these id's get generated right now, today, by whom?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2010
    Posts
    9

    flattened code?

    Hi Teddy and thanks for your interest in my catastrophe!!!

    I have been working with this until now as a one MATTER table solution. Meaning, I created a unique shortID for the most frequent combinations that I need but this is not an elegant nor a long term solution.

    For example:

    shortID = AB for clientA and matterB
    short ID = AC for clientA matterC etc

    The problem is really with the non-client codes (generated centrally by accounts).

    shortID = UKlitATTtravl for UK litigation attourney travel costs; or
    shortID = UKHRconf for UK HR conference costs, etc.

    There are litterally thousands of combinations. That is why it makes sense for the user to select his/her combination by every log entry anew.

    Does that answer your question?

    Bo lost in London

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No, not really.

    Why not just store the combinations in their entirety? Don't worry about how many possible combinations there are. Just become some are more likely to occur together than others doesn't mean you should be codifying just those combinations in to some kind of homebrew identifier that excludes everything else.

    What's wrong with storing clientId, matterId, codeId, etc?

    Maybe we could have some more informationa bout what a "code" is and why your users care...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2010
    Posts
    9
    Not so sure about your last few comments. I'll ignore them for now and try and better explain the problem:

    1st of all it doesn't make sense to store data that is just a combination of little bits. That is what a database is all about, no?

    And besides, even if there were no other solution (I know there is, I'm not just versed enough yet to find it), I'm not going to key all the (I believe over 485,000) possible combinations myself. Again that defeats the whole purpose of a database. One code changes and suddenly we have to change the update 27000 records.

    It's got to be possible to chose data out of various tables and store this, for lack of a better word - concatenated (I'm originally an excel expert) in another table.

    E.g.: dropdown list of 8 companies tick AG = 20
    dropdown list of 20 different sites tick London = 50
    dropdown list of 12 different departments tick Admin = 00
    dropdown list of 24 different sub departments tick HR = 520
    dropdown list of over 100 different ledger codes tick conference cost = 12345

    Final result: 20 50 00 520 - 12345

    Every user has a paper version of all this codes at the moment and must constantly consult them when keying data. This is tedious and contra productive. With a few dropdowns I can give the user an easy way to key his data and give the central accounts the possibility to update a serious of lists across the entire firm with one single update.

    Help explain the problem any better?

    Bo
    Lost in London

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by bo_danseur View Post
    Not so sure about your last few comments. I'll ignore them for now and try and better explain the problem:

    1st of all it doesn't make sense to store data that is just a combination of little bits. That is what a database is all about, no?
    Errrr.... Yes, relational databases are all about modeling relationships by storing combinations of little bits that provide a way to lookup meaningful data...

    And besides, even if there were no other solution (I know there is, I'm not just versed enough yet to find it), I'm not going to key all the (I believe over 485,000) possible combinations myself.
    You don't need to. You merely need to store the "little bits" that comprise your combinations in an appropriately normalized structure. In fact, if you adopt a more flexible model you could ask for a cartesian product that would automagically generate every possible combination for you. Granted that's not terribly useful, but that should give you an idea of some of the benefits involved with not mashing all your id's together.

    Again that defeats the whole purpose of a database. One code changes and suddenly we have to change the update 27000 records.
    Which can be done with a single UPDATE statement if you use the method I'm describing. You're stuck with figuring out how to parse 27000 records that have multiple identifiers all smashed together in a single field if you pursue your current path.

    It's got to be possible to chose data out of various tables and store this, for lack of a better word - concatenated (I'm originally an excel expert) in another table.

    E.g.: dropdown list of 8 companies tick AG = 20
    dropdown list of 20 different sites tick London = 50
    dropdown list of 12 different departments tick Admin = 00
    dropdown list of 24 different sub departments tick HR = 520
    dropdown list of over 100 different ledger codes tick conference cost = 12345

    Final result: 20 50 00 520 - 12345
    If you have the appropriate values for sites, departments, sub deparments and ledger codes, then you can generate the concatenated code on the fly with a query. for example:

    SELECT company_id & " " & department_id & " " & department_sub_id & " - " & ledger_code As MySuperAwesomeConcatenatedCode
    FROM tbl_SomeTableThatStoresJustTheIdsYouNeed


    Every user has a paper version of all this codes at the moment and must constantly consult them when keying data. This is tedious and contra productive. With a few dropdowns I can give the user an easy way to key his data and give the central accounts the possibility to update a serious of lists across the entire firm with one single update.
    Agreed, this is a good goal. However I don't think you're going to get there with your current method.


    I get the feeling there are still some business rules involved that haven't been covered.

    Edit: The last questions of my last post were asking what your users would say to me if I asked them to describe what a "code" is in your system, and how/why they use these codes.
    Last edited by Teddy; 01-21-10 at 16:32.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jan 2010
    Posts
    9

    really confused now

    First you say I should personally key in ever combination available then you say quite the contrary. Oh if I weren't so blond. I'm not as versed as all of you SQL profis. I am beginning to think I've bitten off more than I can chew with this project.

    Although, I did understand your code I don't see how it helps me. Your suggestion seems to oblige me to store all of my codes in one table rather than 5. Or am I misreading the SQL? Where do I put the code? How does the user make his choice of combinations?

    I apologise for not seeing what is most likely bleeding obvious.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is one solution:

    on the form:
    - 1 Option Group (name: Frame_ChargesCode) with two Toggle Buttons (names: Toggle_Internal (OptionValue = 1) and Toggle_Client (OptionValue = 2)).
    - 7 Combo Boxes (names: Combo_Internal_1, Combo_Internal_2, Combo_Client_1, Combo_Client_2, Combo_Client_3, Combo_Client_4, Combo_Client_5).

    These combos hold the various lists of code bits you store in the "various tables" you were mentioning.

    Now you can use the following code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Const InternalCode As Long = 1
    Private Const ClientCode As Long = 2
    Private Const InternalChargeCode As String = "@1 @2 @3 @4 - @5"
    Private Const ClientChargeCode As String = "@1 - @2"
    
    Private strChargeCode As String
    
    Private Sub Combo_Client_1_AfterUpdate()
    
        strChargeCode = Replace(strChargeCode, "@1", Me.Combo_Client_1.Value)
        
    End Sub
    
    Private Sub Combo_Client_2_AfterUpdate()
    
        strChargeCode = Replace(strChargeCode, "@1", Me.Combo_Client_2.Value)
    
    End Sub
    
    Private Sub Combo_Client_3_AfterUpdate()
    
        strChargeCode = Replace(strChargeCode, "@1", Me.Combo_Client_3.Value)
    
    End Sub
    
    Private Sub Combo_Client_4_AfterUpdate()
    
        strChargeCode = Replace(strChargeCode, "@1", Me.Combo_Client_4.Value)
    
    End Sub
    
    Private Sub Combo_Client_5_AfterUpdate()
    
        strChargeCode = Replace(strChargeCode, "@1", Me.Combo_Client_5.Value)
    
    End Sub
    
    Private Sub Combo_Internal_1_AfterUpdate()
    
        strChargeCode = Replace(strChargeCode, "@1", Me.Combo_Internal_1.Value)
    
    End Sub
    
    Private Sub Combo_Internal_2_AfterUpdate()
    
        strChargeCode = Replace(strChargeCode, "@1", Me.Combo_Internal_2.Value)
    
    End Sub
    
    Private Sub Frame_ChargesCode_AfterUpdate()
    
        Select Case Me.Frame_ChargesCode
            Case InternalCode
                Me.Combo_Client_1.Visible = False
                Me.Combo_Client_2.Visible = False
                Me.Combo_Client_3.Visible = False
                Me.Combo_Client_4.Visible = False
                Me.Combo_Client_5.Visible = False
                Me.Combo_Internal_1.Visible = True
                Me.Combo_Internal_2.Visible = True
                strChargeCode = InternalChargeCode
            Case ClientCode
                Me.Combo_Client_1.Visible = True
                Me.Combo_Client_2.Visible = True
                Me.Combo_Client_3.Visible = True
                Me.Combo_Client_4.Visible = True
                Me.Combo_Client_5.Visible = True
                Me.Combo_Internal_1.Visible = False
                Me.Combo_Internal_2.Visible = False
                strChargeCode = ClientChargeCode
        End Select
            
    End Sub
    
    Private Function IsChargesCodeValid() As Boolean
    
        If InStr(strChargeCode, "@") Then
            IsChargesCodeValid = False
        Else
            IsChargesCodeValid = True
        End If
        
    End Function
    The IsChargesCodeValid function allow checking that all the parts of the code have been entered before storing it into a table. if the "@" charracter is used in your codes replace it with some other unused one.
    Have a nice day!

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by bo_danseur View Post
    First you say I should personally key in ever combination available then you say quite the contrary.
    I'm not suggesting that at all. I'm suggesting you store company id's in a company table, department id's in a department table, ledger codes in a ledger code table, etc etc...

    Once you have all those tables populated, than you create another table that has one column for each id from each of the tables mentioned above. This is the table that gets populated when you're users select the desired combination of company/dept/sub-dept/code/etc. You wouldn't populate this table in advance. This is just where you'd store the combination of stuff that the user told you is appropriate.

    Although, I did understand your code I don't see how it helps me. Your suggestion seems to oblige me to store all of my codes in one table rather than 5.
    Nope, it's obliging you to store all of the id's that comprise the code the user has created in a single row. The difference between what you're thinking and what I'm thinking is whether you store all those id's in a single field, or whether you have a separate field for each id that comprises the code. This latter approach gives you all kinds of flexibility in how you present or report on the data.
    Last edited by Teddy; 01-21-10 at 18:02.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jan 2010
    Posts
    9
    Thanks guys for all of this. I will try and have a look at setting everything up as you've described and revert asap with results. I hope to have it up and running today (Friday) but I may need the whole weekend to sort through it.

    Will keep you posted.

    bo
    lost in london

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think taking a bit of time out and sitting back with a good read about the fundamentals of relational database design would be of incalculable benefit.
    The Relational Data Model, Normalisation and effective Database Design
    That's the best crash-course on relational database design I know of on the internet. It might look like a long read but it will save you an enormous amount of time in the long run. Problems with your initial database design will plague you for the entire lifespan of the application.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2010
    Posts
    9
    pootle flump,

    thanks for the very welcomed suggestion. i've picked up quite a few "mamoths" and have read a bit but my developments are always blocked when I want to get elaborate and do "fancy" stuff.

    i'll pick it up on the weekend and have a good read.

  13. #13
    Join Date
    Jan 2010
    Posts
    9
    Sindho,

    It's taken me a while, but finally today I've been able to work with your suggestion. Thank you for such a thorough and well thought throw solution. I know again I'm probably just being blond, but...

    After some necessary adjustments I get the combo's to populate, the toggles to work etc, but my final value e.g. "@1 @2 @3 @4 - @5" for InternalChargeCode or "@1 - @2" for ClientChargeCode aaa... How do I get it to update my table in field "charge_code"?

    blush

    Sorry!

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem, you're welcome!

    Going on with the code I previously posted, I now suppose that the value of strChargeCode must be stored in a table: MyTable that has a Text field: ChargeCode and also a primary key: Primary_Key.

    I also suppose that there are two textboxes on the form and that these textboxes are bound to those two fields in the table. I can use a button: Command_StoreChargeCode_Form with the following code:
    Code:
    Private Sub Command_StoreChargeCode_Form_Click()
    
        If IsChargesCodeValid Then
            Me!ChargesCode = strChargeCode
        Else
            MsgBox "Charges code is not correctly composed: " & strChargeCode, vbInformation, "Uncorrect charges code!"
        End If
        
    End Sub
    Now if the form is unbound, or bound to something else, or for any other reason... I want to directly store the value of strChargeCode in the table, I can use a button: Command_StoreChargeCode_SQL, with the following code:
    Code:
    Private Sub Command_StoreChargeCode_SQL_Click()
    
        Const SQL_Insert As String = "INSERT INTO MyTable ( ChargesCode ) VALUES ( '@' )"
        Const SQL_Update As String = "UPDATE MyTable SET MyTable.ChargesCode = '@' WHERE MyTable.Primary_Key) = "
        
        If IsChargesCodeValid Then
            If IsNull(DLookup("Primary_Key", "MyTable", "Primary_Key = " & lngPrimaryKey)) Then
                CurrentDb.Execute Replace(SQL_Insert, "@", strChargeCode)
            Else
                CurrentDb.Execute Replace(SQL_Update, "@", strChargeCode) & Me!Primary_Key
            End If
        Else
            MsgBox "Charges code is not correctly composed: " & strChargeCode, vbInformation, "Uncorrect charges code!"
        End If
        
    End Sub
    In the second case, the function tests to see if there already is a record in the table with a primary key matching the variable lngPrimaryKey (whatever it can be), in which case it updates the corresponding record; otherwise it creates a new record into the table.
    Have a nice day!

  15. #15
    Join Date
    Jan 2010
    Posts
    9
    Sinndho

    I've not been able to properly deal with this since last you helped me. I've had to get by on a makeshift solution without being able to properly integrate your suggestions. Are you still around? Could you posssibly still help me complete this (for me) mammoth task?

    Thanks,

    Lost in London - Bo!

Tags for this Thread

Posting Permissions

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