Create Record Database Help

[charlesh]

Hi,

I do not know if I'm failing to see what your telling me. I created the table for the "Patients name only". But am failing to see how this will help me when I select the "Provider" from the Providers combobox in the form and have "Patients Combo" only display the Patient Name (once) from the table for "Patient Record"(listed multiple times) and (not the new table I created). The field "Patient Name" in the table for the " Patients Records" will have the same name listed several times for the provider selected. My goal is not to have the "Patients" combobox show duplicate names. In Excel I have a code to populate the combobox without duplicate names.
So as you can see I'm still confused using Access.

Charles

[June7]

Let's back up, I might have I responded hastily and my advice in post 8 was in error. We need a better understanding of data relationships. Certainly each provider can have more than one patient but can a patient have more than one provider? If yes that is a many-to-many relationship and then normalized data structure would be like:

tblPatients
ID (primary key)
LastName
FirstName
Gender
etc

tblProviders
ID (primary key)
LastName
FirstName
Address
etc

tblPatientProviders
PatientID (foreign key)
ProviderID (foreign key)

For data entry the conventional approach is a form/subform arrangement. Options:

1. main form bound to tblPatients and a subform bound to tblPatientProviders with an unfiltered combobox to select provider

2. main form bound to tblProviders and a subform bound to tblPatientProviders with an unfiltered combobox to select patient


If a patient can have only one provider that is a one-to-many relationship (each provider has many patients) then have a field in patient info table for the ProviderID and the junction table tblPatientProviders is not needed. This means a data entry form bound to tblPatients with a combobox to select provider.


Now what is purpose of the form you want? If it is just to allow search for patients associated with selected provider, the approach used will depend on the relationship of the data. So which relationship is correct for your data?

[charlesh]

June8,

Yes one Patient can have more than one provider and too the provider will have more than one patient.
The purpose of the form is to allow the user to select the "Provider" this selection in turn should only populate the "Patient" combo,
But the Patient combo must only show the Names of the Patients one time. Even if the are muti entries for that patient and provider.
The user will then have the option to review the record for the Patient selected. This will then allow the user to update the record if a correction needs to be done.
In my form I have "Add" for new records. "Update" for updating the record selected, "Next" to look at the next record for the same Patient. and "Previous" should the user need to view the previous record for the same patient again.
My approach would select the "Provider" first then the Patient. The selection of the patient will then fill in most of the forms fields.

Charles

I have a working Excel file, but would rather use access. This will help with the "File Sharing" issue in Excel.

[June7]

Showing patient names only once is easy part.

What I am not clear about is the behavior you want to use this form for. Do you want to use it for creating new patient/provider associations in the junction table tblPatientProvider?

[charlesh]

Hi,

Short answer is "Yes".
The user will select the "Provider" then enter the info for the Patient.
Once I get this part working I will need to start another part for the Patent Record.
This will include some "Math" and "Email"
But, one step at a time.

Charles

[June7]

The way data entry would normally work is with form/subform arrangement described in post 17.

Select item from combobox, if desired item (patient or provider depending on the option) not in the list then code in the combobox NotInList event opens a form to enter the new record (patient info or provider info) then the new item is available for selection in the combobox.

[charlesh]

HI,

Thanks again for your response.
The way I have Excel set up is
Data for the provider come from a sheet that I have named "Lookup"
This is where all of the info for the provide is.
The Combo box for the user id populated from this list.
When the User selects the Provider the Excel form will populate the info for the Provider.
At the same time it will in the Record for the Patient set a "Filer" for the Provider selected. (Note all of the providers will be listed in the sheet for them)
When the Filter is set I have a code that when ran will populate the "Patient" combo minus any duplicates.
Now the user will know upfront if the "Patient" is a new Patient for that Provide and they will select the Provide then add the data for that Patient.
Once the data is entered the will click "Add".
Also the user will know upfront if the Patient is for the same provider (Date of Service). They will select the provider then the patient make the necessary change if any the click the "Add" button.
This will add the data to the Record for patients.
Now if there needs thee need to be a correction for a record.
The user select the "Provider" then the "Patient" from the dropdown.
If the first record for that Patient need to be updated the user will correct the data. If the first occurrence is not the recored that needs to update the user will click next and so on until they get to desired record for update. They will then click the "Update" button. I have a "Previous" button. But is should be obvious.
So essence I have 2 sheets of data 1 for the Provider and the other for the Patients Record.
I know your trying to help with your suggestions, but I'm completely new to access am have a hard time understanding your suggestions.
If this can be done with code I may be able to understand it better.

[June7]

Have to forget how things work in Excel. In a relational database where data has a many-to-many relationship, 3 tables are required at minimum.

tblProviders

tblPatients

tblPatientProvider

The last table is a junction table that will associate patients with providers. Need records in the junction table before can do any filtering based on patient/provider associations. The form/subform arrangement will facilitate creation of records in the junction table as well as create new records in the providers and patients tables. Filtering the combobox in subform based on the record of main form will subvert the functionality of this arrangement. The subform itself, however, will automatically filter to display only records associated with the main form record.

Cascading comboboxes are useful to filter lists when the comboboxes are in the same form or when trying to set up filter criteria to restrict records for report output.

[charlesh]

Ok,
If I need

tblPatientProviders
PatientID (foreign key)
ProviderID (foreign key)

How would that table be set up.
Can you provide an example?

Charles

[June7]

That is an example. Need the two foreign key fields at a minimum. Add others at your discretion. Additional fields you might want:

EnterBy
EnterDate

[charlesh]

June7,

Once again thanks.
But, The example I was looking for was what the table should look like.

Charles

[June7]

Don't understand. Table will look like a table. I proposed four fields. Use table designer and create those fields. Use whatever names for table and fields that make sense to you.

[charlesh]

June7,

I created the table for Provider/Patient. But how do you set the
PatientID (foreign key)
ProviderID (foreign key)

In the table I would assume if a Provider has more than one Paitent
you would the in the table have in the column for the Provider their name listed several times that is equal to the number of Patients that they have.
So if Provider1 has 5 Patients then you will see the provider name 5 times with the offset column for the Patent name(with no dupes).

Charles

[June7]

Yes, and if a patient is associated with multiple providers, patient name will be repeated but not with the same provider.

Use form/subform for data entry/edit as described earlier.

BTW, hope you did not really include "(foreign key)" in the field names. That was just informational.

[charlesh]

Hi,

No I did not include it.
What I was asking if the "Foreign Key" was an actual key.
So what I need to know is if I need to add "Primary Key" in the table for "Provider/Patient?

Charles