access query help

[hispeaches]

Hello All,



I am working on a query and having trouble completing what I need to do.

Tables: I have 2 tables - Table 1 has patients that were seen at the ue with dates and times of appointments (07:01, 7:02)
Table 2 has appointment dates and hour (07:00, 08:00) available at practices.


I need to build a query that will tell me which visits could have been made at the practice instead of the ue.


My Tables do not have relationships nor do they have primary keys. I joined date to date and time to hour and it gives me the exact match now I want to see which patients could have been seen at practice. IE: appt at ue 07:35 appt available at practice at 07:00.


Thanks in advance for your help!

[rpeare]

have you got an example of your two datasets and what your desired result would be based on those two datasets?

I am not sure I am understanding your request.

[orange]

I'm not understanding your request either.

Readers do not appreciate your ue and practice--so perhaps you could tell us what these are.
My guess is they are 2 locations offering some sort of services.
Your example
"Table 2 has appointment dates and hour (07:00, 08:00) available at practices."
doesn't show dates??
Every table in relational database should have a Primary key.

Perhaps things would be clear if you described " a day at my work" that indicated how this tables relate to one another.

[hispeaches]

have you got an example of your two datasets and what your desired result would be based on those two datasets?

I am not sure I am understanding your request.

Ok, UE is a specialty practice and then we have several Primary Care Practices.


sample.pdf

[Rachel Emmons]

If I'm understanding your question correctly, it sounds like you have two sets of data and the only thing that identifies the location of the appointment time is which table the record is stored in, correct? My first thought would be that the data should all be in one table with an additional field that specifies the location of the appointment. If that is not possible (because you do not have that kind of control over the data for example), another suggestion (and this is pretty clunky so anyone, please feel free to suggest an alternative method) would be to create one query for each set of data with an expression field that Literally adds the name of the location as an additional field (Location: "UE" in one query and Location: "Practice" in the other query, and then use those 2 queries as the table source for your final query. I'm not sure if that explanation made as much sense as it did in my head, so feel free to ask questions to clarify. Also, I have not tested this, so it may not work as cleanly as I envisioned it to...

Hope this helps you, good luck!

[orange]

hispeaches,

Please tell us about a day at the office in simple English so we can clearly understand WHAT you are working with.
Your tables may be exactly what is needed, but until readers understand the requirement in simple terms any talk about tables is premature. Just plain facts, no database jargon.

[hispeaches]

hispeaches,

Please tell us about a day at the office in simple English so we can clearly understand WHAT you are working with.
Your tables may be exactly what is needed, but until readers understand the requirement in simple terms any talk about tables is premature. Just plain facts, no database jargon.

If I told you what a day at the office was like we would be here all day. :-)

Plain and simple I need to figure out what appointments that were made at the ue that could have been scheduled at a practice. The only report that I have is one that shows only hours no minutes 07:00 but the other report I have shows hours & minutes so, I need to say if this appt was scheduled at 07:10 and I had 7 available appts at 07:00, it could have been scheduled at the practice.

[orange]

If you want readers to help you in resolving your issue, they need to understand what it is you are trying to do.
Because your current set up doesn't provide the info you need, readers can only guess what you need.
Said differently -if you can't describe what you need in simple terms, it probably means no one can design a solution.

I need to figure out what appointments that were made at the ue that could have been scheduled at a practice.

What is the difference between ue and practice?
Are some services unique to a location?

Why do some details include hours and minutes and others only hours?
If you need hours and minutes OUT of the database, then you'll have to ensure it is entered or calculated n some manner.

The database won't tell you anything you didn't tell it whether direct or through some algorithm.

[hispeaches]

If you want readers to help you in resolving your issue, they need to understand what it is you are trying to do.
Because your current set up doesn't provide the info you need, readers can only guess what you need.
Said differently -if you can't describe what you need in simple terms, it probably means no one can design a solution.


What is the difference between ue and practice?
Are some services unique to a location?

Why do some details include hours and minutes and others only hours? because that's the only report that i can get.
If you need hours and minutes OUT of the database, then you'll have to ensure it is entered or calculated n some manner.

The database won't tell you anything you didn't tell it whether direct or through some algorithm.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Orange: I'm sorry. I used to be an avid access user however, I switched jobs 4 years ago and am now having a time catching back up. It's very hard to explain this to someone who is very seasoned in access especially through typing
I described UE in an earlier post, it is a specialty practice IE: gastroenterology and the practices are primary care physician offices. I need to find out which UE appts could have been scheduled with my primary care office. Patients in our primary office are being told to go to the UE and I need to figure out what could have been scheduled with the primary care.

CMG patient list shows all UE visits with date and time (07:15, 08:30) while T_Schedule Utilization 2 shows dates with hour only hours ie: 07:00, 08:00, I have a column that shows me how many appointments were available in the hour time frame. Practice name fields show on each table as well as the same date (month of October).

