Lookup Field

[Rita]

I created a table with dates. In another table I'm trying to create a lookup
field based on this table. I get the message "You entered an expression that
has n invalid reference to the property |".

Does anyone know if you are able to use Date/Time as a lookup?

THANKS!

 

[Ken Snell [MVP]]

May we recommend that you not put a lookup field in your table? It will only
cause great confusion later on. See this article for more info:
http://www.mvps.org/access/lookupfields.htm

Use a combo box on a form to do what you seek. A form should be used for
manipulating data that are in tables, or for adding new data manually to
tables.

 

[KLP]

Ken,

I read that article and it seems to address my problem. I have a table of
students, among the fields are tuition and discount. This is for a private
school. Tuition is a lookuop field with the values coming from the tuition
rates table and discount is also a lookup field with values selected from the
disc rate table. The objective is to be able to change the tuition rates and
dscounts and see the effect. I created a query where a new field is
calculated - net:[tuition]*[discount] I am getting some strange results. I
am taking an indepth access class at community college and it stumped the
instructor. Is there a work around for this? I originally used an Excel
spreadsheet for this, but it is way too complicated for the school staff to
use.

 

[Ken Snell [MVP]]

If you're still using lookup fields in the table for tuition and discount,
then the query as you've created it will not give you the proper results.

Tuition in the table is actually storing the "value" of the primary key from
the table from which you're displaying the tuition choices, it's not storing
the actual value that you're displaying. The same is true for the Discount
field.

How do you want to change the tuition and discount amounts? in the table
itself?

Please give us much more details. Using a query to display a calculation
such as you wish is a very basic capability for ACCESS so long as you're
using the right values. It may be necessary to change the query's design to
do what you want, but I won't know that until you can tell me more about the
tables, the fields in the tables, the query that you're trying to use, and
so on.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I read that article and it seems to address my problem. I have a table of
students, among the fields are tuition and discount. This is for a
private
school. Tuition is a lookuop field with the values coming from the
tuition
rates table and discount is also a lookup field with values selected from
the
disc rate table. The objective is to be able to change the tuition rates
and
dscounts and see the effect. I created a query where a new field is
calculated - net:[tuition]*[discount] I am getting some strange results.
I
am taking an indepth access class at community college and it stumped the
instructor. Is there a work around for this? I originally used an Excel
spreadsheet for this, but it is way too complicated for the school staff
to
use.

May we recommend that you not put a lookup field in your table? It will
only
cause great confusion later on. See this article for more info:
http://www.mvps.org/access/lookupfields.htm

Use a combo box on a form to do what you seek. A form should be used for
manipulating data that are in tables, or for adding new data manually to
tables.

 

[KLP]

Tuition and discount would be changed using a form. There are 3 tuition
amounts based on grade and 4 discount levels based on the number of children
in the family, i.e., 1st child no discount, 2nd child 30% off, etc.

