How to add a new item from one table to all customer table?

[Minty]

Hi Jenny,

It's not overly complicated as far as the data goes.
The premise is that you have a Schedule Table which lists your 300 price Schedules- A ScheduleID, probably a description , some other "Stuff" that doesn't change, an Active/Inactive flag, setup date ?
You customers all have a account number unique PK of some description. (I Hope!)

Your price table layout will then be something like (Only showing 12 here but you'll get the idea) ;

PriceID	AccountNo	ScheduleID	GBPBase	LastPriceDate	Active
1	0	1	45	19-Jan-16	TRUE
2	0	2	25	19-Jan-16	TRUE
3	0	3	40	19-Jan-16	TRUE
4	0	4	40	19-Jan-16	TRUE
5	0	5	65	19-Jan-16	TRUE
6	0	6	185	19-Jan-16	TRUE
7	0	7	265	19-Jan-16	TRUE
8	0	8	50	19-Jan-16	TRUE
9	0	9	85	19-Jan-16	TRUE
10	0	10	295	19-Jan-16	TRUE
11	0	11	30	19-Jan-16	TRUE
12	0	12	45	19-Jan-16	TRUE

You initially set your default pricing with an account no of 0 - this shouldn't match anyone's real account number.
Now lets add a new price for account 243 just for for Schedule ID's 10 ,11, 12. They are on default for the other schedules.

PriceID	AccountNo	ScheduleID	GBPBase	LastPriceDate	Active
13	243	10	340	21-Mar-19	TRUE
14	243	11	35	21-Mar-19	TRUE
15	243	12	55	21-Mar-19	TRUE

Instead of adding 12 records we only added the 3 exceptions.



When you query the tables you need to get a bit clever with a union of default pricing and account specific pricing, but you are only maintaining the exceptions.
You can also add further dated records and maintain a history of previous pricing by setting the older records to inactive and/or switching off the exception pricing for accounts revert them to default pricing.

[JennyL]

Bing: Access compound index

http://www.geeksengine.com/article/c...ex-access.html

Thanks! This works. Only problem is that with the error handling added to the code - user won't know that there is an attempt for adding a duplicate fee. I guess I just have to train users or lock down this rights to only allow certain managers to do the "all fee code to existing schedules".

Thank you so much!