Query Issue

[mtnairco]

Hello,

I have a small company that uses a price estimating program that I wrote in Excel that I would like to rework in Access. The program uses a number of variables to come up with sizing dimensions for my product. The variables used to come up with the sizes of each part of my product are: ProductType, Connection, Support, and MatType. All four variables are entered by the user to get the sizes of each part of my product using an array. In Excel all the possible dimensions for each part of my product are listed in a large spreadsheet that the array references. The array looks like this:
=SUM((ProdType=colProdTypes)*(Connection=colConnec tion)*(Support=colSupport )*(MatType=colMatType)*colxx)

I'm having difficulty converting this to Access. I've tried using queries with no luck so far. Can anyone help me with this.

Thank you in advance for your assistance!!

[ajetrumpet]

ideally here, what you would have is a table for products and a child table for dimensions that are related to each product.

After that, all you do is query out the 2 tables using an INNER JOIN and parameter boxes for your 4 dimensions. That's about it.

[mtnairco]

Thank you for your response. I don't believe I gave you enough infomation. The user first selects a ProdType. There are 15 Prodtypes to choose from (so far). Then the user selects a Connection for that ProdType. There are 11 Connection types to choose from for each ProdType. Then the user selects a Support for the ProdType. There are 6 Support types to choose from for each ProdType. And finally, the user selects a MatType (material type). There are 10 different material types for each type of Support. Once all this info is selected by the user, the array in Excel can determine the different sizes for each part of my products -up to 9 different sizes needed for fabrication. Given this additional information how do suggest I proceed?
Thank you for your help!

[mtnairco]

Thank you for your responses. Here is some additional info. I can manage the sizing calculation, but what I having trouble with is retrieving the size constant. This is basically what the Excel spreadsheet that I'm using looks like:
ProdType Connection Support Material A1 A2
Wid1 Z 300A ITA .106 .241
Wid1 Z 300A RUS .098 .455
Wid1 W 500A CHI .119 .765
Wid1 V 500B US .324 .438
Wid1 W 500B GER .873 .640
The User inputs the info in the first four columns. The array locates the size constant in column A1 and A2 (there are actually 20 size constant columns). I have table set up for all 4 user input columns as well as 1 table with all columns (the four user input columns and the 20 size constant columns). I'm having trouble retrieving the appropriate size constant. Any suggestions?
Thank you in advance for your assistance!

[mtnairco]

I would like to add some additional information that may be useful. Regarding my last thread, the information about the 20 size constant columns was information that is not relevant to the issue at hand. I inadvertantly muddied the waters with this info. I was just trying to convey how big the Excel spreadsheet is - again not relevant to retrieving the constants. How the constants are calculated is also not important as they are industry standards, specific to my industry. These standard are all in a table and I just need a way to look them up based on the four variables that the user inputs. Where the user input comes from is not of importance either in that the four variables help construct the product one of our customers may want. I'm looking for a way to retrieve only one size constant at a time. If I may, let ask a question that references the info in my last thread. Lets assume the user enters the following info based on a customer's request. This info is entered by the user in Access on a form bound to a table:
ProdType = Wid1
Connection = Z
Support = 300A
Material = RUS
I'm looking for just the constant in column A1 that corresponds to the four variables that the user has input. The correct answer is .098. Please notice that if the user had input the same info except used ITA for the Material the correct answer would then be .106. Can the Dlookup feature accomplish this task or is there a better way to do this.
Thank you very much for all your help!!