How to calculate the number of days from two dates in a table

[Noob_2048]

I am making a basic hospital management system in Access 2013.I have two tables named "Bed" and "Receipt".
Bed(BedID,AssignedDate,PatientID,DischargeDate,BedCharges) Reciept(ReceiptID,PatientID,BedCharges)
I want to calculate "BedCharges" by calculating the number of days using "AssignedDate" and "DischargeDate" and then multiplying with a constant amount of charges per day. Also the BedCharges calculated in "Bed" Table also needs to be in the "Receipt" table.
How can I count the number of days and then calculate the "BedCharges" in both the tables?

 

[plog]

Also the BedCharges calculated in "Bed" Table also needs to be in the "Receipt" table.
How can I count the number of days and then calculate the "BedCharges" in both the tables?

You don't. You've made 2 mistakes in thinking.

1. In a database you don't store calulcated values, you calculate them. That means BedCharges isn't a field that exists in any table of your database, but a field in a query you build upon your tables.

2. You don't store redundant data. You don't store the same data all over your tables, you store it in one place and then build queries to link your data.

So, combining 1 & 2, you don't need a field in a table, you need a query in which you will calculate BedCharges. That calculation will use the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php) which you can use to find out how many days are between two dates.

 

[Noob_2048]

You mean that we do not need to store the biling information of a patient?

 

[plog]

You mean that we do not need to store the biling information of a patient?

I don't know what that means. I do know, per your initial explanation, that you do not store the value you are calling BedCharges. If you would like to explain what you mean by billing information I can answer that question as well.

 

[Noob_2048]

Bill information is the information regarding a patient's medical visit to a hospital including medicine charges , treatment charges,doctor fee etc
the Receipt table is for that purpose .Tell me if i am doing something wrong?

 

[plog]

Read up on data normalization (http://en.wikipedia.org/wiki/Data_normalization). Its the process of properly laying out your tables. Like I said before, 2 rules of it are you don't store calculated values nor redundant data.

 

[Noob_2048]

Thanks...Please guide me how can i perform the query that you talked about in your first comment?

 

[plog]

I did, you use the DateDiff function (http://www.techonthenet.com/access/functions/date/datediff.php).

 

[jadehawk]

@Noob_2048 Like it was already advice, a Calculated field should not be store.

But I ask this. You said the BedCharges get calculated based on a "constant" for the day.

What happens in the case you want to generate the receipt for a patient visit 2 years ago, when the price for the bed may have been different?

Maybe you should hard code the BedCharges per patient visit on your Reciept Table.
Just a thought.