Relationship for multi-variable discount table?

gmworrall

Registered User.
Local time
Today, 00:54
Joined
Jun 19, 2012
Messages
18
Hi!

I'm designing a product database.

1. Feed - this is linked to a CSV so can't change anything really
2. Products
3. Products_Feed - links 1 and 2. Multiple suppliers for stock so this basically says OurCode | SupplierCode

Then there are tables for Category, Manufacturer and Supplier. Related to the ones above.

I've got a Discounts table, where you choose Category, Manufacturer and Supplier and assign a discount percentage. So only products that match all 3 have the percentage. Does that make sense so far?

I'm stumped on how to get this last table integrated properly! I don't mind looking myself, but not even sure what to look for...
 
You need to use an outer join for this last table since it is only present for some rows in the other table. When you create the join, Access gives you three choices. Equal, which is the default and will only show a row when there is a match in both ables. Left, which shows all rows in the left table regardless of whether or not any rows in the right match and Right which shows all rows in the right table regardless of whether or not any rows in the left table match. Choose left or right depending on which will give the results you need.
 
That worked great, though then I had a problem with 'ambiguous queries'. *Think* I've solved that now - certainly seems to be giving the results I want.

Thanks Pat!
 

Users who are viewing this thread

Back
Top Bottom