Thank you all for the feedback. I guess it would have been better if I stated what fields I have and what is included in the fields. I actually have tables that have the information already in them. They are actually check box/radiio button fields that returns a 1 if the box is checked or not. I then need to pull that information and match it up to patients with BMI's that are out of normal range. So if the BMI is above normal I am looking for a 1 to be in the table for Diet and a 1 to be in the table for Physical activity, (for one report- I need both to be checked off, for another report I need either of them to be checked off). At the moment I pull the data out by each category and take to excel and manipulate to match up to BMI's if normal, High or Low. I am just trying to see if I could do that in access instead.
I pull each of the categories separately in separate queries, based on a date range (data needs to be the last visit in the past 24 months),
At the moment I think this will remain the way until I learn more skills/queries. I will use the information you all shared to expand my knowledge and try to figure out the best way to do this going forward.
Thank you all again for all the feedback. It is very helpful, can't wait to learn some of these new things and see if I can find a faster way to do it
Have a wonderful evening, thank you
Patty
---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
Hi Patty,
adding on to what Duane said ...
perhaps your data is not properly structured. A common mistake is to create a field for everything you want to track instead of considering a related table.
For instance, if you have a 'checklist' (which could change!), instead of making a field for each item, make a table for the checklist items to track. For example:
* Questions
- QID, autonumber, PK
- QText, text, question
- QcatID, number long -- FK to question category so you can know what to autogenerate for answering
- IsActiv, yes/no -- is this an active question for autogenerating records for answers?
* QCategories
- QCat, autonumber, PK
- QCat, text, question category
Then you would make a related table to Patients (has PatientID) and ptQuestions (has ptQID) with the answer to each question. As data type is important, you may want to make different tables for answers if it is Yes/No, text, or number ... or learn how to handle all those answer types.
When you are figuring out what to separate, ask yourself : what describes the patient and what describes something else? What might might change? Do you need to know the date something changes? Do you need to know what it changed from?
Ask yourself a lot of questions and get the data structure right before continuing ... structuring data is an iterative process and the most important thing you will do. Right now, don't worry about how to make it work, just separate the fields that relate to each table as they should
Warm Regards,
Crystal
Learn Access Playlist on YouTube
http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA
~ be awesome today ~
adding on to what Duane said ...
perhaps your data is not properly structured. A common mistake is to create a field for everything you want to track instead of considering a related table.
For instance, if you have a 'checklist' (which could change!), instead of making a field for each item, make a table for the checklist items to track. For example:
* Questions
- QID, autonumber, PK
- QText, text, question
- QcatID, number long -- FK to question category so you can know what to autogenerate for answering
- IsActiv, yes/no -- is this an active question for autogenerating records for answers?
* QCategories
- QCat, autonumber, PK
- QCat, text, question category
Then you would make a related table to Patients (has PatientID) and ptQuestions (has ptQID) with the answer to each question. As data type is important, you may want to make different tables for answers if it is Yes/No, text, or number ... or learn how to handle all those answer types.
When you are figuring out what to separate, ask yourself : what describes the patient and what describes something else? What might might change? Do you need to know the date something changes? Do you need to know what it changed from?
Ask yourself a lot of questions and get the data structure right before continuing ... structuring data is an iterative process and the most important thing you will do. Right now, don't worry about how to make it work, just separate the fields that relate to each table as they should
Warm Regards,
Crystal
Learn Access Playlist on YouTube
http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA
~ be awesome today ~
On 6/30/2015 1:12 PM, Duane Hookom duanehookom@... [MS_Access_Professionals] wrote:
Patty,
Are you expecting to actually add a column/field to a table or simply create a calculated column in a query? You shouldn't be adding fields to store data that can be calculated on the fly in a query.
Also, if you have field names like "Diet" and "Physical Activity" then it suggests your table isn't normalized. You might want to provide your table and field names and ask for someone to review.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
Date: Tue, 30 Jun 2015 11:19:12 -0700
I am not sure this is possible or not but thought I would ask
I would like to pull in data from a table, then have it add a couple of columns so if say Diet is not blank(or has a specific result) it would put a 1 in the column field for Diet Educ, if Physical activity is not blank(or has a specific result) then it would put a 1 in the PA educ column and if they have both then it would put a 2 in the both educ column.
Is that possible and if so what is it called, or how would I write such a query?
Appreciate the help.
Patty
__._,_.___
Posted by: pattykf@cox.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
.
__,_._,___
