Repost-Edit and save query criteria

D

DNuding

Sorry to repost this, but I was not sure I would get help with this again and
it would get lost in the list.

As suggested, I created a table called ExcludeShows1 with the ShowID and
Year fields. I have posted the SQL with those changes
to the query. When I tested this initially, it appeared to work. Then I
tried testing the queries using last year's show data for 2009. No data is
being
returned for 2009. Not sure what is happening that it is not returning 2009
data.

SELECT Shows.ShowID, Entries.Place,
IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])
AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum,
Horses.HorseName, OwnersandRiders.FullName, Horses.HPNominatedYear,
Horses.NWHATRNumber, Horses.Title, Horses.Titles, Horses.Suspended,
Shows.StartDate, OwnersandRiders.NWHAMemDate, Shows.Year, Classes.NWHAHP,
NWHAHPCategories.HPDescription, OwnersandRiders.NWHAMember,
OwnersandRiders.Suspended, OwnersandRiders.AddField, Entries.Rider,
OwnersandRiders_1.NWHAMember, OwnersandRiders_1.NWHAMemDate
FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN
((NWHAHPCategories INNER JOIN Classes ON NWHAHPCategories.NWHAHP =
Classes.NWHAHP) INNER JOIN (Entries INNER JOIN OwnersandRiders AS
OwnersandRiders_1 ON Entries.Rider = OwnersandRiders_1.FullName) ON
Classes.ClassID = Entries.ClassID) ON Horses.HorseName = Entries.HorseName)
ON OwnersandRiders.OwnerRiderID = Entries.OwnerRiderID) ON (Shows.Name =
Classes.ShowName) AND (Shows.ShowID = Classes.ShowID)
WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))
AND ((Horses.HPNominatedYear)=CStr(Year(Date())))
AND ((Horses.Suspended)="N")
AND ((Shows.StartDate)>=[Ownersandriders].[NwhaMemDate])
AND ((Shows.Year)=CStr(Year(Date())))
AND ((Classes.NWHAHP)<>"NA")
AND ((OwnersandRiders.NWHAMember)="yes")
AND ((OwnersandRiders.Suspended)="N")
AND ((OwnersandRiders_1.NWHAMember)="Yes")
AND ((OwnersandRiders_1.NWHAMemDate)<[Shows].[Startdate])
ORDER BY Entries.Place, Classes.Entries;

In the ExcludeShows1 table I have 2 fields: ShowID and Year with Lookups by
query to the Shows table for those 2 fields. I entered 2009 ShowIDs and
2009 as the year for each show. When I run the above query, no
records are returned. The ExcludeShows1 table is populated with 7 records.
I thought that the query should return records for the 31 shows for 2009 to
Include. In the Shows table, the Year
field was setup as Text, in the ExcludeShows1 table it is set up as text to
match.

This is the year format for the Shows.Year and Horses.HPNomincatedYear fields.
CStr(Year(Date())). With those fields set up as text, should this be
different?

Thanks again for your help.
 
D

Duane Hookom

You have a number of criteria like:
Year = Year(Date()))
Without looking at the entire syntax, won't this limit the results to 2010?

--
Duane Hookom
MS Access MVP



DNuding said:
Sorry to repost this, but I was not sure I would get help with this again
and
it would get lost in the list.

As suggested, I created a table called ExcludeShows1 with the ShowID and
Year fields. I have posted the SQL with those changes
to the query. When I tested this initially, it appeared to work. Then I
tried testing the queries using last year's show data for 2009. No data
is
being
returned for 2009. Not sure what is happening that it is not returning
2009
data.

SELECT Shows.ShowID, Entries.Place,
IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])
AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum,
Horses.HorseName, OwnersandRiders.FullName, Horses.HPNominatedYear,
Horses.NWHATRNumber, Horses.Title, Horses.Titles, Horses.Suspended,
Shows.StartDate, OwnersandRiders.NWHAMemDate, Shows.Year, Classes.NWHAHP,
NWHAHPCategories.HPDescription, OwnersandRiders.NWHAMember,
OwnersandRiders.Suspended, OwnersandRiders.AddField, Entries.Rider,
OwnersandRiders_1.NWHAMember, OwnersandRiders_1.NWHAMemDate
FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN
((NWHAHPCategories INNER JOIN Classes ON NWHAHPCategories.NWHAHP =
Classes.NWHAHP) INNER JOIN (Entries INNER JOIN OwnersandRiders AS
OwnersandRiders_1 ON Entries.Rider = OwnersandRiders_1.FullName) ON
Classes.ClassID = Entries.ClassID) ON Horses.HorseName =
Entries.HorseName)
ON OwnersandRiders.OwnerRiderID = Entries.OwnerRiderID) ON (Shows.Name =
Classes.ShowName) AND (Shows.ShowID = Classes.ShowID)
WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))
AND ((Horses.HPNominatedYear)=CStr(Year(Date())))
AND ((Horses.Suspended)="N")
AND ((Shows.StartDate)>=[Ownersandriders].[NwhaMemDate])
AND ((Shows.Year)=CStr(Year(Date())))
AND ((Classes.NWHAHP)<>"NA")
AND ((OwnersandRiders.NWHAMember)="yes")
AND ((OwnersandRiders.Suspended)="N")
AND ((OwnersandRiders_1.NWHAMember)="Yes")
AND ((OwnersandRiders_1.NWHAMemDate)<[Shows].[Startdate])
ORDER BY Entries.Place, Classes.Entries;

