Database design question

[Jeffh]

I have three different parameters I need to query. The first is a
classification such as 7.75G, 8.25A, 6.50E, etc. The second parameter is a
size range for each of the previously mentioned classifications, 1000-1250,
1251 - 1500, and 1501 - 1750. The next item is cost based on those ranges,
$150 if the size falls between 1000-1250, $200 if the size falls between
1251-1500, etc. I need to have the users enter a classification and then a
size and have Access calculate the cost. For example, if the user enters a
class of 7.75G and a size of 1157, Access should come up with a cost of
$173,550. Any suggestions on how I can accomplish this would be greatly
appreciated.

 

[mnature]

Jeff, you need to design your database before you can query it. What sorts
of data do you have? Are you setting this up on a web page, or on a local
network? Are you trying to do inventory control, as well as allowing easy
lookup of items?

You may want to look at the sample database that comes with Access
(Northwind), and see how they set up looking at different parameters.

 

[Jeffh]

I am desiging a cost table for construction costs. Each house built is
classified a type 6.50G, 7.75E, 8.25A, etc. Each classification has a square
foot range and cost associated with it. Here is a sample of the data...

Class sq ft cost
7.75G 1200 $175.00

My main question is this... Each class can have several ranges of square
footages wtih different costs. Here is an expanded sample.

Class sq ft range cost
7.75G 1000-1250 $175.00
7.75G 1251-1500 $180.00
7.75G 1501-1750 $190.00

I want the user to enter the classification and a square foot amount and
have access calculate the final cost. I'm just not sure how to set up the
tables to allow for the square foot range and still be able to perform a math
calculation on the sq ft entered by the user. I hope this makes at least
some sense.... thanks for your help.

 

[Pat Hartman\(MVP\)]

You can't effectively work with the range as it is. You need to separate it
into two fields for the start and end. Then you can use simple queries to
find the correct price:

Select tblA.Class, tblA.Size * tblB.Cost = TotalAmt
From tblA Left Join tblB On tblA.Class = tblB.Class
Where tblA.Size >= tblB.BeginRange and tblA.Size <= tblB.EndRange;

 

[Jeffh]

Thank you for the information. I understand the need to separate out the size
ranges. I am still having trouble getting the query to return any results. If
at all possible, based on the sample data provided in an earlier posting, can
you show me how the tables and relationships would be set up? When I set it
up, the form always asks for a total amount and never calculates the true
cost based on criteria entered. I know I've goofed up the query somewhere but
cannot figure out exactly where. I think I may be entering the query in the
wrong spot and associating it in the wrong area. I very much appreciate your
input.

 

[Pat Hartman\(MVP\)]

If you are having problems with the query, it would be easier for us if you
pasted the SQL here so we can see it. The query can be created with the QBE
and saved as a querydef or you can create it with the QBE from within the
form's RecordSource and it will be stored as a text string in the
RecordSource. You'll need to add whatever additional fields that are
necessary to populate your form. The calculated cost is not saved in the
table. It only shows in the query and form.

 

[Jeffh]

Here is, hopefully a clearer description of what I am trying to accomplish..

TableA
Class
Size

TableB
Cost
Class
SizeBeginRange
SizeEndRange

Query: Built with help from query wizard

SELECT TableA.Class, TableA.Size*TableB.Cost=TotalAmt AS Expr1
FROM TableA LEFT JOIN TableB ON TableA.Class = TableB.Class
WHERE (((TableA.Size)>=[TableB].[SizeBeginRange] And
(TableA.Size)<=[TableB].[SizeEndRange]));

Form has this query tied to a 'Total Amount' field

In a nutshell, I want the user to enter a class, say 7.75G and a square foot
amount, say 752 and have Access calculate the total cost. Sample data is
given below. A user entering a class of 7.75G and 752 square feet would have
a cost returned of $173,486.40.

Class SizeBeginRange SizeEndRange Cost
7.75E 651 750 $252.20
7.75E 751 850 $241.60
7.75G 651 750 $240.70
7.75G 751 850 $230.70
7.75A 651 750 $229.20
7.75A 751 850 $219.70

7.50E 651 750 $232.40
7.50E 751 850 $222.80
7.50G 651 750 $221.90
7.50G 751 850 $212.70
7.50A 651 750 $211.30
7.50A 751 850 $202.60