Child Info Table:
child id - autonumber, primary key
familyid - linked from family info table (parents name, address, etc.
child first name
child last name
grade
child no - child's position in the family 1st, 2nd, 3rd, etc.
tuition rate - lookup field - linked to tuition table
multi child discount - lookup field, liniked to discount table

Tuition Table
rates id - autonumber and primary key
tuition

Discount Table:
multichild id - autonumber and primary key
number of children
discount - the amount entered is the % owed, e.g., for a 30% discount, 70
would be entered., this is prevent multiplying by zero in the query.

The query used the Child Info table only. The new field which would compute
the net tuition per child is net:[tuition rate]*[multichild discount]/100

The relationship between the tuition and discount table to the Child Info
table is one-to-many with the child info being the many. Enforce Referential
integrity is checked.

Thank you for any insight in resolving this.

If you're still using lookup fields in the table for tuition and discount,
then the query as you've created it will not give you the proper results.

Tuition in the table is actually storing the "value" of the primary key from
the table from which you're displaying the tuition choices, it's not storing
the actual value that you're displaying. The same is true for the Discount
field.

How do you want to change the tuition and discount amounts? in the table
itself?

Please give us much more details. Using a query to display a calculation
such as you wish is a very basic capability for ACCESS so long as you're
using the right values. It may be necessary to change the query's design to
do what you want, but I won't know that until you can tell me more about the
tables, the fields in the tables, the query that you're trying to use, and
so on.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I read that article and it seems to address my problem. I have a table of
students, among the fields are tuition and discount. This is for a
private
school. Tuition is a lookuop field with the values coming from the
tuition
rates table and discount is also a lookup field with values selected from
the
disc rate table. The objective is to be able to change the tuition rates
and
dscounts and see the effect. I created a query where a new field is
calculated - net:[tuition]*[discount] I am getting some strange results.
I
am taking an indepth access class at community college and it stumped the
instructor. Is there a work around for this? I originally used an Excel
spreadsheet for this, but it is way too complicated for the school staff
to
use.

May we recommend that you not put a lookup field in your table? It will
only
cause great confusion later on. See this article for more info:
http://www.mvps.org/access/lookupfields.htm

Use a combo box on a form to do what you seek. A form should be used for
manipulating data that are in tables, or for adding new data manually to
tables.
--

Ken Snell
<MS ACCESS MVP>



I created a table with dates. In another table I'm trying to create a
lookup
field based on this table. I get the message "You entered an expression
that
has n invalid reference to the property |".

Does anyone know if you are able to use Date/Time as a lookup?

THANKS!

 

[KLP]

Since I sent the last response, I tried this: brought the tuition rate and
discount table into the query and used the tuition field from tuition rate
table and discount form the multi-child discount table. I ran some numbers
and it appears to work.

Your thoughts?

Kelvin

If you're still using lookup fields in the table for tuition and discount,
then the query as you've created it will not give you the proper results.

Tuition in the table is actually storing the "value" of the primary key from
the table from which you're displaying the tuition choices, it's not storing
the actual value that you're displaying. The same is true for the Discount
field.

How do you want to change the tuition and discount amounts? in the table
itself?

Please give us much more details. Using a query to display a calculation
such as you wish is a very basic capability for ACCESS so long as you're
using the right values. It may be necessary to change the query's design to
do what you want, but I won't know that until you can tell me more about the
tables, the fields in the tables, the query that you're trying to use, and
so on.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I read that article and it seems to address my problem. I have a table of
students, among the fields are tuition and discount. This is for a
private
school. Tuition is a lookuop field with the values coming from the
tuition
rates table and discount is also a lookup field with values selected from
the
disc rate table. The objective is to be able to change the tuition rates
and
dscounts and see the effect. I created a query where a new field is
calculated - net:[tuition]*[discount] I am getting some strange results.
I
am taking an indepth access class at community college and it stumped the
instructor. Is there a work around for this? I originally used an Excel
spreadsheet for this, but it is way too complicated for the school staff
to
use.

May we recommend that you not put a lookup field in your table? It will
only
cause great confusion later on. See this article for more info:
http://www.mvps.org/access/lookupfields.htm

Use a combo box on a form to do what you seek. A form should be used for
manipulating data that are in tables, or for adding new data manually to
tables.
--

Ken Snell
<MS ACCESS MVP>



I created a table with dates. In another table I'm trying to create a
lookup
field based on this table. I get the message "You entered an expression
that
has n invalid reference to the property |".

Does anyone know if you are able to use Date/Time as a lookup?

THANKS!

 

[Ken Snell [MVP]]

That sounds like the correct approach, yes.

--

Ken Snell
<MS ACCESS MVP>

Since I sent the last response, I tried this: brought the tuition rate
and
discount table into the query and used the tuition field from tuition rate
table and discount form the multi-child discount table. I ran some
numbers
and it appears to work.

Your thoughts?

Kelvin

If you're still using lookup fields in the table for tuition and
discount,
then the query as you've created it will not give you the proper results.

Tuition in the table is actually storing the "value" of the primary key
from
the table from which you're displaying the tuition choices, it's not
storing
the actual value that you're displaying. The same is true for the
Discount
field.

How do you want to change the tuition and discount amounts? in the table
itself?

Please give us much more details. Using a query to display a calculation
such as you wish is a very basic capability for ACCESS so long as you're
using the right values. It may be necessary to change the query's design
to
do what you want, but I won't know that until you can tell me more about
the
tables, the fields in the tables, the query that you're trying to use,
and
so on.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I read that article and it seems to address my problem. I have a table
of
students, among the fields are tuition and discount. This is for a
private
school. Tuition is a lookuop field with the values coming from the
tuition
rates table and discount is also a lookup field with values selected
from
the
disc rate table. The objective is to be able to change the tuition
rates
and
dscounts and see the effect. I created a query where a new field is
calculated - net:[tuition]*[discount] I am getting some strange
results.
I
am taking an indepth access class at community college and it stumped
the
instructor. Is there a work around for this? I originally used an
Excel
spreadsheet for this, but it is way too complicated for the school
staff
to
use.

:

May we recommend that you not put a lookup field in your table? It
will
only
cause great confusion later on. See this article for more info:
http://www.mvps.org/access/lookupfields.htm

Use a combo box on a form to do what you seek. A form should be used
for
manipulating data that are in tables, or for adding new data manually
to
tables.
--

Ken Snell
<MS ACCESS MVP>



I created a table with dates. In another table I'm trying to create a
lookup
field based on this table. I get the message "You entered an
expression
that
has n invalid reference to the property |".

Does anyone know if you are able to use Date/Time as a lookup?

THANKS!