Calculated Field

[Chris Burke]

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Many Thanks

 

[The_Doc_Man]

First, watch out for multiple posts. Sometimes your browser "hiccups" and you get some. (Like, you have a triple at the moment.)

Second, NEVER store in a table what you can compute in a query. A, because next year the values will change so the field in the table will go out of date. B, because you generally want to run a query to do all your computations anyway and base your reports or forms on the query. Never use a table directly from anything but a query unless you are doing a combo-box or list-box type of lookup. (Even then, there is some precedent for using queries...)

 

[ScottGem]

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Many Thanks

As Doc said, we don't store calculated values. They can be calculated on the fly as needed. The following expression will calculate age:

Age=DateDiff("yyyy", [Bdate], Now())+ _
Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

 

[BadKitty]

different type of calculated field

that info was helpful. i do have a question though...i also need a calculated field, but once it's done, i need to store it permanently. (i'm the one with the permit db) for example, a contractor pays for more than 1 permit at a time (or just 1) with one check (the ck # is the primary key) but it is 1 transaction that i would like to store in the payment table. should they come back for another permit, that will be a new transaction & a new entry. any suggestions? thanx in advance.

 

[ScottGem]

that info was helpful. i do have a question though...i also need a calculated field, but once it's done, i need to store it permanently. (i'm the one with the permit db) for example, a contractor pays for more than 1 permit at a time (or just 1) with one check (the ck # is the primary key) but it is 1 transaction that i would like to store in the payment table. should they come back for another permit, that will be a new transaction & a new entry. any suggestions? thanx in advance.

I don't understand what you think you need to store. The payment table will store the Check #, the amount, the payee, etc. What do you need to calculate it and why do you think you need to store it. It is EXTREMELY rare that you need to store a calculated value.

 

[BadKitty]

a bit mixed up

maybe i'm confused...see, the users will be entering individual amounts in each form for each permit. if there is more than one permit purchased, i would think i need a sum, wouldn't i? that's what i want to be stored in the payments table. if i am figuring this incorrectly please let me know. thanx!

 

[ScottGem]

maybe i'm confused...see, the users will be entering individual amounts in each form for each permit. if there is more than one permit purchased, i would think i need a sum, wouldn't i? that's what i want to be stored in the payments table. if i am figuring this incorrectly please let me know. thanx!

This is a classic order entry scenario. You need at least 2 tables; PermitOrders and OrderDetails. The PermitOrders table has info about the Date of the order, the person/organization ordering the permits and any other info that is specifc to the order. The OrderDetails has a foreign key to link it to the PermitOrders record and has info about each permit ordered including fee.

No, you do not store the sum since that can be calculated as needed by adding up the fees for all permits with that PermitOrderID.

 

[BadKitty]

thanx, but a bit out of practice

let me give you a brief description of the whole database:
i have a table for contractors/owners who will be the ones applying for the permits. there are 4 types of permits that may be purchased - it was suggested to me that i have one main permit table and extra tables related 1-1 w/the main permits table for the differing attributes of each permit. also, as you said, i will have the payment table & the payment detail table. contarctor/owners may be buying more than 1 type of permit at a time, but paying with the same check. in the end, what i need is to be able to print each permit specifically w/its price & also a receipt for the total amount paid (with one or more check #s on it). i'll have one form for users to enter contractors/owners info; one form for the permit info w/tabs for the specific attributes; and then i need a form for payments to be entered to be entered by users. any suggestions are appreciated.

 

[ScottGem]

This doesn't change anything I have said before.

 

[BadKitty]

primary key

should the PermitOrderDetails table have its own primary key or have the foreign key from the PermitOrders table be the primary key as a 1-1 relationship for the details table?

 

[ScottGem]

should the PermitOrderDetails table have its own primary key or have the foreign key from the PermitOrders table be the primary key as a 1-1 relationship for the details table?

Unless only one permit is ordered at a time then each table should have its own PK and the Details table should have an FK to the main Orders table.

 

[BadKitty]

Thank You!

you've been alot of help through this whole process...i haven't designed anything with this much info since i was in school (an that was quite some time ago). appreciate all your help & patience.

 

[ScottGem]

glad to assist