Question Few Questions for Listbox problem (Help Appreciated)

[jvicchio]

Hello everyone

I work in the insurance industry and designed a database and I am in charge of validating medical providers and track ownership of the clinics. I have 12 tables tracking different aspects of ownership, doctors etc.

Recently I came up with a tracking system to differentiate the different facilities IE hospital, Diagnostic. I saw the table addition with the Lookup Wizard but I don't think I will be able to query. I also did some searches and saw some form methods but I do not understand them completely.

Could I create a table that has the 5 different clinic types (hospital, diagnostic etc.) and link it to another table? Would this allow me to query "Hospital" to show all clinics that are Hospitals?

Thank you in advance for your assistance

 

[burrina]

Welcome. Yes you can. Google a search for this. link by Primary Key/Fake Key.
Then add the tables into your query and filter.

HTH

 

[jvicchio]

Thank you

I have a State table that includes all the State abbreviations which I think will do the same thing.

I was just reading some threads and people indicated it was unwise to have Lookups in the table itself and to use Forms. The form method confuses me right now and I haven't done enough reading related to forms yet.

Just wanted to verify that using a seperate Table would not bog down my database.

 

[jdraw]

I work in the insurance industry and designed a database ......

Can you describe in plain English WHAT the business is about? Please identify the things you'll be using/tracking/monitoring in your database.

Also can you take a screen capture of your Relationships window;
save it as a jpg and then make it a zip and attach it ro your post.

You may also be interested in this concept sub/supertype

 

[jvicchio]

I am sorry I did not post more information. I did not want to ramble... I handle validating medical clinics ownership. I track locations, doctors, workers compensation, bank accounts, property owners... etc.

I have attached a snapshot of my relationships for you to review.

Basically I store the data and I monitor the new clinics. I also have one report I have created so far but I use a lot of queries to identify number of clinics on a street, number of doctors in a clinic, number of clinics a doctor works at etc.

I am adding the new field because I want to be able to track, hospital facility and I can pull all the facilities that are hospitals etc.

One issue I have identified with my thinking so far is that some facilities are a surgery center and a hospital or something along those lines.

I hope this helps and again thank you

 

[jdraw]

I was hoping for a description of the "business" involved in a format similar to this:

From RogersAccessLibrary

Narrative
ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

see tutorial here: http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Did you look at the sub/supertype info in previous post?

You might want to generalize to Facility and then FacilityType.

 

[jvicchio]

I have reviewed the subtype and it appears I have designed something similar. I have the Tax Identification which is the clinic identification kind of like a person. The 11 other tables are subtypes to the clinics.

One issue I am having currently is adding a field to indicate if the facility is a Chiropractor or radiologist but I want to have a combo field if they do both X-Rays and MRI's.
I can do the one field but I am unable to do two at once.

Make a Facility/Type table?

I haven't made a statement like this before.

Auto Insurance has required a tracking system of all medical providers that are billing our policy holders and claimants. In the State of Florida, medical providers are supposed to be properly licensed and by law, owned by a medical professional(no layman ownership). I track all offices by their tax identification numbers as my primary key and this number never changes. I also track, company name, address, city, state, zip, phone number, website, google map, billing location, department of corporation registered owner, manager and medical license numbers, violations on medical licenses, expiration of license, pharmacy license, massage license, pain management license, workers compensation company, workers compensation policy, medical director name, medical director license, and banking information. This information is re-checked every 6 months to ensure it is still current and the Tax Identification Number is valid and ensure ownership hasn't changed.

 

[smig]

If a clinic can be both Chiropractor and radiologist you can do it in two ways:
1. Create two Yes/No fields (Chiropractor, Radiologist) in the Clinics table.
2. Create a linking ManyToMany table:
ClinicID, TypeOfClinicID
The tblTypeOfClinics table will hold Chiropractor and radiologist types, and will be able to hold more types of clinics in the future.

I prefer the second method as it will allow you to add more types in the future.
You can also look for a specific type by selecting the one that you want selecting it from the tblTypeOfClinics table.

 

[jvicchio]

I did the latter with the extra table. I have one table linked via my Primary key (Tax Identification) to store the attribute and another stand alone table storing all the different types of clinics (28 now).

Thank you for all your input