I need help with calculations in queries in MS Access!

[rikadik]

I've agreed to build a database and then do a load of data entry for my dad but I don't really know what I'm doing. Basically, I'm fine with the easy bit - creating a table and form - but am having difficulty with some queries I have to create.

The database is simple, having only three fields:

• SxNumber - number any length long, unique, primary key. This is a unique ID which identifies every patient my dad sees.

• Callback - yes/no, default no. This is whether or not my Dad identified something from the first scan of the patient which prompted him to call the patient back into hospital.

• B5Diagnosed - yes/no, default no. This is whether or not the patient was later diagnosed with B5 (that is, cancer)

From the information in the database, I want to run queries to display the following data:

• Callback Rate - percentage of all patients who are called back. I.e., number of entries with Callback field as 'Yes' / total number of entries * 100%

• Final Diagnosis Rate - percentage of all patients who are diagnosed with B5. I.e., number of entries with B5Diagnosed field as 'Yes' / total number of entries * 100%

• Initial Diagnosis Rate - percentage of patients who are diagnosed with B5 who were called back. I.e., number of entries with Callback field as 'Yes' and B5Diagnosed field as 'Yes' / number of entries with B5Diagnosed field as 'Yes' * 100%

If I was doing this in Excel it'd be no problem but I can't really get my head around Access at the moment. I could explain the things I've tried but it isn't worth it because I'm sure this is a case of me not knowing how to use Access and the answer will be fairly straight forward!

Many thanks in advance!

 

[EagleKeeper]

You may have to create some macros.

There is a function that will tell you how many records were read in with a query.

Keep track of the total reference records using one query. Result-A
Then run the second query with the filter on the reference records (condition that you are testing for). Get the total records that match filter Result-B

Percentage = Result-B/Result-A*100.

 

[rikadik]

I've managed to set it up. Your suggestion pointed me in the right direction - that is, making multiple queries rather than trying to do it all in one.

 

[EagleKeeper]

Any problem is much easier to solve and/or get a handle on it when you break it into small enough pieces to digest.

Good Luck