Results 1 to 6 of 6
  1. #1
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28

    How do I group and count text values together from two separate fields?

    Hi there

    I'm posting with trepidation as I always end up feeling like a moron when I ask questions but am really stuck even though it seems really simple. Will do my best to explain clearly what I need. I can't code but can find my way around at macro/query builder level so need simple explanations!

    I have a report for grouping and counting occurrences from field [VisitAction] which runs from a date parameter query, based on [VisitDate]. (the DB records visits to clients, and what took place). I've been told now that they sometimes need to record two actions at a single visit so I added a second field [VisitAction2] to record this. The fields both get text values from the same lookup table. This is all fine so far but I need a way to run a report or query that totals the occurrences of each text value from both fields - each action could appear in either field. Is there an obvious way to do this? Or does the whole structure need a re-think? I've tried lots of things but keep getting stuck because it's text not numbers. The nearest I've got is a crosstab query (new to me!) but it split separate records for combinations of the two fields - see screenclip below






    How would I go about approaching this? I should say I don't actually need it to appear in pretty report form, just some way of showing the totals effectively.

    Thanks in advance

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    The screen clip is missing.
    Really need some data to understand the situation and sample of counts to show what is expected.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Screenclip not displayiing for me in IE or Chrome. Can use forum Advanced editor to build a table in post.

    Two VistAction fields is not a normalized structure, however, there are ways to work around this.

    1. IIf() expression in query to construct a field to use for aggregating records

    2. use UNION query to rearrange fields to normalized structure and use that query to aggregate records
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    This is all fine so far
    Actually, as June7 noted, it's not. Lack of normalization is often the cause of your problem and sure, there are work-arounds. What if they come back next month and ask for another measure - add another field and have to modify all your reports/forms/queries? To need to do this is a sure sign of design problems. As a general rule, you should never be in a position where you have to add additional fields of the same attribute (characteristic). I know you've been around here before and hope that I've never answered in a way that made you feel moronic, so at the risk of repeating myself, do you need pointers to entity/relationship topics? Understanding that is is pivotal IMHO.
    You probably need a table for visit info (with fields like visit id, location, date etc.) and a table for visit actions/outcomes/plans - whatever you call them. Because this would be a many times an action can be related to many visits, it would be a many to many relationship.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ScottishFi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    28
    Thanks everyone for replies. I totally agree this is not a great design but the db is already in use and I needed a quick workaround. I genuinely do understand the principles of normalization and basic structure but am figuring the rest out as I go along. There's no way I will be trying to add anything else to this if needed.

    I'll give the options June7 mentioned a try and see if I can make that work, otherwise I'll need to go back to first principles. Thanks for the steer and the input, I appreciate it.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    So exactly what data is saved in VisitAction and VisitAction2?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 01-23-2017, 08:53 AM
  2. Replies: 2
    Last Post: 11-26-2016, 01:07 PM
  3. Group duplicate text fields as one?
    By jset818 in forum Queries
    Replies: 13
    Last Post: 10-24-2014, 01:05 PM
  4. Replies: 10
    Last Post: 05-22-2013, 02:56 PM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums