Selasa, 30 Juni 2015

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

 

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 :

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@... [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)

.

__,_._,___

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

 

correction:

- QCatID, autonumber, PK

On 6/30/2015 1:47 PM, crystal 8 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 ~




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
 


__._,_.___

Posted by: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

.

__,_._,___

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
 

__._,_.___

Posted by: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)

.

__,_._,___

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

 

Hi Patty,

in the designer. click in an empty field cell

MyCalculatedFieldname: iif( nz([fieldname],"") <> "", value-if-true, value-if-false)

WHERE
[fieldname] is a text field -- if another data type, change optional second parameter of NZ

so, applying this to your data:

Diet Educ: iif( nz([fieldname],"") = "my specific result" , value-if-true, value-if-false)

hopefully you can draw an analogy to what you need

Warm Regards.
Crystal

Tool: Document Query Calculated Fields: Access to Excel. Music by JD Live (cc)
https://www.youtube.com/watch?v=vS8KfHU6L90
 - SQL to show calculated fields in queries, use CopyFromRecordset to put in Excel and then format


~ be awesome today ~

On 6/30/2015 12:19 PM, pattykf@cox.net [MS_Access_Professionals] wrote:

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: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

.

__,_._,___

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

 

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: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

.

__,_._,___

[MS_AccessPros] question about adding a column to a query

 

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 (1)

.

__,_._,___

Re: [MS_AccessPros] Msaccess and solid state disk (SSD)

 

> "Good ones are much faster than cheap ones"

I can attest to that!  I bought a SSD to boot from and didn't notice any performance gain.  It was low-end on pricing and cost me a few days ... a developer friend came over who shows me how to do stuff.  He is deaf and when he get on my computer, he usually just types in code.   However, one day, he saw something I needed and didn't have and before I could tell him where to put it, it went to my boot drive and ran out all the space.  I took the SSD out, gave it to one of my kids, got another drive, reinstalled an operating system and all my programs & utilities, customized my applications, ... then I got to my data again, on different drives.

I have a slower boot drive now but it is BIG!

Warm Regards,
Crystal

Bad Relationships? Make them good by enforcing referential integrity
video Tip: Enforce Referential Integrity on Access Relationships (cc) closed-caption
http://www.youtube.com/watch?v=_zxxc9jzWEg

~ be awesome today ~


On 6/30/2015 8:46 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:
Richard-

It's better if you include the entire thread so everyone can see the conversation.  I've pasted the earlier stuff below.

So, you do not have the same problem as Onno?

If you have a decent SSD, it shouldn't matter what you put on it.  If critical data co-resides with Windows on the SSD, and Windows is using the SSD for paging, there could be some small impact, but it shouldn't be anything like Onno has reported.

Onno didn't mention the make and model of the SSD.  Good ones are much faster than cheap ones.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jun 30, 2015, at 4:17 PM, richard sturgeon rhsj209@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am trying to just run windows 8.1 On the ssd. I try to run everything else on the regular drive.


__._,_.___

Posted by: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

.

__,_._,___

Re: [MS_AccessPros] Msaccess and solid state disk (SSD)

 

Richard-


It's better if you include the entire thread so everyone can see the conversation.  I've pasted the earlier stuff below.

So, you do not have the same problem as Onno?

If you have a decent SSD, it shouldn't matter what you put on it.  If critical data co-resides with Windows on the SSD, and Windows is using the SSD for paging, there could be some small impact, but it shouldn't be anything like Onno has reported.

Onno didn't mention the make and model of the SSD.  Good ones are much faster than cheap ones.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jun 30, 2015, at 4:17 PM, richard sturgeon rhsj209@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am trying to just run windows 8.1 On the ssd. I try to run everything else on the regular drive.

__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)

.

__,_._,___

[MS_AccessPros] Re: Msaccess and solid state disk (SSD)

 

I am trying to just run windows 8.1 On the ssd. I try to run everything else on the regular drive.

__._,_.___

Posted by: richard sturgeon <rhsj209@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

.

__,_._,___

Re: [MS_AccessPros] Msaccess and solid state disk (SSD)

 

Onno-


First reaction: No WAY!

Are the hard disk and the SSD on the same system?  How much free space is there on the SSD?

When you ran your query tests, did you start from scratch both times?  In other words, start Access, open the database, run the query.

Also how much memory do you have on your computer and what else was running during each test?  It has been my experience that Access desktop performance is most highly impacted by the amount of memory available.  Access actually spends as little time as possible fetching stuff from the disk - it tries to do as much as possible in memory.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jun 30, 2015, at 12:32 PM, onno.knol@pbl.nl [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Dear pro's,

I recently put an SSD in my Computer. The OS (win7) is on that disk and the system starts remarkably faster.

 Now I thought that my msaccess application would profit from that, so I put it on my C:\ disk. I ran a query that invokes a user defined function (VBA) and ran on the original disk for 50 seconds. Result: on the SSD it runs 6 minutes!  Much slower!  Besides that  no performance improvements, so I switched back.What ere your experiences with SSD disks? Is it no good idea to put your database on it? Or are there other caveats that you should take care of.?

Is it possible or not?


Kind regards,


Onno Knol


__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

.

__,_._,___

[MS_AccessPros] Msaccess and solid state disk (SSD)

 

Dear pro's,

I recently put an SSD in my Computer. The OS (win7) is on that disk and the system starts remarkably faster.

 Now I thought that my msaccess application would profit from that, so I put it on my C:\ disk. I ran a query that invokes a user defined function (VBA) and ran on the original disk for 50 seconds. Result: on the SSD it runs 6 minutes!  Much slower!  Besides that  no performance improvements, so I switched back.What ere your experiences with SSD disks? Is it no good idea to put your database on it? Or are there other caveats that you should take care of.?

Is it possible or not?


Kind regards,


Onno Knol

__._,_.___

Posted by: onno.knol@pbl.nl
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

.

__,_._,___