Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Downers Grove, Illinois, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Group By (Access2000)

    There must be a better way! I'm intermediate in Access so bear with me and any help you can offer will greatly be appreciated.

    Our customers have MEMBER #'s and if they have multiple shops, they also have a PARENT #. If any of the shops have parent #'s I've combined their sales in order to get one dollar amount for each group. Then, if there is a parent #, I'm set it up so that the same Dollar amount is posted for each of the shops in the member/parent group whether they buy or not. That way it doesn't matter which shop is pulled up, the same dollar amount is listed. example:

    BEFORE QUERY
    Member Parent Spending
    5555 120 $1,000
    5554 120 500
    5553 120 0
    AFTER QUERY
    Member Parent Spending
    5555 120 $1,500
    5554 120 1,500
    5553 120 1,000

    Of course the powers that be, loved that, now they would like to have an additional report but this time they only want to see one of the shops representing the group. I haven't been able to do a new group by because each shop has a different MEMBER number.

    There must be another step that can be done to take care of this. I'm currently working in excel manually deleting all but one of the shops in the Member/Parent group. Not fun as we have 20,000 members.

    Thank you in advance,
    Theresa

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Group By (Access2000)

    Let me see if I have this straight.

    Each customer is identified with a Member Number.
    A Member Number can have one or many shops.
    A Parent Number is assigned to a Member Number if the Member has many shops.

    Why not set the logic as follows:

    Create a Customer Table that strores a unique id, Customer Name and Member Number.
    Then create a second shop table table that links to the Customer table as a one to many such that a member can have one or many shops. Sales data would be stored at this level as well.

    To get sales by shop, run a query at the shop level. To get total sales by Member, run the query by Member Number.

    Suggest that the sales data contain data data so you can query by month or year. If you want to expand even more, put in sales data so you can include trends in the queries as well.

    Hope this helps.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Downers Grove, Illinois, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group By (Access2000)

    Gosh, what a busy month! It's been so long since I've had a chance to get back to this. Gary, thank you for your reply. There is a member number for each shop so this wouldn't work.

    Each customer is identified with a Member Number.
    One Member Number per shop.
    A Parent Number is assigned to a Member if it is a part of a group.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Group By (Access2000)

    t would certainly make it clearer to me if you would give us a picture of your tables and how you are querying them.
    Your example is not clear to me. I presume that 120 is the Parent number. Why would the query show $1,000 for member 5553 and $1,500 for member 5554?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Group By (Access2000)

    ... actually, if you think about it, the original logic just may work as a Member Number can have one or many shops. I agree with Patt on this one, can you show us an example of your tables or post the database.

    Based on your latest reply there is a disconnect between the Parent Number, Member, and group. I would suggest trying to re-define the relationships. How do you know if a member is part of a group? Now I am a little confused as well... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Downers Grove, Illinois, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group By (Access2000)

    Thank you for your patience, I'm a rookie here. Each shop has a member number and if it's part of a group there is also a parent number listed. In access, I've combined the sales to show the same total for each shop in the group. Combined Sales: IIf([Rank]![PARENT]>0,[QryParentYES_CombineSales]![SumOfSumOfEXTENDEDPR],Null)

    This is an example of what I have to do in excel because I can't figure it out in access. I'm manually going through 20,000 records and deleting all but one shop in each group.

    MEMBER SHOP Combined Sales PARENT
    59-5579AA FLOWERS BY CINDY $59,958.84 82
    09-0118AA BOCA FLOWER SHOP $59,958.84 82
    09-0910AA BUNING THE FLORIST $59,958.84 82
    09-4318AA BUNING THE FLORIST $59,958.84 82
    09-3146AA EXOTIC GARDENS $59,958.84 82

    They only want to see one shop for each group. They don't care which shop it is either. I wasn't able to do a Group By because they all have different member numbers and as in this example, different shop names.

    There must be a better way but go easy on me, I'm pretty new at this.
    :- )

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Group By (Access2000)

    You can create a Totals query with the Totals options set as follows:

    <table border=1><td>Parent</td><td>Member</td><td>Shop</td><td>CombinedSales</td><td>Group By</td><td>First</td><td>First</td><td>First</td></table>
    This will group by Parent, and return the first Member, Shop and Combined Sales amount Access finds in each group. Since the combined sales amount is the same for each, and you don't care which shop is shown, this should do what you want.

  8. #8
    New Lounger
    Join Date
    Oct 2002
    Location
    Downers Grove, Illinois, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group By (Access2000)

    Thank you! Thank you! Thank you!

    THANK YOU!

  9. #9
    New Lounger
    Join Date
    Oct 2002
    Location
    Downers Grove, Illinois, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group By (Access2000)

    My boss just came by, he told me to thank you too!!!

    Thanks again.
    T

Posting Permissions

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