Patty-
No worries!
The #Error means there was a problem with the DCount. If person_nbr is text then it would require quotes around the last argument:
DCount("Person_nbr", "[NCM_non face to face]", "Person_nbr = '" & [person_nbr] & "'") AS CountOfperson_nbr
I also forgot the brackets around the name.
Could take a while because it has to do the count for each row, and if you have thousands of rows and no index on person_nbr, that could force you to take a lunch break!
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 Feb 4, 2015, at 5:36 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I looked back at the one that worked and I had less fields once i took the other fields out it worked. SO sorry to bug you
Patty
---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :
Patty
---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :
OH my that caused something that the last column showed #error and it kept cycling and cycling. I had to force it back to sql land to get it to stop.
Mine actually worked in a different query with the same fields except I had sorted for different data to pull in the intervention_type field.
what i did is built two queries, one that showed all the interventions that are considered face to face and then another query that showed all the Non face to face encounters. After that I did a count query (i actually needed to have the information for all but also needed a count for each patient too), the count query for the face to face worked just fine, but when I did the same thing for the non face to face I got symbols in two columns
Patty
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Mine actually worked in a different query with the same fields except I had sorted for different data to pull in the intervention_type field.
what i did is built two queries, one that showed all the interventions that are considered face to face and then another query that showed all the Non face to face encounters. After that I did a count query (i actually needed to have the information for all but also needed a count for each patient too), the count query for the face to face worked just fine, but when I did the same thing for the non face to face I got symbols in two columns
Patty
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Patty-
I would bet one or more of your fields are memo data type. When you include a Memo field in a GROUP BY, strange things happen because the GROUP BY forces Access to build a temporary index. Index entries can contain only up to 255 characters, so if some of your memo fields have more data than that, they'll get garbled on the output. Also when you group on all the fields, you'll probably only get a count of 1 for each row.
Try this instead:
SELECT [NCM_non face to face].person_nbr, [NCM_non face to face].last_name, [NCM_non face to face].first_name, [NCM_non face to face].date_of_birth, [NCM_non face to face].intervention_type, [NCM_non face to face].intervention_date, [NCM_non face to face].LastOfinsurance1, [NCM_non face to face].LastOfinsurance2, [NCM_non face to face].LastOfexclude_from_reporting, [NCM_non face to face].LastOfPCP_DBP, [NCM_non face to face].description, [NCM_non face to face].expired_ind, [NCM_non face to face].expired_date, DCount("Person_nbr", "[NCM_non face to face]", "Person_nbr = " & person_nbr) AS CountOfperson_nbr
FROM [NCM_non face to face];
FROM [NCM_non face to face];
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 Feb 4, 2015, at 5:06 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:I wrote a query, ran it without the count query added, but when I added the total field the datasheet suddenly had symbols in two of the columns instead of the data. I am trying to see all the patients that have had a non face to face encounter (already determined in a earlier query), their specific info some of the status indicators and then the number of encounters that they had (each line would be an encounter in the datasheeet prior to the count being added). If this does not make since please let me know
SELECT [NCM_non face to face].person_nbr, [NCM_non face to face].last_name, [NCM_non face to face].first_name, [NCM_non face to face].date_of_birth, [NCM_non face to face].intervention_type, [NCM_non face to face].intervention_date, [NCM_non face to face].LastOfinsurance1, [NCM_non face to face].LastOfinsurance2, [NCM_non face to face].LastOfexclude_from_reporting, [NCM_non face to face].LastOfPCP_DBP, [NCM_non face to face].description, [NCM_non face to face].expired_ind, [NCM_non face to face].expired_date, Count([NCM_non face to face].person_nbr) AS CountOfperson_nbr
FROM [NCM_non face to face]
GROUP BY [NCM_non face to face].person_nbr, [NCM_non face to face].last_name, [NCM_non face to face].first_name, [NCM_non face to face].date_of_birth, [NCM_non face to face].intervention_type, [NCM_non face to face].intervention_date, [NCM_non face to face].LastOfinsurance1, [NCM_non face to face].LastOfinsurance2, [NCM_non face to face].LastOfexclude_from_reporting, [NCM_non face to face].LastOfPCP_DBP, [NCM_non face to face].description, [NCM_non face to face].expired_ind, [NCM_non face to face].expired_date;
thank you for assisting
Patty
__._,_.___
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 (5) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar