To Relate or Not to Relate

[accessnut]

I have a table with only salespeople in it. (I use it for a "look up list" for another table. I also have a table with products in it (also used as a "look up list" in the other table).I need reports for each sales rep per each product. Should I establish a relationship between any of these 3 tables? Or would that just skew my report up?

 

[Rolliee]

Is there a common field? And what do you use that common field for? Has to be answered.

rollie@bwsys.net

 

[tviman]

Unless you use data from the 'salesman' table and the "product' table to create your reports, or to update other tables where data from both tables is needed then it would serve no useful purpose to relate the tables. Besides, if these tables are 'look-up' tables then they shouldn't have any common field to set a relation to - unless you have one salesman for each product or a group of products and no other salesman could sell products not in his/her group. There's always a better way...

 

[accessnut]

All 8 salespeople can sale all products to only their customers or new customer in their territory. We have about four of these type of products every month. This was being done in excell which can be more difficult to use. I have set it up as follows...
tbl-salesperson (lookup only)
tbl-Customers (lookup only)
tbl-Products (lookup only)
all of these are used in
tbl-Special(salesperson,customer,product,size,color,price,color price)
I have used queries to pull each salespersons customers sold per each product which just for right now stands at 76 queires just for the rest of this year. I have based a report on each one in order to use a command button for each salesperson on a form. It all seems too bulky to me. Is there someway to use relationships to trim it down. I don't have alot of problem with anything else. Just this blows my mind.

 

[tviman]

You can relate these four tables for report purposes by using primary & foreign keys but I don't think you're going to gain much for the effort. As long as each record of your look-up tables are unique within the table then I wouldn't waste the time setting up relations. Now, if you are performing other functions such as updates to tbl-Special through a sales table then you would want to relate the appropriate tables.

There's always a better way...