Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Merging

  1. #1
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39

    Unanswered: Merging

    Hi, I have place an attachment here to ask if it's possible to merge 'Incident Categories' where there is a 'Event Ref No' which is the same number to identify different categories in an access database. There could be any number of different 'Incident Categories'. Table is named tbl_Incidents. The first table in the attachment is what I have imported into a table (there are other fields not listed, not sure if you need to know all the fields at this time) and the second table is how I would like the categories to be merged. Cheers, Tim
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    FYI...Document attachment contains:

    Incident Category Event Ref No
    JUDICIAL 12345678
    NO OFFENCE 12345678

    Event Ref No Categories
    12345678 JUDICIAL, NO OFFENCE
    The first table, I assume, would have duplicate categories such as:
    JUDICIAL 87654321
    JUDICIAL 12345678
    NO OFFENSE 12345678
    NO OFFENSE 87654321

    My first recommendation would be to look at crosstab queries. They are kinda tricky and horrible to make forms and reports on, but the only other alternative I can see is to code something to loop through each record to build the comma separated string you want.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Quote Originally Posted by StarTrekker
    FYI...Document attachment contains:



    The first table, I assume, would have duplicate categories such as:
    JUDICIAL 87654321
    JUDICIAL 12345678
    NO OFFENSE 12345678
    NO OFFENSE 87654321

    My first recommendation would be to look at crosstab queries. They are kinda tricky and horrible to make forms and reports on, but the only other alternative I can see is to code something to loop through each record to build the comma separated string you want.
    Thanks for the recommendation. You are correct in assuming there are many duplicate categories which are often grouped by having the same Ref No. So one Ref No can have mulitiple categories. I'm brand new to code and loop so this is going to get interesting for me.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is the end result of this a report? What are you going to do with the results?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Yes, I'm sorry, the end result is a report. This table is linked to people involved in incidents, the link being the Event Ref No. Actually the end result is a contact list including phone numbers etc which are contained in the person table, but am having to delete records at the moment to prevent the people being repeated as a result of being involved in more than one 'incident'. So I would like to group the incidents together in the report so that at the end there is only one name (person to be contacted) and then list the incidents together that refer to that person. Hope this makes sense.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Makes good sense

    So I'd suggest looping through the records to build the categories string. You'll need to make a globally accessible function that does this and then use it in a query that groups the event reference numbers.

    I'll help you build it, but I want you to do it yourself so that you learn the process
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    Help would be much appreciated, just point me in the right direction and I'll give it my best shot.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, well first up you're going to need a reference to code and basic programming. Do you have such a thing?

    Once you're ready, create a Module. Inside it, create a public function to join your categories.

    Code:
    Public Function StringJoiner(vERN as Long) as String
    
    End Function
    I'm assuming that your Event Reference Numbers are numeric and long integers.

    This code gives you a the framework for a function that you can access inside any query, any form, any report and any code. It's globally accessible.

    For now, we'll just get the function returning a value. Add a return line.

    Code:
    Public Function StringJoiner(vERN as Long) as String
       StringJoiner = 1
    End Function
    There. By assigning the name of the function a value, the function returns that value. Now you have a globally accessible function that returns a 1 in all cases ^^

    Not very useful yet. Just laying some groundwork here.

    Do you have a table with the URN's listed only once -- a table that has each URN listed once? If you do, you're gonna need to let me know what it's called.

    How much Access do you know? Have you programmed in ANY environment before?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    I'm just your basic table, query, form and report man, so I hope not to test your patience too much. In other words I'll be struggling all the way with this. I don't have a reference to code and basic programming. The EventRefNum is a general number at the moment. The source data I query on is currently a linked table -> an excel spreadsheet. Before I paste new data into this I delete duplicates etc,so you see I'm a tut tut tut sort of database user. If URN is unique ref number I haven't got that table yet and to answer your last question I have never programmed in any environment before. I have access to other databases where I have seen modules and code group strings etc so I am only able to say that I have a tiny understanding of what you are referring to.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so you're going to have to make a query to give a list of unique URNs too.

    Create a new query with your table and include only the URN and sort it. Open up Properties for your query and set Unique Values to Yes.

    In the next blank column in the grid, in the Field row, enter Categories: StringJoiner([Event Ref No])

    Run the query.

    You should see your URN's only once each and a 1 displayed in the Categories column.

    Save it for now... maybe as qryURNsWithCategories.

    Are you coping with the instructions so far? Am I makings sense? Are you learning!?

    I was worried about testing your patience by stepping you through it over time
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    I have the query with unique values to yes on EventRefNo, but I'm getting a function error message on entering the field Categories: StringJoiner([Event Ref No]). I guess I haven't built the module correctly. I will keep trying before moving into further unchartered territory. This is a bit stop start because I only have restricted internet access at work and can only chip away at this when I get home, so forgive me for not replying straight away.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No problem. Sometimes it's good to take it slowly... building on each step as you go
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    StarTrekker,

    I've tried to get this module created to return a 1 next to the URN but no matter what I do I get errors. I have returned to ask for a bit more help. I have created a new query with the properties field unique valued to yes. Running that query returns the Event Ref Num without a drama, but I can't seem to correctly word the module to have it return a 1 in the adjacent field with the entry of Categories: StringJoiner([Event Ref No]). When I debug within the module there is an error message which says ambiguous name detected Stringjoiner. What should I be doing here.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make sure you only have ONE definition for StringJoiner.

    In post #8, I showed two. The first one was to show you the basic structure of a function and the second was there to show you that you can add in a line so that the function returns a value. There can be only ONE StringJoiner and it should be like the second one in post #8.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Aug 2007
    Location
    Port Macquarie, New South Wales, Australia
    Posts
    39
    I'm still trying, it's hard for me to work out how to correctly reference to the correct field. So far I have done a few things, I created a query as you described. I also created a new table with just those unique 'Event Ref Num' records in it. But when I get to creating the module I've got no idea where to insert the reference to the field from the query. So far when I have typed in Category: ([Event Ref Num]) all I have been able to achieve is to get that second column to return the exact same data as the Event Ref Num Column, so the result is giving me the same unique numbers in both columns, but alas not the required result of a single numeral 1 next to each Event Ref Num. I'm afraid I'm already confused.
    This is what I have entered in the module so far:
    Function StringJoiner(vERN As Long) As String
    ' StringJoiner = Event Ref Num
    StringJoiner = 1
    End Function

Posting Permissions

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