Matrix using data from other tables for field names

[crystaleyes82]

Hello. I am creating a database to store and calcuate particular costs and numbers. I have already built the basic tables that I need, but I now need to build a matrix using information from these other tables.

I have two tables, one that lists products with various levels of pricing and one that lists locations and groups them into regions. The matrix I am trying to build stores the amount of products needed per location. I am trying to use the locations from the second table across the top and the products along the side. I don't know if that is possible since the locations would become field names, but if a new location is added then a new field would need to be created.

When entering data into the matrix via a form, I would like the user to be able to sort to list only the product grouping that they want to enter information into. But to also be able to sort according to region.

Does anyone know if this is possible in Access? I know that it will most likely require some VBA, but I'm stumped on the best way to go about setting up the table and the form.

Any help/suggestions, as well being pointed in the right direction, would be greatly appreciated.

Jenn

 

[PHV]

Have a look at CrossTab query and/or PivotTable.
But, this is for reporting, not for data entry.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

one that lists products with various levels of pricing

Does this mean your table is:

ProductID ProductName PriceLevel1 PriceLevel2 PriceLevel3 etc....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[crystaleyes82]

Yes it does.

 

[PHV]

So, have a look here:

http://r937.com/relational.html

Or use Excel ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

you may want to review The Fundamentals of Relational Database Design. You are "committing spreadsheet" with your table design. What happens when a new price level is created? You are going to have to change the table AND every query, report or other component to include the new field.

Since each product can have many prices it should be like:

tblProducts
ProductID (PK)
ProductName
etc

tblProductPriceLevels
ProductID (FK - PK)
LevelID (PK)
Price

This way if a new price level is created, you just add a new record.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[crystaleyes82]

Except that there is no possibility for a new price level. There is a set number of price levels and that absolutely cannot change.

What I am trying to create is a matrix of the quantity of each product for each location. This can very easily be done in Excel. But once the locations are separated into their respective regions and then calculations are done using the products prices and quantities, it ends up being a huge mess of a workbook. Not to mention having to add a new product and keep it in the correct category, as well as a new location, is very time consuming.

I want to be able to easily store all of my data and calculate the data. Then as reports are needed using different criteria, be able to call up those numbers. As well as add new products and locations without having to almost recreate the spreadsheets and formulas.

If I have posted this in the wrong forum, please let me know.

 

[lespaul]

Except that there is no possibility for a new price level. There is a set number of price levels and that absolutely cannot change.

SOMEDAY it will change, but in any case, it doesn't matter, the best thing to do is follow the rules of database normalization.

If your tables are normalized & structured correctly, getting the data out is easy.

If your tables are not normalized and "commit spreadsheet" extracting the data is difficult.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual