Simply query question

[Gamefewel]

THis is pretty simple and don't know why it is not working.

I am building a database for a Dr who wants to keep track of lab results for
patients. I can't get just a simple query to work for more than and less
than.

I also am trying to build a query for Blood Pressurea and since it is a
fraction was putting it into 2 columns, one for Systolic and one for
Diastolic.
When I run the criteria >=150 in Systolic and <=95 both on the same criteria
line, it is giving me all records that are more than or equal to 150 OR less
than or equal to 95, not over or equal to 150 AND less than or equal to 95.
The other one I am trying to run in <=7 for less than or equal to 7 and it
either will return nothing or all.

I have also tried running it without using the = and using the higher or
lower number and it is still not working.

THanks, hope his is not too simple for you to help with.

 

[Clifford Bass]

Hi,

How about you post the SQL of the query? To view the SQL change to SQL
view while viewing the query.

Clifford Bass

 

[KARL DEWEY]

When I run the criteria >=150 in Systolic and <=95 both on the same criteria
line, it is giving me all records that are more than or equal to 150 OR less
than or equal to 95, not over or equal to 150 AND less than or equal to 95.

Open the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

By posting the SQL others can see what Access sees and what you may be
missing.

 

[Gamefewel]

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE (("Where [Systolic]">"135" And [Diastolic]<"95"))
ORDER BY Patients.[Last Name], Patients.[First Name];


Thanks to both of you.

 

[John W. Vinson]

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE (("Where [Systolic]">"135" And [Diastolic]<"95"))
ORDER BY Patients.[Last Name], Patients.[First Name];


Thanks to both of you.

You don't include the WHERE keyword on the criteria line in the query builder,
as you have apparently done!

Assuming that the fields Systolic and Diastolic are Number fields (which they
certainly should be) try

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE [Systolic]>135 And [Diastolic]<95
ORDER BY Patients.[Last Name], Patients.[First Name];

 

[Gamefewel]

I copied that and replaced the SQL with it. I then got a parameter box
asking for what Lab. That is not what I want.
but thanks

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE (("Where [Systolic]">"135" And [Diastolic]<"95"))
ORDER BY Patients.[Last Name], Patients.[First Name];


Thanks to both of you.

You don't include the WHERE keyword on the criteria line in the query builder,
as you have apparently done!

Assuming that the fields Systolic and Diastolic are Number fields (which they
certainly should be) try

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE [Systolic]>135 And [Diastolic]<95
ORDER BY Patients.[Last Name], Patients.[First Name];

 

[John W. Vinson]

I copied that and replaced the SQL with it. I then got a parameter box
asking for what Lab. That is not what I want.

The newsgroup post will have line breaks. These should be removed from the SQL
in the query window.

 

[Gamefewel]

I took out the line break and got "Lab Results. Systolic" in the Parameter Box

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE (("Where [Systolic]">"135" And [Diastolic]<"95"))
ORDER BY Patients.[Last Name], Patients.[First Name];


Thanks to both of you.

You don't include the WHERE keyword on the criteria line in the query builder,
as you have apparently done!

Assuming that the fields Systolic and Diastolic are Number fields (which they
certainly should be) try

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE [Systolic]>135 And [Diastolic]<95
ORDER BY Patients.[Last Name], Patients.[First Name];

 

[Gamefewel]

Okay, I did get a query to run and this is the result. I still am getting
OR, not AND

MRN Last Name First Name DOB Phone Systolic Diastolic
M5555500000 Adams Jane 01/01/2001 (217) 999-9999 137 102
M111111111 Carroll Mary 02/02/1951 (217) 111-1111 140 90
M111111111 Carroll Mary 02/02/1951 (217) 111-1111 95 72
M0000555555 Doe John 01/11/2001 (217) 000-0000 200 90
M2222222222 Smith Carol 05/24/1982 (217)-999-0000 180 120

The SQL is this. Thanks

