Table with min/max lookup values

[Guest]

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?

 

[Guest]

I used a Hi & Lo Accts field in the table. Try this --
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));

 

[Guest]

One solution is to apply a Query to your table. And base the Form on the
Query.

In the Query, put in the criteria of the #ofAccounts column:

= [Forms]![YourForm].[ValueTextBox] AND < ([Forms]![YourForm].[ValueTextBox] + 4999)

This will return the correct row. In your example it will return the 30000
record row.

You can then set up your Form to show all the fields of this row - but let
the checkbox/radio button selected (in this case ABC) to make visible the
column which otherwise is defaulting to not visible.

 

[Guest]

How would you use a similar formula in a query? Or, how can I get the result
from your forumla in to populate another table?

I used a Hi & Lo Accts field in the table. Try this --
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?

 

[Guest]

Where did the 4999 come from? What if I have multiple values for different
items?

One solution is to apply a Query to your table. And base the Form on the
Query.

In the Query, put in the criteria of the #ofAccounts column:

= [Forms]![YourForm].[ValueTextBox] AND < ([Forms]![YourForm].[ValueTextBox] + 4999)

This will return the correct row. In your example it will return the 30000
record row.

You can then set up your Form to show all the fields of this row - but let
the checkbox/radio button selected (in this case ABC) to make visible the
column which otherwise is defaulting to not visible.


--
NTC

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?

 

[Guest]

the 4999 came from seeing your illustration - it is what mathematically
works....but it doesn't matter if your illustration was not fully
representative - - generically speaking you would need to establish an
algorithm that will return the proper row based upon the user's entered value
of #ofAccounts.....

this is just one approach....the advice from the other entry is also a way
to do it....
--
NTC

Where did the 4999 come from? What if I have multiple values for different
items?

One solution is to apply a Query to your table. And base the Form on the
Query.

In the Query, put in the criteria of the #ofAccounts column:

= [Forms]![YourForm].[ValueTextBox] AND < ([Forms]![YourForm].[ValueTextBox] + 4999)

This will return the correct row. In your example it will return the 30000
record row.

You can then set up your Form to show all the fields of this row - but let
the checkbox/radio button selected (in this case ABC) to make visible the
column which otherwise is defaulting to not visible.


--
NTC

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?

 

[Guest]

How would you use a similar formula in a query?
What I post was a query.
I do not understand your question.

Explain more as to what you are trying to do.

I used a Hi & Lo Accts field in the table. Try this --
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?

 

[Guest]

Your orignal post said you had a form for user to input 28,000 and
checkboxes. What you just posted does not look like any form input to a
query. I do not follow your SQL.

Okay, after slowing down and reading what is actually happening I understand
your SELECT statement. I have added an additional field to my table and now
have high and low fields (great idea!). Now I placed the statement below in
a query nder the "Field" (not criteria correct?) I replicated your formula
to better suit my database and it now reads as follows:

SELECT IIf([Calulated Proposal Details]![ODS]=Yes,"ODS" &
[ODS],0) AS [DW IH Rtl Price/IHCost/OutsrcCost License]![CBSODS] FROM Lookup
WHERE ((([# of Total Accounts]) Between [Lo Accounts] And [High Accounts]))

I keep getting the message "Check the subquery's syntax and enclose the
subquery in parentheses". I think we are almost there if I can resolve the
syntax issue. Any thoughts?

Thanks for your help!

How would you use a similar formula in a query?

What I post was a query.

how can I get the result from your forumla in to populate another table?

I do not understand your question.

Explain more as to what you are trying to do.

:

I used a Hi & Lo Accts field in the table. Try this --
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));


:

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?

 

[Guest]

Forget the form for a moment. I thought I was simplifying it by simply
pulling the checkbox result from a query as a Yes/No result versus pulling it
from a from a an "Is Not Null" criteria. Why does that have any bearing on
the formula itsself? I have a query result that holds the results of the
check boxes from the form input.

From there I want to say if the result in a specific field is "yes" then
perform the lookup, if not then use zero. If the result to the IIf statement
is "yes" then I want it to perform the lookup based on a value from a
different table (28,000 for example) compared to the hi/lo values and give me
a result (one number) from a specific column on another table.

Does that make it any clearer? The method of how the result from a checkbox
or yes/no answer is gathered is secondary here. I would be happy simply
understanding just how the basic lookup when used with the between command
works!


SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));

