Junction Table Help

[blkane]

Hi, I'm a new-ish access user. I'm having trouble with designing where the junction tables are in many-many relationships. I am building a database that will take raw volume, calc revenue and generate invoices. There is no order entry. Volume is delivered in a simple table at the end of each month which I can import into the database. These are my critical elements.

Customers
Customer unit price (differs for each customer)
Volume

The thing that is tripping me up is price. If I had a consistent price across all customers, it would be simpler. How do I relate customer-volume-price when price varies by customer?

I appreciate any input on this.

Thank you,

[June7]

The price is not included with the raw data? Are there multiple products involved? The data has a product ID?

You can either manually enter the price info if it is not in the raw data, or maintain a table of customer prices that can be joined to the raw data.

[blkane]

There are multiple products. Each customer can have 1 or more products. Each customer has their own prices per product.

I had thought about adding price into the raw data table as an option. It is the second option of adding a price table that I am trying to understand in my question. I don't know how to structure the required tables.

The reason I was focusing on a table is that prices change annually. It seemed easier to drop in a new table rather than restructure the data load.

Thank you

[June7]

Options are to save the price or an ID for the price record.

What is structure of pricing table? Is there a record for each customer for each product?

How do you want to edit the pricing table? Do you want to change the price in each record or do you want to enter new records for the new pricing?

If you want to edit the existing records then MUST save price with raw data. If you add new records then option is to save the price or ID.

How to populate a price/ID field in raw data table is another matter.

If raw data and pricing tables both have customer and product ID's, then join the two tables.

[jony33]

There is no order entry. Volume is delivered in a simple table at the end of each month which I can import into the database. These are my critical elements.

_________________
Mohsin

[June7]

Not sure what you are asking for. If you want to calculate unit price of a product across all customers, I guess you will have to calculate an average.