Results 1 to 7 of 7
  1. #1
    Rhi is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    3

    Auto populate column based on specifics of subgroup with multiple situations?

    I wasn't sure how to title this, I apologies if it doesn't make sense.

    I have a database I've created with the data out of our main system. The data lists each customer charge as a separate entry, but with the same transaction number. So Joe has 2 entries with transaction number 205, one with charge ch497, and one with ch520.
    I am trying to get a count of how many customers required direct service. So if ch497 requires service and ch520 may require service, I want to count only ch497. (I have created a list of which charges require service, may require, or is a non-service, and have that classification pulled into a table with my beginning data.) I have also created a column in my table labeled "IncludeInCount" to denote a "yes" or "no". My plan it to create a query in the end to count only the "yes".

    Is there a way I can get access to auto populate the "IncludeInCount" based on the denotation in the "Service" column? My table has 98442 lines, and that's only half of year. Any help in reducing the manual process is greatly appreciated. This is my first time in many years to be using Access again, I am very rusty.

    Here is some sample data showing each of my possible situations with the "IncludeInCount" column completed with the answer I would like to have calculate.

    Customer Transaction# date ChargeCode qty Amount Service IncludeInCount
    Joe 205 2/15/2021 ch497 1 50.00 service YES
    Joe 205 2/15/2021 ch520 1 10.00 may NO
    Smith 206 2/15/2021 ch497 1 50.00 service YES
    Smith 206 2/15/2021 ch352 1 75.00 service NO
    Fred 208 2/15/2021 ch750 1 10.00 may Yes
    Fred 208 2/15/2021 ch520 1 10.00 may NO

    "Service" charges are always included, but included only once per transaction.
    "May" charges are included if there is no "service" charge, and included only once per transaction.
    "nonservice" charges are never included and have already been removed from data.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You don't need the IncludeInCount to be a field in the table, it can be calculated in various ways (using dCount, subquery or separate query). Here is an example of one way to get your count using a union query.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why don't you just return records where ChargeCode = ch497? If you want the count of that without worrying about who, then use DCount on that field; perhaps as a calculated query field like
    Code:
    CountCharge:Count(ChargeCode, ChargeCode = "ch497")
    If doing this in code, the quotes will have to be modified.

    Having a field store a decision and then doing something with that result is an extra unnecessary step when you can do both at once.
    Last edited by Micron; 04-16-2021 at 09:41 AM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Rhi is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    3
    Thank you! I knew I needed to look at it differently, I was stuck in an IIF mindset. I think I can make these two separate query using the "Is Null" look up work. I may be back with more questions. If it works I'll be sure to make this solved.

    I know the IncludeInCount column doesn't make sense in my example, but this example is only a piece of the overall data and database purpose.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No idea whom you're addressing; probably Vlad. The colon in my post was replaced by an emoji. I've edited to fix that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Rhi is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    3
    Vlad,

    Thank you so much for your help. I followed your example and was able to get a count. The issue being, although I don't care which "service" is counted I need to be able to list the exact "service" for each transaction number that was counted. Its very ridiculous, but it's the world I work.
    Anyways, your example got me into the land of Is Null, and unmatched queries. That worked amazingly.

    Side question: Unmatched queries do not allow you to make changes to the data. Is there a way around that? I've tried searching in the forum, but not finding it. I can start a new Thread for this question if needed.

    Thank you,
    Rhi

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Rhi,
    To edit data you need to use a temporary local table (in the front-end) to hold the results of the unmatched query. What I usually do is switch the unmatched query from a select query to a make-table query and run it to create the temp table. Then switch it again from make-table to append and set it to append to the newly created temp table. Now edit the temp table and index the fields you need to uniquely identify your records. Use this temp table alongside your original table to create your editable query. Finally you need a new query to delete all records in the new temp table before running the append. Sounds a bit complicated but really it is only three lines of code or so.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 03-21-2018, 03:32 PM
  2. Replies: 3
    Last Post: 07-06-2016, 09:48 AM
  3. Replies: 4
    Last Post: 11-15-2015, 07:47 PM
  4. Auto Populate based on Combo box
    By Mpike926 in forum Forms
    Replies: 16
    Last Post: 09-07-2012, 03:16 AM
  5. Replies: 2
    Last Post: 06-21-2011, 10:08 AM

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