Group By (Access2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

… 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…

Reply | Quote

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

Reply | Quote

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.
:- )

Reply | Quote

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

Parent	Member	Shop	CombinedSales
Group By	First	First	First

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.

Reply | Quote

Thank you! Thank you! Thank you!

THANK YOU!

Reply | Quote

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

Thanks again.
T

Reply | Quote