new to access

[Derkins]

I'm pretty new to access and am totally lost as to how to do something.

I have a table for patients with nurses scheduled to see them looks something like.

PK Case Number
Name
MondayNurse
TuesdayNurse
.
.
.
etc

I want to generate a report to list all of the nurses for a given day and who they have been assigned to. Since I assign nurses to patents, im not sure how to do this.

Thanks guys

 

[Premy]

First thing: do the fields MondayNurse, TuesDayNurse, etc. always contain data in every row, like for example:

CaseNumber MondayNurse TuesdayNurse
1.................Joan.............Helen
2.................Frida.............Nancy

Or does your table look more like:

CaseNumber MondayNurse TuesdayNurse
1..................Joan
2....................................Nancy

If the latter is your case u will first have to do some research on normalisation and redesign your DB.

HTH

 

[Derkins]

No some are blank, some patients only need to be seen M T F for example

 

[MSAccessRookie]

I'm pretty new to access and am totally lost as to how to do something.

I have a table for patients with nurses scheduled to see them looks something like.

PK Case Number
Name
MondayNurse
TuesdayNurse
.
.
.
etc

I want to generate a report to list all of the nurses for a given day and who they have been assigned to. Since I assign nurses to patents, im not sure how to do this.

Thanks guys

If you have the option available to you, another way to approach this is to redesign the data tables. A simple table containing Patient ID, Nurse ID, and Date (Day of week) matched with a second table of Patient Information and a third table of Nurse information would allow you to make queries showing the nurses for each patient as well as the patients for each nurse.

 

[MSAccessRookie]

First thing: do the fields MondayNurse, TuesDayNurse, etc. always contain data in every row, like for example:

CaseNumber MondayNurse TuesdayNurse
1.................Joan.............Helen
2.................Frida.............Nancy

Or does your table look more like:

CaseNumber MondayNurse TuesdayNurse
1..................Joan
2....................................Nancy

If the latter is your case u will first have to do some research on normalisation and redesign your DB.

HTH

Normalization would most likely be beneficial in either case as I pointed out in my previous reply (without actually calling it normalization).

 

[Premy]

Well, like I suspected. Your db should be designed in such a way that these "cheeseholes" do not exist in your tables. If u give me some more to go on I can try to point you in the right direction.

Regards.

 

[Derkins]

Redesigning the database is not an option for me at this point. Is there a way to do it with VBA?

 

[Premy]

Your DB design, i.e, the way your tables are structured and related is the very foundation of your app. If this is weak, the whole thing will be wobby and there ain't no VBA cure for that, that's

 

[boblarson]

Redesigning the database is not an option for me at this point. Is there a way to do it with VBA?

Unfortunate, because I will liken this to opening a jar with a stick. If you try to open a jar with a stick, you might actually open it with the stick, but if the jar was designed right you could actually just twist off the top and get at your stuff inside. With the stick, you risk breaking something while trying to open it and you are not guaranteed that the results you get are actually going to be all that usable.

So, there may be a way to do it with VBA but it is not a "simple" fix and can complicate things as far as the rest of your database is concerned.

As the old Fram oil filter commercial used to say -

"You can pay me now, or pay me later"

meaning, you will eventually have to fix this problem. You can do it while it is still not a big deal, or you can create a monster and suffer much more later.

 

[MSAccessRookie]

Your DB design, i.e, the way your tables are structured and related is the very foundation of your app. If this is weak, the whole thing will be wobby and there ain't no VBA cure for that, that's

Premy seems to have been interrupted, but I will agree and say that a small design change could make life much easier. As it stands now, your primary forcus for the data is the patient, and locating a nurse is not an easy task. With the small change, the primary focus becomes the Nurse/Patient relationship, and you have access to both sides for reporting purposes.

Edit:

BobLarson agrees with me. That is a good thing (for me), and reiterates my belief that it would be a good thing for you. If you would like suggestions as to how to modify the design, please post back. People will assist you

 

[gemma-the-husky]

eg, if you wanted to find the patients that nurse Jones saw, finding this in your scenario will be very hard

finding this information with one of the proposed scenarios will be far easier,