SELECT Patients.MRN, Patients.[Last Name], Patients.[First Name],
Patients.DOB, Patients.Phone, [Lab Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.[MRN] = [Lab Results].[MRN]
WHERE [Systolic]>"135" AND [Diastolic]<"95"
ORDER BY Patients.[Last Name], Patients.[First Name];

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE (("Where [Systolic]">"135" And [Diastolic]<"95"))
ORDER BY Patients.[Last Name], Patients.[First Name];


Thanks to both of you.

You don't include the WHERE keyword on the criteria line in the query builder,
as you have apparently done!

Assuming that the fields Systolic and Diastolic are Number fields (which they
certainly should be) try

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE [Systolic]>135 And [Diastolic]<95
ORDER BY Patients.[Last Name], Patients.[First Name];

 

[John Spencer]

IF the Systolic and Diastolic fields are text fields that is a correct result
for TEXT fields.

If the fields are number fields then try removing the quotes.

SELECT Patients.MRN, Patients.[Last Name], Patients.[First Name],
Patients.DOB, Patients.Phone, [Lab Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.[MRN] = [Lab Results].[MRN]
WHERE [Systolic]>135 AND [Diastolic]<95
ORDER BY Patients.[Last Name], Patients.[First Name]

If the fields are actually text you will have to force a conversion of the
text to number type. And if the fields can contain null values (blank), you
will need to handle that so the safest thing to do would be somethng like the
following

SELECT Patients.MRN, Patients.[Last Name], Patients.[First Name],
Patients.DOB, Patients.Phone, [Lab Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.[MRN] = [Lab Results].[MRN]
WHERE Val(Nz([Systolic],0))>135 AND Val(Nz([Diastolic],100))<95
ORDER BY Patients.[Last Name], Patients.[First Name]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Okay, I did get a query to run and this is the result. I still am getting
OR, not AND

MRN Last Name First Name DOB Phone Systolic Diastolic
M5555500000 Adams Jane 01/01/2001 (217) 999-9999 137 102
M111111111 Carroll Mary 02/02/1951 (217) 111-1111 140 90
M111111111 Carroll Mary 02/02/1951 (217) 111-1111 95 72
M0000555555 Doe John 01/11/2001 (217) 000-0000 200 90
M2222222222 Smith Carol 05/24/1982 (217)-999-0000 180 120

The SQL is this. Thanks

SELECT Patients.MRN, Patients.[Last Name], Patients.[First Name],
Patients.DOB, Patients.Phone, [Lab Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.[MRN] = [Lab Results].[MRN]
WHERE [Systolic]>"135" AND [Diastolic]<"95"
ORDER BY Patients.[Last Name], Patients.[First Name];

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE (("Where [Systolic]">"135" And [Diastolic]<"95"))
ORDER BY Patients.[Last Name], Patients.[First Name];


Thanks to both of you.

You don't include the WHERE keyword on the criteria line in the query builder,
as you have apparently done!

Assuming that the fields Systolic and Diastolic are Number fields (which they
certainly should be) try

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE [Systolic]>135 And [Diastolic]<95
ORDER BY Patients.[Last Name], Patients.[First Name];

 

[Gamefewel]

Thank you! They were text so i changed them to numbers, took out the "" and
it works like a charm. It had been years since I had done this so it just
kind of skipped my mind. Thanks again.

I took out the line break and got "Lab Results. Systolic" in the Parameter Box

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE (("Where [Systolic]">"135" And [Diastolic]<"95"))
ORDER BY Patients.[Last Name], Patients.[First Name];


Thanks to both of you.

You don't include the WHERE keyword on the criteria line in the query builder,
as you have apparently done!

Assuming that the fields Systolic and Diastolic are Number fields (which they
certainly should be) try

SELECT Patients.[Last Name], Patients.[First Name], Patients.Phone, [Lab
Results].Systolic, [Lab Results].Diastolic
FROM Patients INNER JOIN [Lab Results] ON Patients.MRN = [Lab Results].MRN
WHERE [Systolic]>135 And [Diastolic]<95
ORDER BY Patients.[Last Name], Patients.[First Name];