Your orignal post said you had a form for user to input 28,000 and
checkboxes. What you just posted does not look like any form input to a
query. I do not follow your SQL.

Okay, after slowing down and reading what is actually happening I understand
your SELECT statement. I have added an additional field to my table and now
have high and low fields (great idea!). Now I placed the statement below in
a query nder the "Field" (not criteria correct?) I replicated your formula
to better suit my database and it now reads as follows:

SELECT IIf([Calulated Proposal Details]![ODS]=Yes,"ODS" &
[ODS],0) AS [DW IH Rtl Price/IHCost/OutsrcCost License]![CBSODS] FROM Lookup
WHERE ((([# of Total Accounts]) Between [Lo Accounts] And [High Accounts]))

I keep getting the message "Check the subquery's syntax and enclose the
subquery in parentheses". I think we are almost there if I can resolve the
syntax issue. Any thoughts?

Thanks for your help!

How would you use a similar formula in a query?
What I post was a query.

how can I get the result from your forumla in to populate another table?
I do not understand your question.

Explain more as to what you are trying to do.


:

I used a Hi & Lo Accts field in the table. Try this --
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));


:

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?

 

[Guest]

SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " & [abc],
IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi " &
[ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));


This query has one table named Lookup as record source.
It prompts user for input [User input value] and is compared to Lookup table
fields [Lo_Accts] And [Hi_Accts].
It displays records from table Lookup where the user input is between the
value in the [Lo_Accts] And [Hi_Accts]. The function BETWEEN is equivalent
to >= [Lo_Accts] And <=[Hi_Accts].
It evaluates an object on a form [Forms]![YourForm]![CheckBoxABC] to see if
it is null. If it is not null it displays “abc “ and the contents of Lookup
table field [abc] as output field [Selected Output]. Also if it is not null
there is no further evaluation.
If the first evaluation is not true then if evaluates the second which is
whether [Forms]![YourForm]![CheckBoxDEF] is not null. If it is not null it
displays “def “ and the contents of Lookup table field [def] as output field
[Selected Output]. Also if it is not null there is no further evaluation.
If this second evaluation is not true it then displays “ghi “ and the
contents of Lookup table field [ghi] as output field [Selected Output].

Forget the form for a moment. I thought I was simplifying it by simply
pulling the checkbox result from a query as a Yes/No result versus pulling it
from a from a an "Is Not Null" criteria. Why does that have any bearing on
the formula itsself? I have a query result that holds the results of the
check boxes from the form input.

From there I want to say if the result in a specific field is "yes" then
perform the lookup, if not then use zero. If the result to the IIf statement
is "yes" then I want it to perform the lookup based on a value from a
different table (28,000 for example) compared to the hi/lo values and give me
a result (one number) from a specific column on another table.

Does that make it any clearer? The method of how the result from a checkbox
or yes/no answer is gathered is secondary here. I would be happy simply
understanding just how the basic lookup when used with the between command
works!


SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));

Your orignal post said you had a form for user to input 28,000 and
checkboxes. What you just posted does not look like any form input to a
query. I do not follow your SQL.

Okay, after slowing down and reading what is actually happening I understand
your SELECT statement. I have added an additional field to my table and now
have high and low fields (great idea!). Now I placed the statement below in
a query nder the "Field" (not criteria correct?) I replicated your formula
to better suit my database and it now reads as follows:

SELECT IIf([Calulated Proposal Details]![ODS]=Yes,"ODS" &
[ODS],0) AS [DW IH Rtl Price/IHCost/OutsrcCost License]![CBSODS] FROM Lookup
WHERE ((([# of Total Accounts]) Between [Lo Accounts] And [High Accounts]))

I keep getting the message "Check the subquery's syntax and enclose the
subquery in parentheses". I think we are almost there if I can resolve the
syntax issue. Any thoughts?

Thanks for your help!

:

How would you use a similar formula in a query?
What I post was a query.

how can I get the result from your forumla in to populate another table?
I do not understand your question.

Explain more as to what you are trying to do.


:

I used a Hi & Lo Accts field in the table. Try this --
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));


:

Let me see if I can illustrate my issue:

TABLE

# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500

From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?