In the ExcludeShows1 table I have 2 fields: ShowID and Year with Lookups
by
query to the Shows table for those 2 fields. I entered 2009 ShowIDs and
2009 as the year for each show. When I run the above query, no
records are returned. The ExcludeShows1 table is populated with 7
records.
I thought that the query should return records for the 31 shows for 2009
to
Include. In the Shows table, the Year
field was setup as Text, in the ExcludeShows1 table it is set up as text
to
match.

This is the year format for the Shows.Year and Horses.HPNomincatedYear
fields.
CStr(Year(Date())). With those fields set up as text, should this be
different?

Thanks again for your help.
Click to expand...
 
D

DNuding

Thanks for taking a look at this. Yes, looks like after a little research it
does return results for the current year.

I created a table called ExcludeShows1 with 2 fields. ShowID and Year. The
shows to exclude from the calculations this query does will change year to
year.

If I manually exclude the shows and manually enter the year 2009 in the Year
field the query works.

My wish is that I can reference the ExcludeShows1 table with the showids and
year in this query without having to manuall change it each year.

WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))

This was suggested in an earlier post on this request. Is there a way to do
this?

Thanks.

--
DN


Duane Hookom said:
You have a number of criteria like:
Year = Year(Date()))
Without looking at the entire syntax, won't this limit the results to 2010?

--
Duane Hookom
MS Access MVP



DNuding said:
Sorry to repost this, but I was not sure I would get help with this again
and
it would get lost in the list.

As suggested, I created a table called ExcludeShows1 with the ShowID and
Year fields. I have posted the SQL with those changes
to the query. When I tested this initially, it appeared to work. Then I
tried testing the queries using last year's show data for 2009. No data
is
being
returned for 2009. Not sure what is happening that it is not returning
2009
data.

SELECT Shows.ShowID, Entries.Place,
IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])
AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum,
Horses.HorseName, OwnersandRiders.FullName, Horses.HPNominatedYear,
Horses.NWHATRNumber, Horses.Title, Horses.Titles, Horses.Suspended,
Shows.StartDate, OwnersandRiders.NWHAMemDate, Shows.Year, Classes.NWHAHP,
NWHAHPCategories.HPDescription, OwnersandRiders.NWHAMember,
OwnersandRiders.Suspended, OwnersandRiders.AddField, Entries.Rider,
OwnersandRiders_1.NWHAMember, OwnersandRiders_1.NWHAMemDate
FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN
((NWHAHPCategories INNER JOIN Classes ON NWHAHPCategories.NWHAHP =
Classes.NWHAHP) INNER JOIN (Entries INNER JOIN OwnersandRiders AS
OwnersandRiders_1 ON Entries.Rider = OwnersandRiders_1.FullName) ON
Classes.ClassID = Entries.ClassID) ON Horses.HorseName =
Entries.HorseName)
ON OwnersandRiders.OwnerRiderID = Entries.OwnerRiderID) ON (Shows.Name =
Classes.ShowName) AND (Shows.ShowID = Classes.ShowID)
WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))
AND ((Horses.HPNominatedYear)=CStr(Year(Date())))
AND ((Horses.Suspended)="N")
AND ((Shows.StartDate)>=[Ownersandriders].[NwhaMemDate])
AND ((Shows.Year)=CStr(Year(Date())))
AND ((Classes.NWHAHP)<>"NA")
AND ((OwnersandRiders.NWHAMember)="yes")
AND ((OwnersandRiders.Suspended)="N")
AND ((OwnersandRiders_1.NWHAMember)="Yes")
AND ((OwnersandRiders_1.NWHAMemDate)<[Shows].[Startdate])
ORDER BY Entries.Place, Classes.Entries;

In the ExcludeShows1 table I have 2 fields: ShowID and Year with Lookups
by
query to the Shows table for those 2 fields. I entered 2009 ShowIDs and
2009 as the year for each show. When I run the above query, no
records are returned. The ExcludeShows1 table is populated with 7
records.
I thought that the query should return records for the 31 shows for 2009
to
Include. In the Shows table, the Year
field was setup as Text, in the ExcludeShows1 table it is set up as text
to
match.