To answer your questions:

What is the difference between UE and practice? specialty practice vs primary care
Are some services unique to a location? Yes

Why do some details include hours and minutes and others only hours? because that's the only report that I can get, I am at the mercy of canned reports.
If you need hours and minutes OUT of the database, then you'll have to ensure it is entered or calculated n some manner. I did this, however, with the calculation I am finding i can't figure out how to get it to tell me whether or not the UE time falls in the hour of the practice time.


I did put a word document up above so you could see the information I have in my tables.

Does this make better sense? Again I am sorry!

[orange]

Here is a draft model of what I think is an overview of your business for consideration.
I have not included the appointment and availability info.

With the suggested tables it seems you could get the assigned facility and time and also the facility availability.
I'm not sure what info you have currently nor how you want to proceed. This for review only and may not represent your requirement.

[hispeaches]

UCC-Schedule_Backup.zipattached is a copy of my database...

[orange]

I'm looking at the database.
What do the fields represent?-- please provide a 1 liner for each field in each table.
In plain English, is this suppose to be an operational database. That is, is your business relying on it?

Did you see the draft model I posted? Any and all comments welcome.

The model is a representation of a business in which
-there are a number of Patients
-these Patients present to you or your business with some potential Medical Issue
-there is a list of real and potential Medical Issues
-there is a list of Facilities that provide Services
-there is a list which identifies the Facility that should provide Service to a Patient with a specific Medical Issue
(I'm thinking here like triage who directs the Patient to the most appropriate Service Facility)
-there is a list of Available time slots(whatever the name) for each Facility
-there is a list of actual patient visits identifying the Facility, DateTime, other Visit info (diagnosis/recommendation/prescription......)

-there is a list of required reports or other info that must be outputted from the data captured.

This not necessarily what you have or what you need. It is a "strawman" to help identify what it is you require.
It's a starting point to articulate the requirements of your proposed database.

What exactly is your role with the proposed database?

You have 457 Distinct PrinDXd ---are these official names/descriptions?
And 33 Distinct SERVICE AREAs.

Good luck with your project.

[hispeaches]

Orange -

Table - CMG patient list - this is patients seen at UE
Patient DOB - this isn't really needed
Visit Date - date of visit to ue
admit time - time of apt
this database is only for me to run the reports they asked me for. I thought putting it in access would be easier than excel. I figured I could keep adding the info to the table and run.
PCP phys group- is the primary practice they belong too
Printdxd - not needed - I really need to remove it


Table Schedule Utilization
Service Area - practice
Date: Date of appt
Hour - hour of apt
available open - how many appts for the day
booked openings - how many appts booked
booked apts - total books (subtract from booked openings to get available appts)
Utilization % - isn't needed
Irregular appts - not needed
unavailable hours - not needed

this database is for me only... I figured I could build the report and then just load future items to it. My role is to provide what visits could have been scheduled with the primary care physician office instead of the UE. I need the date to train my directors.

[orange]

So the data for this database is extracted from some other operational database(s) and provided to you in some format at some regular times?
Is VisitDate and ApptTime just 2 fields to describe the date and time of a visit?

I tested a new field VisitDtTm as Date/Time data type and populated it with VisitDate + ApptTime.
Puts the info in 1 field and may simplify some calculation.

Is it possible that you will be asked to provide additional info re Patient, PCP phys group, Printdxd???

If the operational database is accessible through ODBC , you might be able to get the info you need to report directly from the main database via Access, or using the tools of the main database you might be able to design a report that would provide that info directly without extract and download to Access/Excel. Just a thought.

Service Area seems to be Facility/Location??

From a business process perspective, is there someone/function to do some sort of initial assessment to send patient(s) to the appropriate Service Area (primary Care) in order that UE is not processing "the wrong patients" (my term rather than mis-directed). Is that practical/feasible as an option for better utilization??

I'll look into the calculations.

Good luck.

[hispeaches]

So the data for this database is extracted from some other operational database(s) and provided to you in some format at some regular times? Yes
Is VisitDate and ApptTime just 2 fields to describe the date and time of a visit? Yes

Is it possible that you will be asked to provide additional info re Patient, PCP phys group, Printdxd??? no.. pcp physician group is there to match to service area

If the operational database is accessible through ODBC , you might be able to get the info you need to report directly from the main database via Access, or using the tools of the main database you might be able to design a report that would provide that info directly without extract and download to Access/Excel. Just a thought. i wish it was however like i said they are canned reports and i just pull them as needed or requested.

Service Area seems to be Facility/Location?? Yes, correct

From a business process perspective, is there someone/function to do some sort of initial assessment to send patient(s) to the appropriate Service Area (primary Care) in order that UE is not processing "the wrong patients" (my term rather than mis-directed). Is that practical/feasible as an option for better utilization?? Yes however i need this data in order to present proof that this is a training issue.