Fields in reports

[Dr Alok Modi MD]

I am a doctor and I am designing a software for my hospital billing. Now
there are various fields like
1. bed charges
2. nursing
3. Initial consultataion
4. visists
5. OT Charge

etc.
There will be some 20 or so fields.
Now every patients does not pay in all the headings. Depending on the nature
of the disese.

Now when I want to print out the report I want to show only those fields
that the patient is going to pay for, but these fields are going to be there
in the form and the table that is being created for them.
So how do I tell access that "Please take only those fields that have been
filled and do not show the other ones on the report when I generate the
report and then sum up these fields only."

Thnaks for your time

Dr Alok Modi MD

 

[jl5000]

Still trying to make it simple, to do this you need at least 3 tables:

Patient_tab(Patient_Id,Name,......) This table holds patient info
Patient_Service_tab(Patient_Id,Dated,Service_Code,Amount) Services for patient
Service_Code_tab(Service_Code,Description,Price) Services provided

When you create your report, create a query that joins the Patient_tab with
Patient_Service_tab by Patient_Id, and Patient_Service_tab with
Service_Code_tab by Service_Code,

Select the needed fields for your report and the joins will cause to show
only the services provided by patient,

I suggest, for detailed information, you read about
relationships/normalization in the help file,

 

[Allen Browne]

Instead of 20 or so different fields for the fee types, consider creating a
table of fee types. Then add a related record to the visit for each fee
payable in the consultation.

This kind of structure:

FeeType table (one record for each type of fee)
FeeTypeID primary key
FeeType descripion/name of this fee type.
Amount typical current default dollars for this fee type.

Patient table (one record per patient)
PatientID primary key

Visit table (one record for each consultation)
VisitId primary key
PatientID foreign key (fk) to Patient.PatientID
DoctorId who the visit is with.
VisitDateTime date and time of visit
Duration number of minutes booked

VisitFee table (one record for each fee in a visit)
VisitFeeId fk to Visit.VIsitId
FeeTypeId fk to FeeType.FeeTypeId
Amount dollars

For an example, look at the Northwind sample database. The Visit table is
like the Orders table, and the VisitFee table is like the OrderDetail table.

 

[Marshall Barton]

I am a doctor and I am designing a software for my hospital billing. Now
there are various fields like
1. bed charges
2. nursing
3. Initial consultataion
4. visists
5. OT Charge

etc.
There will be some 20 or so fields.
Now every patients does not pay in all the headings. Depending on the nature
of the disese.

Now when I want to print out the report I want to show only those fields
that the patient is going to pay for, but these fields are going to be there
in the form and the table that is being created for them.
So how do I tell access that "Please take only those fields that have been
filled and do not show the other ones on the report when I generate the
report and then sum up these fields only."

This question is brought about by the way you have
structured your tables. Proper database Normalization would
have a charges table with at least fields for the type of
charge and another for the amount. This would allow you to
use the standard built-in capabilities to perform the
calculations you want. What you have is more like a
spreadsheet, but database systems (not just Access) do not
perform calculation the way spreadsheets do.

OK, lecture over ;-)

There are some things you can do, but they depend on the
specifics of your report layout. For instance, if the text
boxes are one above the other and do not have a label, you
can set the text boxes CanShrink property to Yes.

It seems rather unlikely that your report would not have
attached labels, in which case you will also need some VBA
code in their section's Format event to make the text boxes
and their attached label invisible so they can both shrink:
Me.sometextbox.Visible = Not IsNull(Me.sometextbox)

To calculate the total you want, you can use a cumbersome
text box expression along these lines:
=Nz([bed charges], 0) + Nz([nursing], 0) + . . .

Be sure to check Help on these concepts to determine how
they might (or might not) be utilized in your situation.

 

[Mike Painter]

Instead of 20 or so different fields for the fee types, consider
creating a table of fee types. Then add a related record to the visit
for each fee payable in the consultation.

This kind of structure:

FeeType table (one record for each type of fee)
FeeTypeID primary key
FeeType descripion/name of this fee type.
Amount typical current default dollars for this fee type.

Patient table (one record per patient)
PatientID primary key

Visit table (one record for each consultation)
VisitId primary key
PatientID foreign key (fk) to Patient.PatientID
DoctorId who the visit is with.
VisitDateTime date and time of visit
Duration number of minutes booked

