Results 1 to 10 of 10
  1. #1
    TennFox is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    5

    counting same vaules in multiple fields of the same table

    I could use some help. I know I can do it the long way round but I know there is a quicker better way.

    I am creating a activity database where people will enter a set of predefined items in to a form. I have the basic info... name, dept, number of call outs, number of repeat call outs. then I have a about 10 rows with the same named fields.( each row ads a 1 or 2 in the table.) location, service#, customer, time,...ect So they can enter the detailed info up to 10 calls.

    My issue is I have 1 field with a combo box... DI or BI or DI and BI. I need to count up the amounts of DI or BI and if its DI and BI either count that up or add it to the other. but Im having trouble doing that.

    I have 10 rows on the form with db/bi1, db/bi2,.....db/bi10 on each row. They are all in the same table because I couldn't figure out how to separate the information part with the activity part. The tech will do a sheet everyday and list all his calls and the results.

    I need to run a report each month and say we have had 100 DB, 40 BI and 30 Both. (or add the 30 to each). The field is a combo box with short text so Ive tried to use the count, which works on one field. When I try to do it to more than one field it doesnt work.

    My workaround is having them total it up as a raw number and deal with it that way, but I was hoping to make it easy on them and have the database do the totaling.



    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,008
    Not quite following all that. Suggest provide db for analysis. Follow instructions at bottom of my post.
    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.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,455
    Suggest you describe the "business/process" in simple, plain English --no database/table/form jargon.
    Tell us what the DI, BI represent - what are the "things involved" and how do they relate with one another?

    As June has said, I also do not understand your post.

  4. #4
    TennFox is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    5
    I have an activity database that each serviceman enters their daily activity including their name and route# and total number of call outs. Then there are several fields that document each call out. Location, Service#, customer, time, disposition.... and a field with that they either enter DI, BI or DI and BI in a combo box. (DI BI just abbreviations for Delivery Inspected, and Bid insured. or Both)

    all of the Lines _____ represent fields in the table. I need to count up the results in the DI/BI fields and get a monthly total. Each line on the activity is a seperate field Location1, location2 etc....just to differentiate between them

    looks like (This is what the paper version looks like too. so they want to keep it the same)

    Date______ Route_____ Name______ Call outs_______




    Location Service# Customer Time Disposition DI/BI Completed.
    _______ ________ _______ _____ ________ _____ _______
    _______ ________ _______ _____ ________ _____ _______
    _______ ________ _______ _____ ________ _____ _______
    _______ ________ _______ _____ ________ _____ _______
    _______ ________ _______ _____ ________ _____ _______
    _______ ________ _______ _____ ________ _____ _______

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,551
    First thing wrong is that you're using a multi-value field - don't put 2 values in the same field. Have a BI and a DI field. If a record has one and not the other, neither, or both, calculations are much easier. You might benefit from this (or something like it)

    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,008
    Should only enter a single value in DI/BI - one of 3 choices, like: DI, BI, BO. An aggregate query should work, or even a CROSSTAB. Show what you want the output to look like.

    Again, suggest providing db.
    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.

  7. #7
    TennFox is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    5

    here you go...

    Quote Originally Posted by June7 View Post
    Should only enter a single value in DI/BI - one of 3 choices, like: DI, BI, BO. An aggregate query should work, or even a CROSSTAB. Show what you want the output to look like.

    Again, suggest providing db.
    my table has only 3 entries for BH, JD, and BH/JD that pulls into the combo box.

    here is the database.

    Im just looking to query and count the occurances for a date span in all the BH/JD1, BH/JD2.... fields...just a raw number is all I need. By Date span so I can do a monthly and a year to date if needed.

    Activity - Copy.zipActivity - Copy.zip

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,008
    There is no field named DI/BI. Table Activity is not a normalized structure. I should have recognized that from your description "10 rows with the same named fields". This is true source of your difficulties. Should have a related dependent table ActivityDetails where each activity is a record, not multiple, repetitive sets of fields in Activity. Fix data structure and your aggregate calcs will be much easier to accomplish. In fact, table names might be better as: Incident, IncidentActivities.

    Then build a form/subform for data entry.

    Advise not to use spaces and punctuation/special characters in names.
    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.

  9. #9
    TennFox is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    5
    I guess I didnt explain this very well.. I am stuck with the structure of the database as it is. it grew from super simple to not so simple.(one row of data to 2 to 10) It has way too much data in it to start over or change structure. I found a some what simple answer but its not working.

    Since I posted last time the dynamics have changed. As you all are well aware when your building a database for people who dont know how they work (or dont care) they have added a bunch to it. Now, I have to find a work a round for the bosses requests.

    Heres the problem in a nut shell. I hope this explains it better.

    There are 10 fields in a table that I need to count specific values (see atachment a sample of BH/JD1 and BH/JD2). Those values have increased from 3 to 15. So Im using a combo box with the values already there so I can count them. The values are words. BH, JD, WhoNosWhat, IdontCare, and whatever they add in the future.
    The bosses are asking that for a specific date span (I know this part) how many of each there are. I dont care if I have to do them separate or have a form with a list box, or I get them all at once. They just want the raw number to put in a fancy memo.

    IVe tried SQL SELECT Count(*) FROM 2Activity
    WHERE [BH/JD1]="BH" OR [BH/JD2]="BH"; This counts the columns where the data is. The error on this is it counts ONLY the number of rows that BH is. So if BH is in 4 different fields (columns) as in attachment . It counts it as 3.


    See attachment
    Click image for larger version. 

