Duplicating Facilities

[rch918]

We are trying to get this database Provider specific. When they go into the database it will open the frmProvidersList. This will list all the providers we provide support to on our EMR. When we click on the Open link it opens to the frmProviderDetails. On this form we would like it to list all the facilities the provider works / sees patients. So we're thinking the relationship should be one provider at many facilities. Right now it shows the providers and their facilities the ones we associated on the tables. However when we add a facility already on the tblfacility to the frmProvidersDetails it adds it as a new facility. I am assuming the relationship is incorrect. Can someone help with this, pleeeeaase? I have attached a test db we're using and removed any sensitive information.



Thank you in advance

[ssanfu]

I don't know if you solved this yet, but I looked at the dB for a loooong while.

It looks like the 2 forms are wizard designed - I've never been impressed with wizard form design and/or the embedded macros.

The description of the problem and the naming of the objects was very confusing. I finally renamed fields and some other objects.

The main problem was the main form/sub form links. The main table PK field is the provider PK and that PK should be saved in the subform field provider FK. Once the facility is chosen (now a combo box), the rest of the data is filled in.

To me, "tblProviders" should have a field for the first name, last name and the degree, not one field. Having 3 pieces of data in 1 field violates normalization rules.


Anyway, see if this is what you are wanting........... the 2 providers I tested/worked with are Abimbola Allen-Taylor, APRN and Nadim Jafri, MD.

[rch918]

ssanfu,

Sorry for the delayed response but I was out of the office. Thank you for looking at this for me. I have downloaded the file and it looks correct. Of course I will run it by the team just to confirm.

Thanks for your help.

[rch918]

ssanfu,

yes the DB works perfect, but I was wondering if you could help me with one more thing, is there a way I could indicate on the facilities which one is the provider's main/primary facility?

The only thing I thought would work was to add PrimaryLocation (PL) field to the workplace table and make it a yes/no, but when they select facility A for one provider any provider associated with Facility A has the same check.
I also tried to add a check button to the form but the same thing would happen too.

If not, I also suggested we just select their primary location first on the ProviderDetails and secondary locations thereafter.

[ssanfu]

OK, I made a few changes.

I added a form to assign Facilities (locations) to a provider. On this for you can set the primary facility (location/address).
I cleaned up a few macros... (macros ... )
Added a quit button on the providers list form.
Added an unique index in tblWorkplaces so a facility (location/address) cannot be added more than once to a provider.
Added a couple of queries and forms


The 2 providers I tested/worked with are Abimbola Allen-Taylor, APRN and Nadim Jafri, MD.
Looking at each one ,you can see that both providers have "Memorial Hermann Transplant Clinic" assigned to them, but only Abimbola Allen-Taylor, APRN has the primary facility checked.

How is this???

[rch918]

Ssanfu,

Thanks for all your help. This is perfect. I'm sure they'll comeback with something else to add to it.

I feel like I did a lot of the same steps you did with adding the primary location to the tblworkplace and made it a yes/no field. Added it to the provadd query too.
I will look deeper into what you have done and try to understand all the changes you made. Now I have to figure out how to mark this as resolved.

[ssanfu]

You are welcome...... good luck with your project....