Re: [MS_AccessPros] Weird symbols in query result

 

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];

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 

http://www.viescas.com/ 

(Paris, France)

On Feb 4, 2015, at 5:06 PM, pattykf@cox.net [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 (2)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___