VisitFee table (one record for each fee in a visit)
VisitFeeId fk to Visit.VIsitId
FeeTypeId fk to FeeType.FeeTypeId
Amount dollars

This is a start but the visit table will probably contain only the ID of the
referring doctor.
Another table will be needed for doctors, etc this would probably be related
to the VIsitFee table (Hmm it looks like we type the same, "VisitFee" table)
as any number of docs might request any number of tests, etc.

Doc, I hope you are not doing this for a US hospital as you will soon be a
patient under heavy sedation when such things as Insurance and Medicare
enter the picture and make things ugly.

 

[Dr Alok Modi MD]

Wow you guys got it all wrong. Of course I would normalise my tables. There
will be separate tables for
1. patient name, address etc, with Patient ID as the primary key or Indoor
number as the primary key.
2. There will be a table for diagnoses
3. there will be a table for clinical summary
4. discharge details
5. There will be one table for all type of charge structures.
Now I would like to manually choose what charges to show in one particular
patient which would differ to the next. So on the report i wanted to show
only those fields that are charged.
Is there a way?

Dr Alok Modi MD

 

[Marshall Barton]

Wow you guys got it all wrong. Of course I would normalise my tables. There
will be separate tables for
1. patient name, address etc, with Patient ID as the primary key or Indoor
number as the primary key.
2. There will be a table for diagnoses
3. there will be a table for clinical summary
4. discharge details
5. There will be one table for all type of charge structures.
Now I would like to manually choose what charges to show in one particular
patient which would differ to the next. So on the report i wanted to show
only those fields that are charged.
Is there a way?

Dr Alok Modi MD

Since all the responders to your question got the same
impression, maybe there's a terminology issue in
interpreting your question? You say that you only want to
display the "fields" that have charges when in a normalized
table structure, you could just use a query criteria to
filter out the "records" with a Null charge.

If your charges table has a separate field for each kind of
charge, then it is not normalized and the cause of your
problems.

And, Yes, as I tried to outline before, there is a way to
deal with unnormalized data, but it gets messy.

 

[Nancy]

You could use a combination of the IIf and IsNull statements - something like
this
IIf(IsNull[BedCharges],0,"Bed Charges") - checks to see if the BedCharges
field is blank or null - if not null then will print the words "Bed Charges"
on the report. Now on the same line, in another text box
IIf(IsNull[BedCharges],0,[BedCharges]) with a format of currency. The line
will not print if there are no charges for that option.

 

[Dr Alok Modi MD]

Thanks Marsh
I think using a query that filters out null records will solve the purpose.
Now one more trouble, how do i use design a query that filters out null
records. i am going to read up Access insode out 2003 by John and if I don't
get it i will get back to you.
thanks

 

[Marshall Barton]

Thanks Marsh
I think using a query that filters out null records will solve the purpose.
Now one more trouble, how do i use design a query that filters out null
records.

OK, let's assume you have a charges table with fields:

Clients table:
ClientID AutoNumber - PK
ClientName Text
. . .
ChargeType table:
TypeID AutoNumber - PK
Descr Text 'description of charge type
. . .

ClientCharges table:
ChgID AutoNumber - PK
ClientID Long 'FK to client table
ChgType Long 'FK to charge type table
ChgDate DateTime
Amount Currency
. . .

Then you can display the charges for a client by using a
query like:

SELECT Clients.ClientName,
ChargeType.Descr,
ClientCharges.ChgDate,
ClientCharges.Amount
FROM ClientCharges
INNER JOIN Clients
ON ClientCharges.ClientID = Clients.ClientID
INNER JOIN ChargeType
ON ClientCharges.ChgType = ChargeType.TypeID

Note that, in this arrangement, there is no need to filter
out the charge types that have no charge since they will
never be entered into the ClientCharges table. However, you
may need to filter on the date of the charge and/or on some
other data beyond these three tables.

 

[Dr Alok Modi MD]

This solves the problem. Thnaks guys for all your time.

 

[Mike Painter]

This solves the problem. Thnaks guys for all your time.

If you are using if Null to filter out charges the probability that your
tables are wrong is *very* close to 1.
If a patient does not get charged for some thing the value should be zero,
if the patient does not get a particular item that line item should not be
in the table.

This line in your original post "only those fields that the patient is
going to pay for" would read "Only those records a ..."

To provide a service and not record it, even if there is no charge is
something that will make accountants and oversight committees shudder.