Retrieve Data from Table Matching Two Criteria

[michaelb-201]

I'm quite new to Access so sorry if this is the dumbest thing you have ever seen.


I have customers listed in a table ("customers") and product groups listed in a table ("family group"). These two tables are related as two fields into another table ("multipliers") which lists rates at which customers are charged for a product group (different customers get different rates, rates differ between product groups, rate is a multiple of cost). I have items, their product group, and cost (from which price will be derive using the multiple) listed in table "items with cost".


First, I want to generate a unique record for each item per customer is table "prices" (essentially all items from "items with cost" multiplied by "customers"). I believe this is generally poor database design but I need to be able to individually modify a customer's price regardless of the multiple or other customers' price for that item. I believe the best way to combine these is with a macro. Either way, this seems like a popular question online so I'm sure I could get something working.

Second, I want to retrieve the rate from table "multipliers" given a specific customer and specific item (which has a specific product group) in table "prices" to generate a price from cost. I don't know even the right words to describe this question so I had a hard time finding anything like this on google. I think the way to do this may be with a query?


Here is my relationship tab which will hopefully clarify what I am trying to do.