Query selecting those players who played under par

[M.T.]

Hello,

I have the following 3 fields in an Access 2 query

Under: ([Nett1]-[CCR1])
Sort Ascending
Criteria < 0

CCR1: DLookUp("Venues.CCR","Venues","VenueID=" & 4)

Nett1
File: Players
Criteria > 0
defined as Integer

I wanted to calculate their nett score for day 1 (nett1) minus the course
rating (ccr1)

The values for "under" range from -9 to any number over zero.


Despite using the criteria for "under" being any value under zero, I am
getting players all players from -9 to -1 correctly, but the query gives
the players with 0, 1 and 2.

I want to ignore any "values" of zero or greater.

Thanking you,

Margaret

 

[Jason Lepack]

Are the criteria for Nett1 and Under on the same row? If they are on
different rows then they are compared using the "OR" function and not
the "AND"

If that isn't it then go into Query Designer and select SQL View and
copy that and post it here and we'll fix it right up for you.

Cheers,
Jason Lepack

 

[M.T.]

The SQL is

SELECT DISTINCTROW ([Nett1]-[CCR1]) AS Under, Players.Gross1, Players.Div1,
Players.Nett1, DLookUp("Venues.CCR","Venues","VenueID=" & 1) AS CCR1,
DLookUp("ClubID","Venues","VenueID=" & [div1]) AS Venue1, Players.Surname,
Players.FirstName, Players.Initials, Players.ClubID, Players.Hcp1
FROM Players, Venues
WHERE (((([Nett1]-[CCR1]))<0) AND (Players.Gross1>0) AND (Players.Nett1>0))
ORDER BY ([Nett1]-[CCR1]);

Thanks Jason.

Margaret

Are the criteria for Nett1 and Under on the same row? If they are on
different rows then they are compared using the "OR" function and not
the "AND"

If that isn't it then go into Query Designer and select SQL View and
copy that and post it here and we'll fix it right up for you.

Cheers,
Jason Lepack

Hello,

I have the following 3 fields in an Access 2 query

Under: ([Nett1]-[CCR1])
Sort Ascending
Criteria < 0

CCR1: DLookUp("Venues.CCR","Venues","VenueID=" & 4)

Nett1
File: Players
Criteria > 0
defined as Integer

I wanted to calculate their nett score for day 1 (nett1) minus the course
rating (ccr1)

The values for "under" range from -9 to any number over zero.

Despite using the criteria for "under" being any value under zero, I am
getting players all players from -9 to -1 correctly, but the query gives
the players with 0, 1 and 2.

I want to ignore any "values" of zero or greater.

Thanking you,

Margaret

 

[Jason Lepack]

Try this:

SELECT DISTINCTROW ([Nett1]-[CCR1]) AS Under, Players.Gross1,
Players.Div1,
Players.Nett1, DLookUp("Venues.CCR","Venues","VenueID=" & 1) AS CCR1,
DLookUp("ClubID","Venues","VenueID=" & [div1]) AS Venue1,
Players.Surname,
Players.FirstName, Players.Initials, Players.ClubID, Players.Hcp1
FROM Players
WHERE (((([Nett1]-[CCR1]))<0) AND (Players.Gross1>0) AND
(Players.Nett1>0))
ORDER BY ([Nett1]-[CCR1]);

What relates the players table to the venues table?

The SQL is

SELECT DISTINCTROW ([Nett1]-[CCR1]) AS Under, Players.Gross1, Players.Div1,
Players.Nett1, DLookUp("Venues.CCR","Venues","VenueID=" & 1) AS CCR1,
DLookUp("ClubID","Venues","VenueID=" & [div1]) AS Venue1, Players.Surname,
Players.FirstName, Players.Initials, Players.ClubID, Players.Hcp1
FROM Players, Venues
WHERE (((([Nett1]-[CCR1]))<0) AND (Players.Gross1>0) AND (Players.Nett1>0))
ORDER BY ([Nett1]-[CCR1]);

Thanks Jason.

Margaret

Are the criteria for Nett1 and Under on the same row? If they are on
different rows then they are compared using the "OR" function and not
the "AND"

If that isn't it then go into Query Designer and select SQL View and
copy that and post it here and we'll fix it right up for you.