This is the year format for the Shows.Year and Horses.HPNomincatedYear
fields.
CStr(Year(Date())). With those fields set up as text, should this be
different?

Thanks again for your help.
Click to expand...
Click to expand...
 
D

Duane Hookom

Apparently you want the Year to be selected by the user. IMO all user
interaction should be through controls on forms. You could have a text box
(or combo) that allows the user to select a year that would be referenced in
the subquery.
--
Duane Hookom
Microsoft Access MVP


DNuding said:
Thanks for taking a look at this. Yes, looks like after a little research it
does return results for the current year.

I created a table called ExcludeShows1 with 2 fields. ShowID and Year. The
shows to exclude from the calculations this query does will change year to
year.

If I manually exclude the shows and manually enter the year 2009 in the Year
field the query works.

My wish is that I can reference the ExcludeShows1 table with the showids and
year in this query without having to manuall change it each year.

WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))

This was suggested in an earlier post on this request. Is there a way to do
this?

Thanks.

--
DN


Duane Hookom said:
You have a number of criteria like:
Year = Year(Date()))
Without looking at the entire syntax, won't this limit the results to 2010?

--
Duane Hookom
MS Access MVP



DNuding said:
Sorry to repost this, but I was not sure I would get help with this again
and
it would get lost in the list.

As suggested, I created a table called ExcludeShows1 with the ShowID and
Year fields. I have posted the SQL with those changes
to the query. When I tested this initially, it appeared to work. Then I
tried testing the queries using last year's show data for 2009. No data
is
being
returned for 2009. Not sure what is happening that it is not returning
2009
data.

SELECT Shows.ShowID, Entries.Place,
IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])
AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum,
Horses.HorseName, OwnersandRiders.FullName, Horses.HPNominatedYear,
Horses.NWHATRNumber, Horses.Title, Horses.Titles, Horses.Suspended,
Shows.StartDate, OwnersandRiders.NWHAMemDate, Shows.Year, Classes.NWHAHP,
NWHAHPCategories.HPDescription, OwnersandRiders.NWHAMember,
OwnersandRiders.Suspended, OwnersandRiders.AddField, Entries.Rider,
OwnersandRiders_1.NWHAMember, OwnersandRiders_1.NWHAMemDate
FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN
((NWHAHPCategories INNER JOIN Classes ON NWHAHPCategories.NWHAHP =
Classes.NWHAHP) INNER JOIN (Entries INNER JOIN OwnersandRiders AS
OwnersandRiders_1 ON Entries.Rider = OwnersandRiders_1.FullName) ON
Classes.ClassID = Entries.ClassID) ON Horses.HorseName =
Entries.HorseName)
ON OwnersandRiders.OwnerRiderID = Entries.OwnerRiderID) ON (Shows.Name =
Classes.ShowName) AND (Shows.ShowID = Classes.ShowID)
WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))
AND ((Horses.HPNominatedYear)=CStr(Year(Date())))
AND ((Horses.Suspended)="N")
AND ((Shows.StartDate)>=[Ownersandriders].[NwhaMemDate])
AND ((Shows.Year)=CStr(Year(Date())))
AND ((Classes.NWHAHP)<>"NA")
AND ((OwnersandRiders.NWHAMember)="yes")
AND ((OwnersandRiders.Suspended)="N")
AND ((OwnersandRiders_1.NWHAMember)="Yes")
AND ((OwnersandRiders_1.NWHAMemDate)<[Shows].[Startdate])
ORDER BY Entries.Place, Classes.Entries;

In the ExcludeShows1 table I have 2 fields: ShowID and Year with Lookups
by
query to the Shows table for those 2 fields. I entered 2009 ShowIDs and
2009 as the year for each show. When I run the above query, no
records are returned. The ExcludeShows1 table is populated with 7
records.
I thought that the query should return records for the 31 shows for 2009
to
Include. In the Shows table, the Year
field was setup as Text, in the ExcludeShows1 table it is set up as text
to
match.

This is the year format for the Shows.Year and Horses.HPNomincatedYear
fields.
CStr(Year(Date())). With those fields set up as text, should this be
different?

Thanks again for your help.
Click to expand...
Click to expand...
Click to expand...
 
D

DNuding

Yes, that is sort of what I wanted to do. I wanted the information to be
pre-selected from a form created on the ExcludeShowstable1 with the fields
ShowID, ShowYear and HPNominatedYear. Then when the user invoked the query,
it would return the results. They may not always invovoke the query from the
form.

The criteria Year=Year(Date())), does this always return the current year?
Next year, would it return 2011? If so, I may leave the query as is.

Thanks for your help.
--
DN


