ACCESS FEED: Re: [MS_AccessPros] removing duplicates in a query-Fixed

fixed the query, the timestamp needed create_timestamp to identify which timestamp it was dealing with

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

it asks for a date see pop up, no matter what I put in it the results are the same, so not sure why the pop up is showing.
Ideas?

thankx again

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

it is a date time so I will give it a shot,
I just got rerouted to a different query needed so I will need to come back to this in a bit,
will get back to you and let you know how it went as soon as I can
thank you as ever for all of your help and training.
I would guess your books are as good! going to look into them for more info
Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

In Access, the Date() function gives you today's date. But keep in mind that if you're comparing to dates as character strings, you'll either have to convert the string or the value from the Date() function. To get a comparison string from Date(), you could use Format(Date(), "yyyymmdd").

Is timestamp a date/time field, or is it text in format yyyymmdd? If date/time then you could do:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM (Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id) INNER JOIN [Encounter Last in period] ON Person_filter_New.person_id = [Encounter Last in period].LastOfperson_id
WHERE (((dbo_person.date_of_birth)>"19300101" And (dbo_person.date_of_birth)<"19970101") AND ((dbo_person.person_id) In (SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*401.*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0) AND (dbo_problem_list_data_.timestamp <= DateAdd("m", -6, Date())) ))));

If timestamp is a string, then do:

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 9, 2015, at 6:59 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I just realized on this one I need to limit the patients that have had a diagnosis greater than 6 months, so I need to find a way to use the dx date the field is dbo_problem_list_date.create.timestamp. I am not sure this there is a way to do at TODAY feature in Access, I have done it in excel but never tried in access

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

this is the same query except I changed the dx and the date of birth ranges
I would like the diagnosis description to show so that I can check my work with code limitations (dx code is 401.1 thru 401.9) so I am thinking the 401.* covers that but I do have diagnosis queries to write that have diagnosis ranges that are not so simple and will need to see the diagnosises to make sure that I have captured patients correctly. when I add the diagnosis description field it messes up the results so I know I am adding it incorrectly. this is the field and table: dbo_problem_list_date.problemdescription

this is the hypertension sql query

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM (Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id) INNER JOIN [Encounter Last in period] ON Person_filter_New.person_id = [Encounter Last in period].LastOfperson_id
WHERE (((dbo_person.date_of_birth)>"19300101" And (dbo_person.date_of_birth)<"19970101") AND ((dbo_person.person_id) In (SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*401.*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)))));

also for my learning, where in the code does it limit the return of patients to once only? I know it is working to only return them once but I can't figure out where.
thank you
Patty

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

that worked
the dbo_person is used to be able to sort on the Date of birth since I could not get the Perosn_filter_new date of birth that was converted to filter date of birth ranges. the person filter new, limits some people and the encounter last period was a way to eliminate some other features of inactive patients.
But the long and short of it is that this worked to limit the patients with 250 code to only show them once. I did not need the diagnosis code info to show, had it showing to check work and determine what was happening. but it is not needed once the query works

thank you so much for this, off to work on other diagnosis queries.

Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

It's not clear what the purpose of the INNER JOIN with dbo_person (I assume a table) and Person_filter_New (I assume a query) is supposed to do.

From your original query, it looked like you were using dbo_problem_list_data to find people with diabetic diagnosis. The subquery is supposed to return the person_id of all people where DiagnosisCode Like "*250.0*" and there is no date resolved. Your outer query should simply be returning the people in that list - further filtered for age.

Yes, I eliminated [Encounter Last In Period] - perhaps in error. If this is further filtering the people, then it needs to go back in, like this:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM (Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id)
INNER JOIN [Encounter Last in period] ON Person_filter_New.person_id = [Encounter Last in period].LastOfperson_id
WHERE ((dbo_person.date_of_birth)>"19400101" And (dbo_person.date_of_birth)<"19970101")
AND dbo_person.person_id IN
(SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*250.0*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)));

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 9, 2015, at 5:35 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

it returned more people than less people, something is weird here. when I looked at the design of the query it had no dx table or encounter limiting table anymore. I added back in dx and the dx was not limited to the 250.0* code. I am going to see if I can look at the code of the two and see what changed that I could adapt. I am thinking it is something little that is just missing.
Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

Ah, there's a person_id in both dbo_pers and Person_filter_new. This should fix it:

SELECT Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, Person_filter_New.date_of_birth AS DOB, dbo_person.date_of_birth
FROM Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id
WHERE ((dbo_person.date_of_birth)>"19400101" And (dbo_person.date_of_birth)<"19970101")
AND dbo_person.person_id IN
(SELECT person_id FROM
dbo_problem_list_data_
WHERE (((dbo_problem_list_data_.ProblemDescription)>"0") AND ((dbo_problem_list_data_.DiagnosisCode) Like "*250.0*") AND ((dbo_problem_list_data_.Resolved) Is Null Or (dbo_problem_list_data_.Resolved)=0)));

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 9, 2015, at 4:46 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

this is resulting in an error: