Lookup wizard and same table fields in the query

[Saeen]

I have three tables
Pet_table(pet_id, name, age, sex, breed, pet_type)
Boarding(pet_id, boarding_id,start_date, end_date, fee)
Fee(pet_type, fee)

Fees in the boarding table is calculated by end_date-start_date*Fee
Fee is selected from table fee by comparing the pet_type in pet table and
fee table.
I have achieved this through a query but when i assign this value to the fee
attribute in boarding table using lookup wizard it says im trying use fields
from same table sorta error !!!
Any suggestions ?

 

[Saeen]

THanx for the reply but names isnt wats confusing me here.
I have done the required task through a query. Now i want to assign the
value generated by query to the field boarding.fee through lookup wizard but
its giving me errors probably coz the calculation is using two other fields
from the same table.

 

[Ken Sheridan]

I don't really see the need for either a query or the use of the 'lookup
wizard' here. What you need to do is to assign a value to the fee column in
Boarding when a row is inserted into that table, or if a row is edited in
such a way that the fee will change.

Firstly I would not store the total fee for the full duration of the pet's
stay in the Boarding table. Doing so leaves the table at risk to
inconsistent data as the dates might be changed, Instead I'd store the fee
per day which applies at the time, i.e. the current fee for the pet type in
question in the Fee table. The total fee can then be shown in a computed
control on a form or a computed column in a query, and will always correctly
reflect the number of days. The total fee can then be shown in a computed
control on a form or report for instance with:

=DateDiff("d", [start_date], [end-date])*[fee]

or as a computed column in a query with:

TotalFee: DateDiff("d", [start_date], [end-date])*[fee]

To assign a value to the fee column in Boarding put the following code in
the AfterUpdate event procedure of the pet-id control on a form based on the
Boarding table:

Dim strPetType as String
Dim strCriteria As String

' get pet type for selected pet
strCriteria = "pet_id = " & Me.pet_id
strPetType = DLookup("pet-type", "Pet_table", strCriteria)

' get fee for pet type and assign it to fee control
strCriteria = "pet_type = """ & strPetType & """"
Me.fee = DLookup("fee", "Fee", strCriteria)

This assumes that pet-id is a number data type, and that pet-type is a text
data type.


As regards the 'lookup wizard' many of us regard this as a feature to be
avoided like the plague. For the reasons why see:


http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England

 

[John W. Vinson]

THanx for the reply but names isnt wats confusing me here.
I have done the required task through a query. Now i want to assign the
value generated by query to the field boarding.fee through lookup wizard but
its giving me errors probably coz the calculation is using two other fields
from the same table.

Two suggestions:

- Don't store calculated values, *anywhere*.
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


- Don't use the Lookup Wizard, *anywhere*.
http://www.mvps.org/access/lookupfields.htm