Customer Groupings

[rjbutler77]

I have developed a pricing database, which allows me to look at which customers get what pricing and where geographically.

I have a customer Table which identifies by account number and includes information relating to country, region and sub-region and standard pricing levels.

As well as standard pricing terms we also offer Special one- off prices, which can be linked at account level, country, region, sub-region level or world wide.

I have set up a special pricing register table which allows me to link Special Price Quotation to Customer. My problem, however, is that I want the user to be able to go to the input form and either select an individual account or a grouping of accounts, such as country (for example UK would link it to all accounts in the customer master table wich have the UK against it)

Sorry if I have rambled on. Your help would be grately appreciated.

Rob

 

[neffs]

I'm pretty sure you would have to use a subform. If you can give me a little more information about your datafiles and relationships I think I can help you a lot.

 

[rjbutler77]

Thanks for the reply

The tables in my database (among others) are as follows:

[Customer Master]
>Account Number (Primary Key)
>Customer name
>Country
>Pricing information

[Country Region Table]
>Country (Primary Key)
>Sub-Region
>Region

[Special Pricing Register]
>SP Number [Primary Key]
>Account Number
*I have a form where the user adds a new Special Price in the Register. Currently they can only link one Account number to the Special Price at a time. What I would like to be able to do is have the ability to select either a single account number, or a grouping of account numbers specific to the Sub-Region or region that they select (preferably in a combo box). This grouping should be defined by the country associated (and therefore Sub-region and region) allocated in the customer master.

Your help would be grately appreciated.

Please let me know if you need any further info or sample database.

 

[neffs]

Is each account number related to 1 country, 1 Sub-Region, and 1 Region? Also, are the regions divided into subregions? The way you have your table set up it seems like the region is a division of the Sub-Region.

 

[rjbutler77]

Yes each account has one country, sub-region and Region
For example:

Account #: AC1 - Italy distributor

[Country Region Table]
>Country: Italy
>Sub-Region: Southern Europe
>Region: Southern Europe Africa and Middle East (SEAME)

 

[neffs]

I am attaching an Access 97 Database that I made up based on the info you gave me. It is the best way for me to simulate your problem. I have created datafiles, queries, and some forms that I think will help you or at least give you an idea of where to go. The attached database is rough according to my usual standards but I haven't had time to clean it up. The combo lists on Special Pricing Form need some work to avoid the duplications. There needs to be some better coding on merging the selected accounts to make it more user friendly but you can work on that. I can help if you want. Bring up the Special Pricing Form. You can put in Special Pricing Info and then select an account. Or you can go to one of the 3 combo boxes which bring up country, region, or sub-region. When you make a selection and click the command button below the box, a form will appear with all the corresponding accounts. You can then select the first two columns, copy them to your clipboard, open your Special Pricing File, and Paste-append them into your file. Like I said, it is a little crude but you can refine it. I had to index the special pricing file using the SP Number and Account number to keep it a primary key. If I didn't do that, it wouldn't allow me to add more than one account to the SP number. Hope this helps. Let me know if you need anything else. I'll send you an email with the file attached if since I guess I can't attach mdb files to this reply.