Solved: Problem w/ Query

[mtnco80517]

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=colConnection)*(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!!

[HiTechCoach]

It should be possible to do in Access. I do similar things in Access now.

The key to success will be in setting up the tables properly. This is a lot different that anything in Excel.

Would it be possible for you to post a sample data with the tables and some sample data? Be sure to replace any confidential data with sample data.

[mtnco80517]

Thank you for your responses. 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!

[HiTechCoach]

"20 size constant columns" us a huge read flag for table design issues. This may be what is causing this to be so difficult.

Access is a relational database and Excel is not. This means that almost nothing you did in Excel should be done the same way in Access. When I help clients convert from Excel to Access I do not look at anything in the Excel spreadsheet. There really is very little, normally nothing, that apples to the design of a relational database. I only look at the Excel file when it is time to extract the data. The format does not really matter since it will be converted into a normalized relational database.

[mtnco80517]

Not all 20 are used for each item. I merely stated there were 20 columns to indicate the size of the spreadsheet. Sorry, that info is not relevant to the problem at hand. I simply showed how the spreadsheet is set up to show how the array does retrieve the correct size constant. Given the information in my last thread, how would I setup a query that would give me a value for A2 of .455 if the user enters the following info:
ProdType = Wid1
Connection = Z
Support = 300A
Material = RUS
Please note that I don't want Access to return a value of .241 which has the same first three values for ProdType, Connection, and Support but a different Material value of ITA. Any suggestions? If I entered all this info in Access in VBA code using 'If...And...Then' statements the code would be about two thousand lines long. Do you think this would slow the program down significantly? It seems this may be the way to go. No one seems to know how to do this in a table/query format. Maybe it's not possible to retrive the info using tables/queries. What do you think?
Thank you for your assistance.

[HiTechCoach]

Not all 20 are used for each item. I merely stated there were 20 columns to indicate the size of the spreadsheet. Sorry, that info is not relevant to the problem at hand.

I do think it is very relevant. You need to convert this method to using properly desired tables. This will be a big change in methofology that does not exist in spreadsheets.



... If I entered all this info in Access in VBA code using 'If...And...Then' statements the code would be about two thousand lines long.

I would not recommend that. That is really thinking like a spreadsheet and not like a relational database where data is only stored in tables not code.

The database (Access) way is to use tables to store the data. You look up the desired records to get the values you need.

Before you can create a query to get eh data you will first need to have tables that store the data. Have you got that part done?

[mtnco80517]

Thank you for your response. 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. Each user input field has its own table. Where the user input comes from is not of importance either in that the four variables are used to help construct the product my customers want. I'm looking for a way to retrieve only the constants for each product. Each time a user enters the four variables I will need a way to retrieve only 3 or 4 of the size constants. If I may, let me ask you a question that references the info in my previous threads. Lets assume the user enters the following info based on a customer's request. This info is entered in Access on a form bound to a table:
ProdType = Wid1
Connection = Z
Support = 300A
Material = RUS
To start, 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 you suggested accomplish this task or is there a better way to do this.
Thank you very much for all your help!!