Results 1 to 10 of 10
  1. #1
    PMCOFFEY is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    CA
    Posts
    5

    Question COUNT Group Count Records COUNT FUNCTION

    My problem: I am using ACCESS2010. I need to count the number of specific parts ( 1 – 10 ) in INVENTORY from 5 regions of the country. The date range needs to be the past 90 days. Each region is a separate table and PART field. The fields are all identical. I need to GROUP the count on specific part #’s Say, PART 1 – PART 10 and obtain 1 COUNT of parts 1-10.
    MAIN issue. I have successfully grouped region 1 separately and got the correct count. Individually each FIELD GROUP [Region1] [COUNT] works fine. My problem is that I have not been able to successfully figure out how to add all 5 regions together and obtain an accurate combined count of all regions. I looked at every Youtube video and reviewed lots of COUNT examples. I have a number of ACCESS books - Nothing tells me how to combine (GROUP) 5 fields of DATA together and obtain one accurate count of all 5 REGIONS. I understand a fair amount of basic SQL. So, if that’s what you send me I can use it. Any help greatly appreciated. PS, I’ve successfully accomplished this in Excel. Any help greatly appreciated.


    Regards,
    Michael

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Each region is a separate table and PART field. The fields are all identical.
    Bad design - no wonder you have problems!

    I can, for now, think of only one way of doing this - in two stages.

    1 - Design and run a UNION query to gather all the fields of interest into one table.
    2 - Design and run an aggregate query on the resulting table.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In my view your structure is flawed, with the individual tables. In any case, the way to bring them together is with a UNION query, then run a totals query against that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Apparently I'm typing very slowly tonight.

    In any case, two people saying the same thing should tell you something.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    PMCOFFEY is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    CA
    Posts
    5
    Much appreciate the response. I've run a query to bring all the tables together. Your note at #2. Aggregage query.
    Here is the very simple layout.

    Part# 1-10
    Region1PARTCount
    Region2PartCount

    The question is how would you COUNT both regions together and GROUP them on PART# 1-10.
    =Count([Region1] & [Region2])? How would you structure the aggregate query on two fields.
    The problem takes place when I add [Region2] I get an error message. All of the examples that I've tried ( and work) show the addition of 1 field not 2.
    Recommendations are greatly appreciated.

  6. #6
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I'm a little confused - it doesn't take much - but I cannot understand how you end up with Region1PARTCount AND
    Region2PartCount. I'm no SQL expert but my understanding is that the fields of result sets appended via a UNION statement must match the first result set in all respects - number of fields, data type, etc. - and that the column names of the whole result set are taken from the field names of the first result set (unless you use aliases). Hence I would expect your UNION query to look something like:

    SELECT PartID, PartCount FROM tblRegion1
    UNION ALL
    SELECT PartID, PartCount FROM tblRegion2
    UNION ALL
    ...

    Now you may simply group on PartID and sum on PartCount.

    If you want, publish your table names and the definition of the fields of interest and I'll write something for you.
    Last edited by Rod; 11-09-2012 at 01:08 AM. Reason: Forgot the 'ALL' keyword

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The SQL would need to be more like:

    SELECT PartID, PartCount, 1 As Region FROM tblRegion1

    So that you have the region available.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    PMCOFFEY is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    CA
    Posts
    5
    Rod, Many thanks, I used a UNION ALL query and GROUP BY and it worked just perfect,
    It is truely amazing what you can learn from this Forum.
    Regards,
    M

  9. #9
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Just as a footnote: Paul's suggestion is worthy if you ever want to analyse the results by region, in fact you then don't need the 'ALL' keyword as every row will be unique. I shall mark this thread as solved on behalf of Paul and myself.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I misread and thought you wanted it summed by region. Luckily Rod was here!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. First Group, Second count, and third sum
    By hoachen in forum Access
    Replies: 2
    Last Post: 07-30-2012, 06:03 PM
  2. Replies: 0
    Last Post: 04-25-2011, 07:58 PM
  3. GROUP and COUNT?
    By TheMoodyFiles in forum Queries
    Replies: 2
    Last Post: 02-10-2011, 05:50 PM
  4. SQL Count and Group By
    By Tyork in forum Programming
    Replies: 11
    Last Post: 01-24-2011, 09:06 AM
  5. Access function to count records
    By mbaptista1970 in forum Access
    Replies: 6
    Last Post: 01-07-2011, 09:50 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