Query with more than one criteria

G

Guest

I am trying to set up a database where users can enter a classification and
size and have access calculate a total cost. There are multiple
classifications such as 7.50A, 7.50G, 7.50F, etc. There are also size ranges
for each classification and an associated cost for each range. A sample of
the data is below..

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. A user entering a
class of 7.75G and 752 square feet would have
a cost returned of $173,486.40. (752 x 230.70 = 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

Although I am familiar with tables and queries, I would like suggestions or
examples of how best to set this up. I very much appreciate any input
provided. Thanks for your time.
 
G

Guest

Try this --
SELECT jeffh.CLASS, jeffh.SizeBeginRange, jeffh.SizeEndRange, jeffh.COST,
[Enter square footage]*[Cost] AS [Total Cost]
FROM jeffh
WHERE (((jeffh.CLASS)=[Enter class]) AND ((jeffh.SizeBeginRange)<=[Enter
square footage]) AND ((jeffh.SizeEndRange)>=[Enter square footage]));
 
J

Jeff L

I am assuming that you are going to have a form where the user can
select the values they want. You would probably want a combo box for
the Class, which would have the distinct values for the Class. You
would also want a text box for the SquareFt. Finally, you would need a
text box to calculate the Cost.

To calculate cost, put this in you textbox.
=[SquareFt] * Dlookup("Cost","YourTableName", "Class = '" & [Class] &
"' And " & [SquareFt] & " Between SizeBeginRange And SizeEndRange")

Hope that helps!
 
G

Guest

Thanks for the info. I'll try it out and see what happens.

KARL DEWEY said:
Try this --
SELECT jeffh.CLASS, jeffh.SizeBeginRange, jeffh.SizeEndRange, jeffh.COST,
[Enter square footage]*[Cost] AS [Total Cost]
FROM jeffh
WHERE (((jeffh.CLASS)=[Enter class]) AND ((jeffh.SizeBeginRange)<=[Enter
square footage]) AND ((jeffh.SizeEndRange)>=[Enter square footage]));


Jeffh said:
I am trying to set up a database where users can enter a classification and
size and have access calculate a total cost. There are multiple
classifications such as 7.50A, 7.50G, 7.50F, etc. There are also size ranges
for each classification and an associated cost for each range. A sample of
the data is below..

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. A user entering a
class of 7.75G and 752 square feet would have
a cost returned of $173,486.40. (752 x 230.70 = 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

Although I am familiar with tables and queries, I would like suggestions or
examples of how best to set this up. I very much appreciate any input
provided. Thanks for your time.
Click to expand...
Click to expand...
 
G

Guest

Thanks for the info, I'll try it and see what happens.

Jeff L said:
I am assuming that you are going to have a form where the user can
select the values they want. You would probably want a combo box for
the Class, which would have the distinct values for the Class. You
would also want a text box for the SquareFt. Finally, you would need a
text box to calculate the Cost.

To calculate cost, put this in you textbox.
=[SquareFt] * Dlookup("Cost","YourTableName", "Class = '" & [Class] &
"' And " & [SquareFt] & " Between SizeBeginRange And SizeEndRange")

Hope that helps!

I am trying to set up a database where users can enter a classification and
size and have access calculate a total cost. There are multiple
classifications such as 7.50A, 7.50G, 7.50F, etc. There are also size ranges
for each classification and an associated cost for each range. A sample of
the data is below..

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. A user entering a
class of 7.75G and 752 square feet would have
a cost returned of $173,486.40. (752 x 230.70 = 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

Although I am familiar with tables and queries, I would like suggestions or
examples of how best to set this up. I very much appreciate any input
provided. Thanks for your time.
Click to expand...
Click to expand...
 
G

Guest

Well, I ran the query you suggested and it works... sort of. I think I have
the table design all goofed up. Here is what I have so far.

Two tables, Class and CostTable

The Class table has just a primary key and a text field called Class
The CostTable has a primary key called CostID and fields for Class,
SizeBegin, SizeEnd and Cost

I set up a relationship between Class in the Class table and Class in the
CostTable. I am running the following query..

SELECT [Enter Sq Ft]*[Cost] AS [Total Cost], Class.CLASS AS Expr1,
CostTable.SizeBeginRange, CostTable.SizeEndRange
FROM CostTable
WHERE (((CostTable.SizeBeginRange)<=[Enter Size Begin]) AND
((CostTable.SizeEndRange)>=[Enter Sq ft]) AND (([Class].[CLASS])=[Enter
Class]));

When I run the query, it asks for both size and class twice and then gives
the correct results for each class regardless of which I choose. For example,
if I enter a class of 7.50A, the query returns results for 7.50A and 7.50E.
It appears that the query isn't differentiating between classes. What am I
doing wrong??

KARL DEWEY said:
Try this --
SELECT jeffh.CLASS, jeffh.SizeBeginRange, jeffh.SizeEndRange, jeffh.COST,
[Enter square footage]*[Cost] AS [Total Cost]
FROM jeffh
WHERE (((jeffh.CLASS)=[Enter class]) AND ((jeffh.SizeBeginRange)<=[Enter
square footage]) AND ((jeffh.SizeEndRange)>=[Enter square footage]));


Jeffh said:
I am trying to set up a database where users can enter a classification and
size and have access calculate a total cost. There are multiple
classifications such as 7.50A, 7.50G, 7.50F, etc. There are also size ranges
for each classification and an associated cost for each range. A sample of
the data is below..

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. A user entering a
class of 7.75G and 752 square feet would have
a cost returned of $173,486.40. (752 x 230.70 = 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

Although I am familiar with tables and queries, I would like suggestions or
examples of how best to set this up. I very much appreciate any input
provided. Thanks for your time.
Click to expand...
Click to expand...
 
G

Guest

In this query the table Class has no purpose and I omitted it.

SELECT [Enter Sq Ft]*[Cost] AS [Total Cost], CostTable.CLASS,
CostTable.SizeBeginRange, CostTable.SizeEndRange
FROM CostTable
WHERE (((CostTable.CLASS)=[Enter Class]) AND
((CostTable.SizeBeginRange)<=[Enter Sq ft]) AND
((CostTable.SizeEndRange)>=[Enter Sq ft]));


Jeffh said:
Well, I ran the query you suggested and it works... sort of. I think I have
the table design all goofed up. Here is what I have so far.

Two tables, Class and CostTable

The Class table has just a primary key and a text field called Class
The CostTable has a primary key called CostID and fields for Class,
SizeBegin, SizeEnd and Cost

I set up a relationship between Class in the Class table and Class in the
CostTable. I am running the following query..

SELECT [Enter Sq Ft]*[Cost] AS [Total Cost], Class.CLASS AS Expr1,
CostTable.SizeBeginRange, CostTable.SizeEndRange
FROM CostTable
WHERE (((CostTable.SizeBeginRange)<=[Enter Size Begin]) AND
((CostTable.SizeEndRange)>=[Enter Sq ft]) AND (([Class].[CLASS])=[Enter
Class]));

When I run the query, it asks for both size and class twice and then gives
the correct results for each class regardless of which I choose. For example,
if I enter a class of 7.50A, the query returns results for 7.50A and 7.50E.
It appears that the query isn't differentiating between classes. What am I
doing wrong??

KARL DEWEY said:
Try this --
SELECT jeffh.CLASS, jeffh.SizeBeginRange, jeffh.SizeEndRange, jeffh.COST,
[Enter square footage]*[Cost] AS [Total Cost]
FROM jeffh
WHERE (((jeffh.CLASS)=[Enter class]) AND ((jeffh.SizeBeginRange)<=[Enter
square footage]) AND ((jeffh.SizeEndRange)>=[Enter square footage]));


Jeffh said:
I am trying to set up a database where users can enter a classification and
size and have access calculate a total cost. There are multiple
classifications such as 7.50A, 7.50G, 7.50F, etc. There are also size ranges
for each classification and an associated cost for each range. A sample of
the data is below..

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. A user entering a
class of 7.75G and 752 square feet would have
a cost returned of $173,486.40. (752 x 230.70 = 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

Although I am familiar with tables and queries, I would like suggestions or
examples of how best to set this up. I very much appreciate any input
provided. Thanks for your time.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks. That did the trick. I just need to set up the form to accept the user
input and I'm all set. Thanks again for your help.

KARL DEWEY said:
In this query the table Class has no purpose and I omitted it.

SELECT [Enter Sq Ft]*[Cost] AS [Total Cost], CostTable.CLASS,
CostTable.SizeBeginRange, CostTable.SizeEndRange
FROM CostTable
WHERE (((CostTable.CLASS)=[Enter Class]) AND
((CostTable.SizeBeginRange)<=[Enter Sq ft]) AND
((CostTable.SizeEndRange)>=[Enter Sq ft]));


Jeffh said:
Well, I ran the query you suggested and it works... sort of. I think I have
the table design all goofed up. Here is what I have so far.

Two tables, Class and CostTable

The Class table has just a primary key and a text field called Class
The CostTable has a primary key called CostID and fields for Class,
SizeBegin, SizeEnd and Cost

I set up a relationship between Class in the Class table and Class in the
CostTable. I am running the following query..

SELECT [Enter Sq Ft]*[Cost] AS [Total Cost], Class.CLASS AS Expr1,
CostTable.SizeBeginRange, CostTable.SizeEndRange
FROM CostTable
WHERE (((CostTable.SizeBeginRange)<=[Enter Size Begin]) AND
((CostTable.SizeEndRange)>=[Enter Sq ft]) AND (([Class].[CLASS])=[Enter
Class]));

When I run the query, it asks for both size and class twice and then gives
the correct results for each class regardless of which I choose. For example,
if I enter a class of 7.50A, the query returns results for 7.50A and 7.50E.
It appears that the query isn't differentiating between classes. What am I
doing wrong??

KARL DEWEY said:
Try this --
SELECT jeffh.CLASS, jeffh.SizeBeginRange, jeffh.SizeEndRange, jeffh.COST,
[Enter square footage]*[Cost] AS [Total Cost]
FROM jeffh
WHERE (((jeffh.CLASS)=[Enter class]) AND ((jeffh.SizeBeginRange)<=[Enter
square footage]) AND ((jeffh.SizeEndRange)>=[Enter square footage]));


:

I am trying to set up a database where users can enter a classification and
size and have access calculate a total cost. There are multiple
classifications such as 7.50A, 7.50G, 7.50F, etc. There are also size ranges
for each classification and an associated cost for each range. A sample of
the data is below..

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. A user entering a
class of 7.75G and 752 square feet would have
a cost returned of $173,486.40. (752 x 230.70 = 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

Although I am familiar with tables and queries, I would like suggestions or
examples of how best to set this up. I very much appreciate any input
provided. Thanks for your time.
Click to expand...
Click to expand...
Click to expand...
 
Top