Results 1 to 9 of 9
  1. #1
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23

    Question Summarize data with similar data

    Hi,
    Sorry, its late and I am frustrated. My title for the post leaves a lot to be desired.



    I need to create a report that can show totals for each item, regarless of its suffix(xyz). So in other words it will take just the first 5 numerical characters and if there are 2 that are the same, it will combine them.

    ITEM, JAN
    12345xyz, 2
    12345, 3
    22222, 7
    33333xyz, 2
    33333, 4

    The result I am expecting is
    ITEM JAN
    12345 5
    22222 7
    33333 6

    I tried writing a query and using a LEFT([ITEM],5) fx to pull just the first 5 characters, and I used a total query to get the summary data. I end up with only the results of the first instance of the ITEM. In other words it forgets to display the second instance of 12345 and 33333.

    Any thoughts of suggestions would be greatly appreciated. Have a great day.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I tried writing a query and using a LEFT([ITEM],5) fx to pull just the first 5 characters, and I used a total query to get the summary data
    that is what I| would have suggested, so implies you didn't do it quite right. Suggest post the sql you developed and we can see what needs to be fixed

  3. #3
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    Quote Originally Posted by Ajax View Post
    that is what I| would have suggested, so implies you didn't do it quite right. Suggest post the sql you developed and we can see what needs to be fixed
    I created it in the Query design view, but this is the code:
    SELECT [Core Report_OLD].DocName, [Core Report_OLD].CPTCode, Sum([Core Report_OLD].[2014]) AS SumOf2014, Sum([Core Report_OLD].[2015]) AS SumOf2015
    FROM [Core Report_OLD]
    GROUP BY [Core Report_OLD].DocName, [Core Report_OLD].CPTCode
    HAVING ((([Core Report_OLD].CPTCode)=Left([CPTCode],5)));

    What I am expecting is if the CPTCode appears twice like this:
    CPTCode 2014
    12345 5
    12345ABC 5

    That the values will be summed and I will get this result:
    12345 10

    Any thoughts?

    Much thanks!

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I tried writing a query and using a LEFT([ITEM],5) fx to pull just the first 5 characters,
    where is your left function?

  5. #5
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    Quote Originally Posted by Ajax View Post
    where is your left function?
    In the HAVING section? Where should it be?

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no, it shouldn't be there, try this

    Code:
    SELECT DocName, Left([CPTCode],5) AS CPT, Sum([Core Report_OLD].[2014]) AS SumOf2014, Sum([Core Report_OLD].[2015]) AS SumOf2015
     FROM [Core Report_OLD]
     GROUP BY [Core Report_OLD].DocName, Left([CPTCode],5)
    note using numbers as field names is generally a bad idea. Unless Core Report Old is a crosstab query it is also indicative of your data not being normalised which will only cause you problems in the future.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    How does DocName fit into your data? Is it a unique value on the record? That might be why it is not grouping as you want.

  8. #8
    vicky464 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    23
    Quote Originally Posted by Bulzie View Post
    How does DocName fit into your data? Is it a unique value on the record? That might be why it is not grouping as you want.
    Docname is a field name - DOctor's Name. Your suggestion seemed to do the trick - thank you! I was thinking the Left function should be a criteria, but it is actually a field expression. That makes sense.

    And yes, I had normalized my tables but was thinking that it was my normalization that messed things up. So I imported my flat Excel file and that was the query I shared. I understand the importance of normalizng, but just wondering in your opinion, is it important to normalize when you are importing all data from one excel spreadsheet and not entering or editing anything in the tables - ever?

    Much thanks again!

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in your opinion, is it important to normalize when you are importing all data from one excel spreadsheet and not entering or editing anything in the tables - ever?
    personally I would say yes, it should be normalised, but if you are not going to use if for anything but the simplest of queries I guess it doesn't matter. Bit like saying 'I'm never going to drive my car, just sit in it - should I keep the engine topped up with oil and water?'

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

Similar Threads

  1. Query to summarize data by month
    By wpryan in forum Forms
    Replies: 1
    Last Post: 12-26-2015, 06:44 AM
  2. Need to summarize data but not sure how...
    By djlabreche in forum Queries
    Replies: 4
    Last Post: 07-08-2014, 08:17 AM
  3. Summarize data by pay periods
    By breakingme10 in forum Access
    Replies: 42
    Last Post: 01-13-2014, 08:28 AM
  4. Help a new Access user summarize data
    By afabris in forum Queries
    Replies: 3
    Last Post: 11-01-2013, 01:56 PM
  5. query to summarize top ranking data
    By CMR in forum Queries
    Replies: 1
    Last Post: 09-17-2012, 02:08 PM

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