Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Very Simple Count

    This is a very basic question, and I've googled some examples I just cant seem to get this to work.

    Today is my first day in Access Reporting


    I have a table and I want to count the number of occurances of a value, just produce a summary.

    I've created a report and set the Detail and Page Footer visible = No.

    In the Report Footer I have created an unbounded text box and have attempted the following control source values:

    Status
    Shows a blank, I expected the last value in the table

    =Count([Status]="In Engineering")
    I expected a count of only orders In Engineering those but I got a count on the entire table

    =Count(IIf([Status]="In Engineering",0))
    I expected a count of only orders In Engineering, or a zero if there were none, but I got a count on the entire table

    =Count([CA]="Joe")
    I expected a count of those orders belonging to Joe, but again it counts the entire table



    I thought maybe it wants exact matches so I tried this:

    =Count(MID([Status],1,5)="In En")
    I expected a count of only orders In Engineering, but I got a count on the entire table again


    Our orders have 4 statuses and our team is about 10 people. I wanted to show a report that shows how many orders in each status, totaled, and broken down by the team members. But I have to crawl before I can walk and I can't seem to get basic counters going.

    I want to have a report like this:

    Code:
               status 1         Status 2        Status 3       Status 4
    
    
    Joe         34                16                 33                 19
    Mary       11                34                   0                  0
    etc
    
    Totals
    I'd probably group by Team member and do a summation at the footer. Then I could just print out the Team Member name and the total for that person.

    But i'm having a lot of trouble just getting the basics going. Have I made a really obvious glaring error?

    THanks!!

  2. #2
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    This looks like something you could do with a crosstab query. Your row headers would be your employees and column headers would be your 4 different statuses. Then, you could either count the unique records in each status or sum them depending on what type of numbers you are working with.

  3. #3
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by bdb04
    This looks like something you could do with a crosstab query. Your row headers would be your employees and column headers would be your 4 different statuses. Then, you could either count the unique records in each status or sum them depending on what type of numbers you are working with.
    Yes, that is the approach I mentioned I would be taking. The problem is the basic syntax, I cannot realize my designs!

    Remeber this is Day 1 in Access for me! Do you see any glaring syntax errors in my expressions?

  4. #4
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Probably the easiest way to get your data in order is to create your crosstab query using the wizard. Presumably, there is either a unique value (order #?) that you could count or a total of something (orders processed by day?) that you can use as your value.

    Once you get your data the way you want it, the report wizard should get you a basic report that you can then tidy up to your needs.

    Perhaps if I had a better idea of what data was in the table(s) you were querying, I could give you a more detailed response.

  5. #5
    Join Date
    Aug 2008
    Posts
    78
    Thanks. I'm using Access 2003. I don't see a cross tab query in the wizard but I wanted to build a basic report first.




    It's just one table, three fields

    TableName:OrderStatus

    3 Fields:
    OrderNumber
    Status
    Staff


    They're all text fields

    Status can take only one of 4 values, ever

    Staff has maybe 10 unique values



    The first report I'd like to generate is as follows:


    Code:
    Status1      Status2         Status3       Status4
    XXXXXX       XXXXXXX         XXXXXXX       XXXXXXX
    Where the XXXX is the total for that status. I would only populate the REPORT FOOTER Section, That's it.

    I started the wizard, and set Detail and Page Footer to blank. I figured 4 text controls ("Status1"...) and some kind of counter.

    My counter is reporting ALL rows in the table.

    You can refer to the first post to see the syntax I was using. I'd rather get this very very basic report working before moving on to the crosstab.

    Thanks!

  6. #6
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    I think you are getting the cart before the horse. If you are new to Access, it is much easier to get your data figured out and then base the report on your query results.

    I have the following table data:
    Code:
    OrderNumber	Status	             Staff
    1qwekjqwer     In Engineering	Bob
    1reaie49	        In Pre-production	Bob
    42rfgioer	        In Engineering	Jane
    938409seece    In Post-Production	Tom
    Each of the three fields is formatted as text. There is not a lookup on status, but you can certainly create one.

    Then, I go to Query > New > Crosstab Query Wizard. The wizard will guide you through creating the query with Staff as Row Source, Status as Column Source and a count of Order Number.

    That process gets you this:

    Code:
    Staff	In Engineering	In Post-Production	In Pre-production	Total Of OrderNumber
    Bob	1		1	                                            2
    Jane	1			                                            1
    Tom		                                                 1		      1
    The SQL for that query is this if that is easier for you:

    Code:
    TRANSFORM Count(OrderStatus.OrderNumber) AS CountOfOrderNumber
    SELECT tblOrders.Staff, Count(OrderStatus.OrderNumber) AS [Total Of OrderNumber]
    FROM OrderStatus
    GROUP BY OrderStatus.Staff
    PIVOT OrderStatus.Status;
    Once you get this done, you can go to Report > New > Report Wizard and bind your report to that query. Then, once the report wizard does it's thing, you can go into the design view of the report and tweak to your hearts content.

    Hope this helps!
    Ben

  7. #7
    Join Date
    Aug 2008
    Posts
    78
    Wow that crosstab query is excellent! It's a great launching point for me. Much appreciated!

  8. #8
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Glad that helped get you going in the right direction. Please post back if you get stuck again.

  9. #9
    Join Date
    Aug 2008
    Posts
    78
    The crosstab works great. I can tell how many orders each engineer is managing in each stage of the order. What I cannot easily get to show up on the report is a count of each status. In other words it totals to the right but not down.

    In your example, can I get the total number of "In Engineering" to appear? I was trying to use the COUNT function but it kept counting all the rows.

    I have tried combinations of =COUNT([Status]='In Engineering') without any luck. Any ideas?


    I first tried to edit the crosstab query but i kept breaking it, so I figured a 4 counters in the report would be safer to attempt.

  10. #10
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Did you make the report using the wizard? That usually provides summary options. By using the crosstab however, you are going to want to use sum instead of count. That way it is adding together all of the orders in each bucket. In the report footer, add a test box and set it's control source as =sum([NameOfFieldInDetail]) to do this.

    Give that a whirl and let me know if you have problems with it.

  11. #11
    Join Date
    Aug 2008
    Posts
    78
    Thats a great idea.. I'll try it out and post my results. Im off Thurs+Fri but it's bugging me.. im sure I'll try it at home

  12. #12
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    FYI, =sum([NameOfFieldInDetail]) will work in both group footers and report footers. You didn't mention you were doing any grouping, but if you do, it will work there as well.

    Good luck and have a good couple of days off!

  13. #13
    Join Date
    Aug 2008
    Posts
    78
    I figured it out on Thursday and it works great. It was very helpful to use the wizard and choose various options and save as different reports, then going in and examining the auto-created formats/fuction calls/etc. Great learning tool and time saver. thanks again for the advice!

  14. #14
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Glad to hear you got it worked out! Don't think that using the wizards is the "chicken" way out...they can be great learning tools as you are getting going. Even as you become more familiar with Access, they are still great for getting the basics of a report out there for you to then customize to your liking.

Posting Permissions

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