may be crosstab will do the job

[mhmaid]

I have a table called |"Expense details"
I enter Patients expense details
1.expense category
2.expense description
3.expense date from
4.expense date to
5.amount

I want to account the Allowances Paid to the Patients
we have a daily allowance rate , but if he is inpatient rate will be half of
the rate

on start of treatment , we will give him an advance amount for a period of
time say 15 days.and assume that we gave him allowance of inpatient.

but after that we came to know that he was outpatient on part of the above
period.
so in this case we should pay him the diff

if the rate is $100 for outpatient
$50 for inpatientt
and we have paid him for 15 days
for the period from 01 march 2010 to 15 march 2010
my entry will be like this
expenseCategory=allowances
expenseDescription=Inpatient
expeneDatefrom=01march2010
expensedateto=15march2010
amount 1500
this transaction I have entered on 28feb2010
on 15march , the patient returned.
according to his medical report , we found that he was admitted for only
five days , from 01st to 5th march
now I need to pass an entry for the diff between inpatient to outpatient
like this
expensecategory=allowances
expensedescription=diff inp to op
expensedatefrom=06march2010
expensedateto= 15march 2010
amount = 500 ( 10 days X 50)

treatment period may be very long in some cases , in this case I will face
many cases like above example

when the patient returned I want access to tell me for any particular day
how much we have paid to the patient

in the above example ( the above two entries)
its like this
Date rate paid
01mar 50
02mar 50
03mar 50
04mar 50
05mar 50
06mar 100 (total of 50 which is from first entry plus 50 in 2nd )
and so on
want to view the data entered in the two entries like this format
so, if on patient return , or even before that
if for the date 02nd I pass a new entry ,system should accept as the rate
paid for that day is only 50
but
for 6mar it should not accept , because we paid 100 as an outpatient
how can i make the data entered in the above two entries to be show like the
list ( a line for each date , as above ) is this possible

now forget about the entry of dates of admission and discharge ,these I am
entering in a separate table, but so far I did not make any link between the
two ,
just wanted to get the list of days we have paid for the patient and the
total amount paid to him on each single date ( as above )

hope this can be done in access
thanks

 

[KARL DEWEY]

just wanted to get the list of days
Try this --
Create a table named CountNumber with number field called CountNUM with
numbers from 0 (zero) throught your maximum.
SELECT PatientID, DateAdd("d", CountNUM], [expense date from]) AS DatesOfStay
FROM YourTable, CountNumber
WHERE DateAdd("d", CountNUM], [expense date from]) Between [expense date
from] AND [expense date from]
ORDER BY PatientID, DateAdd("d", CountNUM], [expense date from]);

 

[mhmaid]

thank you very much Mr Karl .this has done the job perfectly
I really appreciate your help
thanks again

Try this --
Create a table named CountNumber with number field called CountNUM with
numbers from 0 (zero) throught your maximum.
SELECT PatientID, DateAdd("d", CountNUM], [expense date from]) AS DatesOfStay
FROM YourTable, CountNumber
WHERE DateAdd("d", CountNUM], [expense date from]) Between [expense date
from] AND [expense date from]
ORDER BY PatientID, DateAdd("d", CountNUM], [expense date from]);

--
Build a little, test a little.

I have a table called |"Expense details"
I enter Patients expense details
1.expense category
2.expense description
3.expense date from
4.expense date to
5.amount

I want to account the Allowances Paid to the Patients
we have a daily allowance rate , but if he is inpatient rate will be half of
the rate

on start of treatment , we will give him an advance amount for a period of
time say 15 days.and assume that we gave him allowance of inpatient.

but after that we came to know that he was outpatient on part of the above
period.
so in this case we should pay him the diff

if the rate is $100 for outpatient
$50 for inpatientt
and we have paid him for 15 days
for the period from 01 march 2010 to 15 march 2010
my entry will be like this
expenseCategory=allowances
expenseDescription=Inpatient
expeneDatefrom=01march2010
expensedateto=15march2010
amount 1500
this transaction I have entered on 28feb2010
on 15march , the patient returned.
according to his medical report , we found that he was admitted for only
five days , from 01st to 5th march
now I need to pass an entry for the diff between inpatient to outpatient
like this
expensecategory=allowances
expensedescription=diff inp to op
expensedatefrom=06march2010
expensedateto= 15march 2010
amount = 500 ( 10 days X 50)

treatment period may be very long in some cases , in this case I will face
many cases like above example

when the patient returned I want access to tell me for any particular day
how much we have paid to the patient

in the above example ( the above two entries)
its like this
Date rate paid
01mar 50
02mar 50
03mar 50
04mar 50
05mar 50
06mar 100 (total of 50 which is from first entry plus 50 in 2nd )
and so on
want to view the data entered in the two entries like this format
so, if on patient return , or even before that
if for the date 02nd I pass a new entry ,system should accept as the rate
paid for that day is only 50
but
for 6mar it should not accept , because we paid 100 as an outpatient
how can i make the data entered in the above two entries to be show like the
list ( a line for each date , as above ) is this possible

now forget about the entry of dates of admission and discharge ,these I am
entering in a separate table, but so far I did not make any link between the
two ,
just wanted to get the list of days we have paid for the patient and the
total amount paid to him on each single date ( as above )

hope this can be done in access
thanks