I need some help please because I can't wrap my head around this to get the best result. I'm a heavy user of FM with applications I've put together for my own department but now the head office wants me to expand my database to be used in multiple locations. We have 4 offices that use different lists of sales reps with ID #s, customers with ID#s and job #s.
Problem is that the same batch of ID#s is used in each office, some reps work out of multiple offices and some customers are serviced by more than one office and their ID#s are not the same in each office. For example in Office A, Sales Rep Joe is # 22 and Customer Orange is # 1523 but in Office B Joe's # is 49 and Orange's # is 8265.
To complicate matters more, the 4 offices' records do not intermingle but each office does need to be able to search in the other's information. One database is logical so we can all search in one spot.
Do I need to make separate tables for each batch of Sales Reps, Customers and Job numbers at each office? In this scenario I would have 4 Sales Reps Lists and 4 Customer Lists etc. Or do I make one table for all sales reps with a multiple entries with office identifier letter field for ones that work in other offices?
Any advice would be greatly appreciated on best way to expand my application!