Duane Hookom said:
Apparently you want the Year to be selected by the user. IMO all user
interaction should be through controls on forms. You could have a text box
(or combo) that allows the user to select a year that would be referenced in
the subquery.
--
Duane Hookom
Microsoft Access MVP


DNuding said:
Thanks for taking a look at this. Yes, looks like after a little research it
does return results for the current year.

I created a table called ExcludeShows1 with 2 fields. ShowID and Year. The
shows to exclude from the calculations this query does will change year to
year.

If I manually exclude the shows and manually enter the year 2009 in the Year
field the query works.

My wish is that I can reference the ExcludeShows1 table with the showids and
year in this query without having to manuall change it each year.

WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))

This was suggested in an earlier post on this request. Is there a way to do
this?

Thanks.

--
DN


Duane Hookom said:
You have a number of criteria like:
Year = Year(Date()))
Without looking at the entire syntax, won't this limit the results to 2010?

--
Duane Hookom
MS Access MVP



Sorry to repost this, but I was not sure I would get help with this again
and
it would get lost in the list.

As suggested, I created a table called ExcludeShows1 with the ShowID and
Year fields. I have posted the SQL with those changes
to the query. When I tested this initially, it appeared to work. Then I
tried testing the queries using last year's show data for 2009. No data
is
being
returned for 2009. Not sure what is happening that it is not returning
2009
data.

SELECT Shows.ShowID, Entries.Place,
IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([Entries]>3 And
[Place]=1,1,0) AS Bonus,
IIf([Points1]+[Bonus]>CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),CInt(Nz(Choose([Place],7,5,4,3,2,1),0)),[Points1]+[Bonus])
AS Points, Classes.Entries, Entries.ClassID, Classes.ClassNum,
Horses.HorseName, OwnersandRiders.FullName, Horses.HPNominatedYear,
Horses.NWHATRNumber, Horses.Title, Horses.Titles, Horses.Suspended,
Shows.StartDate, OwnersandRiders.NWHAMemDate, Shows.Year, Classes.NWHAHP,
NWHAHPCategories.HPDescription, OwnersandRiders.NWHAMember,
OwnersandRiders.Suspended, OwnersandRiders.AddField, Entries.Rider,
OwnersandRiders_1.NWHAMember, OwnersandRiders_1.NWHAMemDate
FROM Shows INNER JOIN (OwnersandRiders INNER JOIN (Horses INNER JOIN
((NWHAHPCategories INNER JOIN Classes ON NWHAHPCategories.NWHAHP =
Classes.NWHAHP) INNER JOIN (Entries INNER JOIN OwnersandRiders AS
OwnersandRiders_1 ON Entries.Rider = OwnersandRiders_1.FullName) ON
Classes.ClassID = Entries.ClassID) ON Horses.HorseName =
Entries.HorseName)
ON OwnersandRiders.OwnerRiderID = Entries.OwnerRiderID) ON (Shows.Name =
Classes.ShowName) AND (Shows.ShowID = Classes.ShowID)
WHERE Shows.ShowID NOT IN (SELECT ShowID FROM ExcludeShows1 WHERE Year =
Year(Date()))
AND ((Horses.HPNominatedYear)=CStr(Year(Date())))
AND ((Horses.Suspended)="N")
AND ((Shows.StartDate)>=[Ownersandriders].[NwhaMemDate])
AND ((Shows.Year)=CStr(Year(Date())))
AND ((Classes.NWHAHP)<>"NA")
AND ((OwnersandRiders.NWHAMember)="yes")
AND ((OwnersandRiders.Suspended)="N")
AND ((OwnersandRiders_1.NWHAMember)="Yes")
AND ((OwnersandRiders_1.NWHAMemDate)<[Shows].[Startdate])
ORDER BY Entries.Place, Classes.Entries;

In the ExcludeShows1 table I have 2 fields: ShowID and Year with Lookups
by
query to the Shows table for those 2 fields. I entered 2009 ShowIDs and
2009 as the year for each show. When I run the above query, no
records are returned. The ExcludeShows1 table is populated with 7
records.
I thought that the query should return records for the 31 shows for 2009
to
Include. In the Shows table, the Year
field was setup as Text, in the ExcludeShows1 table it is set up as text
to
match.

This is the year format for the Shows.Year and Horses.HPNomincatedYear
fields.
CStr(Year(Date())). With those fields set up as text, should this be
different?

Thanks again for your help.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Yes that expression will return the current calendar year. Well, sort of.

Year(Date()) actually returns the year of the date that is the system date.
So if someone has messed up the system date you could get a different year
than the expected year of the current calendar date. A rare occurrence.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare 2 fields from 1 query 0
IIF delima-Need Help PLEASE! 2
Select Query 0
IIf help 2
IIf statements 5
IIf Help! 0

Top