Name:	access help.jpg 
Views:	13 
Size:	137.4 KB 
ID:	44393
    This query and I get the correct count, but its 2 columns with a total. I just want to have a query or report that has all the different values and the raw number of each value.

    IE.
    BH - 7
    JD - 3
    IDontCare - 8
    ImREadyToQuit- 100


    or if I have to do each value separate, that's fine I can do multiple reports into one. They just need the number.


    I cant submit the new dbase because its on a secure network and cant take it off. I have to work with what i have where it is. So you see im kinda stuck. Any help is appreciated.

  10. #10
    TennFox is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    5

    Solution For those who need it.

    Here is the sql code for the query for those who are looking for the solution.. I finally figured it out.

    SELECT Sum(IIf([Client/Non-Client1]='Client',1,0)) AS C1,
    Sum(IIf([Client/Non-Client1]='Non-Client',1,0)) AS NC1,
    Sum(IIf([Client/Non-Client2]='Client',1,0)) AS C2,
    Sum(IIf([Client/Non-Client2]='Non-Client',1,0)) AS NC2,
    Sum(IIf([Client/Non-Client3]='Client',1,0)) AS C3,
    Sum(IIf([Client/Non-Client3]='Non-Client',1,0)) AS NC3,
    Sum(IIf([Client/Non-Client4]='Client',1,0)) AS C4,
    Sum(IIf([Client/Non-Client4]='Non-Client',1,0)) AS NC4,
    Sum(IIf([Client/Non-Client5]='Client',1,0)) AS C5,
    Sum(IIf([Client/Non-Client5]='Non-Client',1,0)) AS NC5,
    Sum(IIf([Client/Non-Client6]='Client',1,0)) AS C6,
    Sum(IIf([Client/Non-Client6]='Non-Client',1,0)) AS NC6,
    Sum(IIf([Client/Non-Client7]='Client',1,0)) AS C7,
    Sum(IIf([Client/Non-Client7]='Non-Client',1,0)) AS NC7,
    Sum(IIf([Client/Non-Client8]='Client',1,0)) AS C8,
    Sum(IIf([Client/Non-Client8]='Non-Client',1,0)) AS NC8,
    Sum(IIf([Client/Non-Client9]='Client',1,0)) AS C9,
    Sum(IIf([Client/Non-Client9]='Non-Client',1,0)) AS NC9,
    Sum(IIf([Client/Non-Client10]='Client',1,0)) AS C10,
    Sum(IIf([Client/Non-Client10]='Non-Client',1,0)) AS NC10,
    ([C1]+[C2]+[C3]+[C4]+[C5]+[C6]+[C7]+[C8]+[C9]+[C10]) AS [Total CLIENT],
    ([NC1]+[NC2]+[NC3]+[NC4]+[NC5]+[NC6]+[NC7]+[NC8]+[NC9]+[NC10]) AS [Total NONCLIENT]
    FROM Activity
    WHERE (((Activity.Date) Between [Start Date] And [End Date]));

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

Similar Threads

  1. combo box not listing all vaules
    By koncreat in forum Forms
    Replies: 8
    Last Post: 01-30-2017, 03:20 PM
  2. Replies: 6
    Last Post: 09-22-2015, 10:36 AM
  3. Replies: 2
    Last Post: 05-21-2012, 02:06 PM
  4. Replies: 4
    Last Post: 04-09-2011, 10:39 AM
  5. Counting across multiple fields
    By shak2 in forum Programming
    Replies: 37
    Last Post: 10-20-2010, 10:20 AM

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
  •  
Other Forums: Microsoft Office Forums - Senior Forums