ACCESS FEED: Re: [MS_AccessPros] question about adding a column to a query

Selasa, 30 Juni 2015

Re: [MS_AccessPros] question about adding a column to a query

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 ~

On 6/30/2015 1:12 PM, Duane Hookom duanehookom@hotmail.com [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