Results 1 to 10 of 10
  1. #1
    panda2012 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    6

    Summing Data Based on Different Criteria in Field and Matching Critera in Another


    I have some patient prescription data and I need to determine how much revenue is being generated by combinations of products. For example we might have one patient that has ordered 2 different medications under the same
    "order number"; these would be listed as two rows of data. Each row would have the same order number but in a "Type" field would have the particular kind of medication listed. What I would like to do is to identify all data containing two different medications as determined by the "type" field with the same order number and then sum the "revenue" field. This would give the total revenue for the combination of those two medications with the same order number. There are other combinations of medications of up to four different "type" with the same order number, but if someone would be able to help me out with the syntax for creating the query for two combinations I think I could probably take it from there. Thanks everyone in advance. If need be I can post up a file.

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Can you post some table dummy data & the result that you want out of it?

    Thanks

  3. #3
    panda2012 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    6
    This isn't the best sample data but hopefully it'll give you a better idea of what I'm attempting to do. It is an excel file but I'd like to do this in Access. The two rows of highlighted blue data I wish to aggregate based off of the criteria that there are 2 total items(rows) with the same order number but containing two different drugs. The end result should combine them into one order number with a sum of the total and quantity columns. This is actually a combinatorics problem in that we have several different combinations of drugs possible across different numbers of items within the order number, but if someone can help me out with aggregating of one possible combination I think I can take it from there. This is probably really simple to do and I'm just dumb... thanks for the help.
    Attached Files Attached Files

  4. #4
    panda2012 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    6
    To be a little more specific, I would first like for the combinations of two medications to be aggregated into a single new row as described above, and then I will later sum the total of these new "combo" products. Ideally, which will be happening in the near future, the various combinations of medications will be referred to with a single identifying name, but for now I am being asked to identify the revenue mix based off of various combinations of medications contained within the same order number.

  5. #5
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    One way perhaps could be what you have yourself outlined above :


    1)
    An aggregate select query with
    grouping on the Order & the Item
    and
    Sum on the Quantity & the Total
    and
    Having the Item criteria as the Item1.

    2)
    An aggregate select query with
    grouping on the Order & the Item
    and
    Sum on the Quantity & the Total
    and
    Having the Item criteria as the Item2.

    3) Join the 2 queries with an INNER JOIN & add the TOTALQTY & TOTALTotal to get the result.


    4) Alternatively, also check if below ( a different way ) gives some guidelines :

    Code:
    SELECT 
        a.Order, 
        Sum(myTable.QTY) AS SumOfQTY, 
        Sum(myTable.TOTAL) AS SumOfTOTAL
    FROM 
        (
            SELECT 
                b.Order, 
                Count(b.[DRUG/ITEM]) AS [CountOfDRUG/ITEM]
            FROM 
                (
                    SELECT DISTINCT 
                        myTable.Order, 
                        myTable.[DRUG/ITEM]
                    FROM 
                        myTable
                    WHERE 
                        (
                            (
                                (myTable.[DRUG/ITEM])="betamethasone" 
                                Or 
                                (myTable.[DRUG/ITEM])="mometasone"
                            )
                        )
                    ORDER BY 
                        myTable.Order
                )
                AS b
            GROUP BY 
                b.Order
            HAVING 
                (((Count(b.[DRUG/ITEM]))=2))
        )
        AS a 
        INNER JOIN 
        myTable 
        ON 
        a.Order = myTable.Order
    WHERE 
        (
            (
                (myTable.[DRUG/ITEM])="betamethasone" 
                Or 
                (myTable.[DRUG/ITEM])="mometasone"
            )
        )
    GROUP BY 
        a.Order;

    Thanks

  6. #6
    panda2012 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    6
    Thank you Recyan for your help on this. I'm taking a look at it now.

  7. #7
    panda2012 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    6
    Do I need to create another table named "a" for this query to work? Sorry, I'm relatively new to Access..

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    No, a & b are aliases for the sub-queries. Just replace"myTable" with your table name in the code.
    Frankly speaking, I have lost touch with what we were doing due the time gap in between.

    My suggestion : Post your questions only when you are in a position to respond fast to replies given (excepting weekends ). It will get your problems solved fast.

    Thanks

  9. #9
    panda2012 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    6
    Sorry about that and thanks for your help on this. I was busy with another excel VBA project and this one sorta took the back burner. I may have a few more questions after I look at the query, but thanks again for your help.

  10. #10
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    No sorry needed.
    Quote Originally Posted by panda2012 View Post
    I may have a few more questions after I look at the query, but thanks again for your help.
    You are most welcome.

    Thanks

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

Similar Threads

  1. Browse and Open Folder Based on Matching Form Field
    By Tomfernandez1 in forum Access
    Replies: 11
    Last Post: 02-26-2013, 01:04 PM
  2. data field in query twice based on criteria
    By sandyg in forum Queries
    Replies: 1
    Last Post: 09-29-2011, 07:47 AM
  3. Replies: 1
    Last Post: 08-11-2011, 11:33 AM
  4. Matching based on criteria from multiple tables
    By Jonpro03 in forum Database Design
    Replies: 13
    Last Post: 08-04-2011, 10:29 AM
  5. Replies: 4
    Last Post: 01-19-2010, 05:36 AM

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