Results 1 to 6 of 6
  1. #1
    pellissier is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    13

    Post Report Grouping Conditional Formatting?

    I have a report which is grouped ItemCode which shows different departments and the price they have charged for the items. Some Departments charge different prices for the same item and I need to identify those. In the example below I want to see the group ItemA but not the Group ItemB - does this make sense? I can query out and only show ItemA Dept2 & Dept 3 but i need to see all of Group Item A. Can anyone help? All data is in one table though I can make new tables to split out the data if that's the only way to get the report I need.


    Item Department Price
    A
    Dept1 $12.00


    Dept2 $14.00
    Dept3 $12.00
    ---------------------------------------------------------------------
    Item Department Price
    B
    Dept2 $5.00
    Dept 3 $5.00

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What determines the requirement for ItemCode A and not B? Can you just have filter criteria of <>"B"? Need a better understanding of your data and requirements.
    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
    pellissier is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    13
    I could do the filter but I need to filter the group - if 3 departments charge for one item and 2 charge the same amount but one charges a different amount I need to show all 3 departments and what they charge. However within groups - if 3 departments charge for the same item and they all charge the same amount then I would not want those on the report. In a nutshell I am trying to identify departments that charge a different amount for the same items. If everyone is charging the same for items I do not need to hightlight those - am I making sense? Another example might be a hospital setting - the icu charges $5 for an aspirin - so does the emergency room but the same aspirin costs $7 in the NICU. I need to hightlight those 3 departments on the report - to show that pricing needs to be reviewed at all 3 of those departments. There are other departments in the hospital but they don't charge or maybe don't dispense aspirin - I don't need to show any of those on the report. Any advice you might have is sincerely appreciated. This has been a long term project and I really need to figure this out. Thank you!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    $7 for aspirin! No wonder health care in the US is a wreck!

    This is tricky because the criteria to select records depends on data in other records of same table. I think what is needed is a count of the unique prices for each Item. If the price count exceeds 1 then return all records for the item otherwise return none. Might be accomplished by a subquery and/or DCount domain aggregate function calc. First, create an aggregate (GROUP BY) or DISTINCT query, like:

    SELECT Item, Price FROM tablename GROUP BY Item, Price;
    or
    SELECT DISTINCT Item, Price FROM tablename;

    Then query the table:
    SELECT * FROM tablename WHERE DCount("Price","query","Item='" & [Item] & "'")>1;
    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.

  5. #5
    pellissier is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    13
    returned no records - . I can query to get all the records that I want - the problem comes in when I need to leave out those that don't have a different price. So if Group A has 3 departments charging the same price - I want to drop those. I only want Group B that might have 3 departments but at least 1 of those 3 has a different price than the others. Might this be accomplished through conditional formatting? Can I recolor the departments with at least one different price? Alternately if I make a table of just the fields in question - would that make a difference to how a query was built to find those groups?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, I was editing my post as you replied. Review it again.

    Even conditional formatting would be difficult because the criteria is dependent on other records in same data source.
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-26-2012, 08:31 PM
  2. Conditional Formatting in a Sub Report
    By cduveE6 in forum Reports
    Replies: 2
    Last Post: 04-03-2012, 01:41 AM
  3. #Name? Conditional Formatting Report
    By stosh59 in forum Reports
    Replies: 5
    Last Post: 01-10-2011, 02:40 PM
  4. Conditional formatting in crosstab report
    By squirrelmaster in forum Reports
    Replies: 0
    Last Post: 06-24-2010, 06:31 AM
  5. Conditional formatting in report
    By RickM in forum Access
    Replies: 1
    Last Post: 09-10-2009, 06:21 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