Thread: Managing Data based on dropdown
06-14-14, 14:09 #1Registered User
- Join Date
- Jun 2014
Unanswered: Managing Data based on dropdown
This may seem like a simple problem (I hope it is) but I cannot get this to work for me. I haven't had to use Access in many years and was just tasked with a project for my company.
Ideally I would like a dropdown with the various types of Admissions (ER, Admission, Readmission, Nursing Facility..etc) I need to be able to total each of these types based on a date range. I then have various calculations that I need to perform based on each of these totals. Grouping each of the admission types gets me the total I need but how can I call that value to another query to perform the calculation?
My table is currently laid out as such:
AdmID - Autonumber
PatientID - Number
AdmType - Dropdown (linked from another table)
AdmDate - Date/Time
We are using Access 2003 (I know it is outdated but we are a not-for-profit and until a time we can't use it I am sad to say it will stay that way).
Worse case scenario I could put a column for each type of admission and try query by date but that seems much more difficult.
06-14-14, 23:46 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
First off, having a column (Field) for each type of admission would be grossly inappropriate; you'd be violating normalization rules. Your use of a Combobox (the 'dropdown' you speak of) is perfectly fine; you'd simply Bind it to the Field named AdmType in the Table the Form is based on.
I'm a little hazy as to the rest of your question, in particular, but in designing Reports, if you need data from multiple Tables, you simply create a Query based on the Tables and go from there.
And there's no need to apologize for using Access 2003! It doesn't have all the bells and whistles of versions 2007/2010/2013, but it is, in my experience, one of the most stable versions of Access, and is more than up to the job of creating well-functioning databases! To be honest, I only upgraded to 2007 in order to be able to answer questions here, and on other forums, related to the new features in 2007!
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
06-15-14, 00:54 #3Registered User
- Join Date
- Jun 2014
Thanks for the reply :-)
So I have the table designed and the form to input the information as necessary, this all works just fine. My problem, and the hazy portion of my post, is that I need to perform some arithmetic based on the results of this table.
AdmID: - AutoNumber (Primary)
PatientID: - Number Linked to Patient Table
AdmType: - Combobox (ER Visit, Admission, Readmission, SNF Admission...)
AdmDate: - Date/Time - Short Date Format
AdmNote: - Memo
Heres the scenario, I enter various admission into the table utilizing the form and obviously I get all these records for the AdmType with an associated date. The report I need to generate every month would include a detailed breakdown of each of the admission types based on a user specified date range and several calculations performed on this data. The report data would look something like this.
Admission Type Raw # #/PT #/1000
ER Visit 7 0.003 0.00003
Admission 43 0.09 0.0015
So each item in my combobox would need to be displayed as shown above then the total number of items per admission type in the date range. Then the # of admission divided by the total patient count (not date range specific) and then the total admission divided by 1000. (yes this is a very random number but for some reason the hospital seems to think they need that metric.)
I did some reading and I am thinking that perhaps a cross tab query *MAY* be along the lines of what I'm looking for but to be honest I am totally lost. I have an associates in CompSci and went to become a Paramedic lol, I don't even know the string of events to land me in this mess.
Thanks for everything.