CODEWITHSUNDEEP
ms-access-2010
Sisu
Sisu

Reputation: 1

Access - Create/Update table based on primary tables selections

I have the following database structure in MS Access:

[The following tables only have 'ID' and 'Product', 'Retailer' etc.]

Now, every product can be used in one or many places (defined by 'Usage'), therefore the 'Usage' column in the 'Products' table allows multiple choices.

Every product has it's own multiplier, defined in the 'Multiplier' column of the 'Products' table.

Now to the actual problem:

I want to create a new table for every record in the 'Usage' table. In those tables I want a list of the products that are selected to be included in this group (as defined by the 'Usage' column in the 'Products' table - Observe the multiple choice). In this new table I want to be able to enter a multiplier that will be specific to where a certain product is used.

To recap: I want to be able to make a product list where every product has it's own multiplier and a secondary multiplier based on where the product is used. For easy accessing I also want to have tables where I can check what products are used at a specific place.

I hope you can get any sense of what I'm trying to achieve.

Thanks in advance for any help!

Upvotes: 0

Views: 485

Answers (1)

Sisu
Sisu

Reputation: 1

I think I managed to solve this puzzle...

I went back to the drawing board and reconstructed the database as follows:

Table: ProductBrand - (ID), BrandName
Table: ProductGroup - (ID), Name
Table: Supplier - (ID), Supplier_Name
Table: Units - (ID), Unit_Name
Table: UsageArea: (ID), UsageAreaName
Table: Products: (ID), ProductGroup.Name, Supplier.Name, ProductBrand.Name, Product_Name, Product_Comments, Product_BasisForCalculation, Product_Price, Units.Name, UsageArea.Name

Table: Multipliers - (Product.Name), (UsageArea.Name), Mulipliers.Multiplier

Column names within parenthesis are the primary keys (observe that "Multipliers" has a primary key made of two columns).

To fetch the information I need, I made Queries for every Usage Area. This queries the Supplier, Brand, Name, Comment, Calculation Basis, Price, Units and the Usage Area specific multiplier for that specific product.

If anyone has improvement ideas, I'm all ears :)

Upvotes: 0

Related Questions