Cheers,
Jason Lepack

Hello,
I have the following 3 fields in an Access 2 query
Under: ([Nett1]-[CCR1])
Sort Ascending
Criteria < 0
CCR1: DLookUp("Venues.CCR","Venues","VenueID=" & 4)
Nett1
File: Players
Criteria > 0
defined as Integer
I wanted to calculate their nett score for day 1 (nett1) minus the course
rating (ccr1)
The values for "under" range from -9 to any number over zero.
Despite using the criteria for "under" being any value under zero, I am
getting players all players from -9 to -1 correctly, but the query gives
the players with 0, 1 and 2.
I want to ignore any "values" of zero or greater.
Thanking you,
Margaret- Hide quoted text -

- Show quoted text -

 

[M.T.]

Jason,

The Players have a division for day 1 called Div1
The Venues have a Division and Day fields.

If I connect the Players division with the venues division AND Venues.Day
=1:

SELECT DISTINCTROW ([Nett1]-[CCR1]) AS Under, Players.Gross1, Players.Div1,
Players.Nett1, DLookUp("CCR","Venues","VenueID=" & 1) AS CCR1,
DLookUp("ClubID","Venues","VenueID=" & [div1]) AS Venue1, Players.Surname,
Players.FirstName, Players.Initials, Players.ClubID, Players.Hcp1
FROM Players INNER JOIN Venues ON Players.Div1 = Venues.Division
WHERE (((([Nett1]-[CCR1]))<0) AND (Players.Gross1>0) AND (Players.Nett1>0)
AND (Venues.Day=1))
ORDER BY ([Nett1]-[CCR1]);

then it only select those who played in division 1 and played under CCR.

This year's tournament is only at the one venue (golf course) so the CCR is
grabbed from ("CCR","Venues","VenueID=" & 1)

Thanks

Margaret

Try this:

SELECT DISTINCTROW ([Nett1]-[CCR1]) AS Under, Players.Gross1,
Players.Div1,
Players.Nett1, DLookUp("Venues.CCR","Venues","VenueID=" & 1) AS CCR1,
DLookUp("ClubID","Venues","VenueID=" & [div1]) AS Venue1,
Players.Surname,
Players.FirstName, Players.Initials, Players.ClubID, Players.Hcp1
FROM Players
WHERE (((([Nett1]-[CCR1]))<0) AND (Players.Gross1>0) AND
(Players.Nett1>0))
ORDER BY ([Nett1]-[CCR1]);

What relates the players table to the venues table?

The SQL is

SELECT DISTINCTROW ([Nett1]-[CCR1]) AS Under, Players.Gross1,
Players.Div1,
Players.Nett1, DLookUp("Venues.CCR","Venues","VenueID=" & 1) AS CCR1,
DLookUp("ClubID","Venues","VenueID=" & [div1]) AS Venue1,
Players.Surname,
Players.FirstName, Players.Initials, Players.ClubID, Players.Hcp1
FROM Players, Venues
WHERE (((([Nett1]-[CCR1]))<0) AND (Players.Gross1>0) AND
(Players.Nett1>0))
ORDER BY ([Nett1]-[CCR1]);

Thanks Jason.

Margaret

Are the criteria for Nett1 and Under on the same row? If they are on
different rows then they are compared using the "OR" function and not
the "AND"

If that isn't it then go into Query Designer and select SQL View and
copy that and post it here and we'll fix it right up for you.

Cheers,
Jason Lepack

Hello,

I have the following 3 fields in an Access 2 query

Under: ([Nett1]-[CCR1])
Sort Ascending
Criteria < 0

CCR1: DLookUp("Venues.CCR","Venues","VenueID=" & 4)

Nett1
File: Players
Criteria > 0
defined as Integer

I wanted to calculate their nett score for day 1 (nett1) minus the
course
rating (ccr1)

The values for "under" range from -9 to any number over zero.

Despite using the criteria for "under" being any value under zero, I
am
getting players all players from -9 to -1 correctly, but the query
gives
the players with 0, 1 and 2.

I want to ignore any "values" of zero or greater.

Thanking you,

Margaret- Hide quoted text -

- Show quoted text -