Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Counting Records IF . . . (2003)

    One of our users has created a simple database that holds information concerning how IT literate a company's employees are. The Database is basically 1 Big Table with about 16 Fields representing responses to Questions.

    The user would now like to summarize this data. Each of these 16 Fields can contain one of 3 values: Good; None (for no experience in a particular area); and Advanced.

    Therefore, the Report has these Fields, all which need to be summarized.

    I've tried to create a Text Box that lists the value based on a certain condition, but I can't get the Syntax right.

    I was trying the following code, but I don't know how to include the actual COUNT part of it.

    =IIf([wordprocessing] = "Good" . . .

    IE, if the Field called 'wordprocessing' contains the value "Good" give me a total of how many people responded that they were Good at wordprocessing.

    I'll repeat this with 3 Text Boxes for each Question, so the Report will have 48 Text Boxes containing summaries., eg,

    12 People Said they were 'Good' at wordprocessing. 8 People said they had no experience at wordprocessing. 4 People said they were Advanced in wordprocessing.
    Ditto for next Field
    Ditto for next Field
    Etc
    Etc

    Any help with this syntax would be greatly appreciated!

    Thanks in advance. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    You can try using a function in a Query to pull the data from the table and create COUNT columns in the Query. I don't know off hand if there will be an issue with trying to use COUNT on text; you may need to run a query to re-set the text values to some numeric value. I'd guess if the text values are all the same for a given response (ie Good, Good, Good but NOT Good, Goode, Good) you can get away with the straight COUNT.

    Try this: Create a query based on the data table, drag one of the fields into a column in the query grid and click on the Sigma (totals) button in the tool bar so you can make the column do COUNT and put in the criteria Good, then run. If the number matches what a manual count would be, you're good to go. Then just do the same thing for the other fields and use the Query as the source for your Report.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Thanks. I tried that, but it didn't work. Probably b/c it's a Text Field.

    I think I'll have to use the IIF Function. Any ideas?

    Thanks again for your reply.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Actually COUNT will work on text but it'll only work if you have absolutely only the one field you are trying to count. any other data that makes the row distinct will not give you the right sum. for example, your table has

    Rating Name
    Good John
    Good Bert
    Bad Ernest

    if you try COUNT with the Name field in the query, you'll get a '1' for each GOOD because the names are different in the two rows. You'll have to run a query for ONLY Rating. This is, believe me, an easier solution than IIF'ing. Unless you like that sort of thing <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting Records IF . . . (2003)

    You won't be able to do this in a single query given the table structure you've described. This is one of the big problems with flat files like that. If you want a count, you can use a grouping query and do something like this:

    SELECT Sum(MyTable.MyField = "Good") As Good
    FROM MyTable
    HAVING Sum(MyTable.MyField = "Good") = True
    Charlotte

  6. #6
    New Lounger
    Join Date
    Sep 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Hi,

    This may seem simplistic but if your intention is to have 48 text boxes then try this:

    [Assuming your data is in table 'tblResponses' and the fields are named 'wordprocessing', 'databasing', spreadsheeting', etc]

    Use the following as the Control Source for the text boxes:

    =DCount("[wordprocessing]","tblResponses","[wordprocessing]='none' ")
    =DCount("[wordprocessing]","tblResponses","[wordprocessing]='good' ")
    =DCount("[wordprocessing]","tblResponses","[wordprocessing]='advanced' ")

    =DCount("[databasing]","tblResponses","[databasing]='none' ")
    =DCount("[databasing]","tblResponses","[databasing]='good' ")
    etc......

    Note: the single quotes around the target text within the last argument in the DCount function.

    This 'should' work and will not be tricked by the fact that the fields contain text.

    Hope this helps.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Thank you to all for all your replies.

    I've tried a couple of different things, and I keep getting, as outlined, a count of 1.

    I can't understand how I can do this in Excel, yet Access can't do this???

    In Excel, I just did the following:

    =COUNTIF(F2:F99,"Good")

    I thought I could do something similar in Access with the Syntax I tried in my initial post, but I guess I'm wrong?

    If not, then I'll just explain to the person that we'll need to Analyze it with Excel, as there are 48 Variables.

    Thank you again for all your help. It was greatly appreciated.

    Brian

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Can you post a sample of the table containing the data.
    It may be useful for us to try a couple of demo's.

    Attach only the table with the data, and under 100kb.
    Be carefull not to post sensitive data.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Oh, Hi

    Thanks for your reply also.

    I've attached the DB but removed all the Objects except for the Tables.

    Thanks for taking a look.

    Brian

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Looking at your table structure, perhaps it could have been simpler.

    You could try to create a query based on the Core-Date table and from a report, select [Name] as a grouping level.
    From there, the report wizard should give you an option to either summarize the data and to detail it also.

    With the wizard, you'll also see an option to, summarise the data.

    I hope this helps.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    I would use an unbound report with unbound text boxes.
    In the text boxes use Dcount functions. If you have a lot of records in the Core_Data table, it could be a little slow.
    Look at Report1 in the attachment
    Francois

  12. #12
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Thanks again to all for the great help you provided.

    Creating a Report based on just the Core Data was OKish, as it did give me the option to summarize all the Fields, whereas it hadn't when done from a Query. However, with ID Fields displayed instead of meaningful names, it made it a little bit hard to analyze.

    Francois' Report worked, although it only gave Grand Totals, so I couldn't break it down as much as I would have liked (can a DCount even contain multiple criteria?).

    I ended up going with Excel, simply because it was the fastest and easiest solution for such a seemingly complicated report. When there are so many variables involved, it can get a little hard to get your head around it!

    I have learned something, though, with the DCount Function, so thanks for giving me something new to work with in Access. [img]/forums/images/smilies/smile.gif[/img]

    Thanks again sincerely for everybody's input. [img]/forums/images/smilies/smile.gif[/img]

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Yes, Dcount could have more than one condition, just use something like :
    =DCount("*";"Core_Data";"[keyboard] = 3 AND [Site] = 3")
    Of course, how more controls with Dcount on your report, how slower it will be to load it.
    Francois

  14. #14
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Counting Records IF . . . (2003)

    Interesting!

    Thanks again, and thanks for the example Syntax! I'm sure that'll help in future. [img]/forums/images/smilies/smile.gif[/img]

    All the best.

    Brian

Posting Permissions

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