Edit and save query criteria

I have 2 Select queries where the results criteria changes on a yearly basis
for 2 fields. There are 6 tables referenced in the queries. Only 2 tables
referenced with 1 field each where the criteria would be changed.

How could a user edit and save these changes? These are very Novice users
of a database who would not have access to the open database. This was a
homegrown database, but now trying to make it useable for others who have
little to no experience.

Thanks for any and all suggestions.
--
DN

One way would be to use a parameter prompt where the users are prompted with
a pop-up to enter the criteria.

Another option would be to use a form to put in the criteria and reference
it from there. Something like =[Forms]![MyForm]![FormField]

By chance is the criteria related to a date field? If so you might be able
to use something like =Date() or =Year(Date)) to automatically put in the
criteria based on today's date.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"DNuding" wrote:

I have 2 Select queries where the results criteria changes on a yearly basis
for 2 fields. There are 6 tables referenced in the queries. Only 2 tables
referenced with 1 field each where the criteria would be changed.

How could a user edit and save these changes? These are very Novice users
of a database who would not have access to the open database. This was a
homegrown database, but now trying to make it useable for others who have
little to no experience.

Thanks for any and all suggestions.
--
DN

Thanks Jerry for your quick response. This homegrown db has turned into a
monster and I am having a problem getting my thoughts around how to fix it.
Here is the SQL of one of the select queries that needs to be updated yearly.

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)384 And (Shows.ShowID)397 And (Shows.ShowID)402
And (Shows.ShowID)403 And (Shows.ShowID)405 And (Shows.ShowID)407 And
(Shows.ShowID)412) AND ((Horses.HPNominatedYear)="2010") AND
((Horses.Suspended)="N") AND
((Shows.StartDate)=[Ownersandriders].[NwhaMemDate]) AND
((Shows.Year)="2010") 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;

The fields ShowID, HPNominatedYear and Year must be updated annually. I
need a way that a novice can do this annually without opening the database
manually and touching the actual query.

A form to do this would be great, just not sure where to start with that.
Am using Access 2007 for this.

Thanks again.
--
DN


"Jerry Whittle" wrote:

One way would be to use a parameter prompt where the users are prompted with
a pop-up to enter the criteria.

Another option would be to use a form to put in the criteria and reference
it from there. Something like =[Forms]![MyForm]![FormField]

By chance is the criteria related to a date field? If so you might be able
to use something like =Date() or =Year(Date)) to automatically put in the
criteria based on today's date.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"DNuding" wrote:

I have 2 Select queries where the results criteria changes on a yearly basis
for 2 fields. There are 6 tables referenced in the queries. Only 2 tables
referenced with 1 field each where the criteria would be changed.

How could a user edit and save these changes? These are very Novice users
of a database who would not have access to the open database. This was a
homegrown database, but now trying to make it useable for others who have
little to no experience.

Thanks for any and all suggestions.
--
DN

Here's what I mean by using the Year and Date function together to get the
current year. No changes would be needed to the query as long as you want the
current year.

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)384
And (Shows.ShowID)397
And (Shows.ShowID)402
And (Shows.ShowID)403
And (Shows.ShowID)405
And (Shows.ShowID)407
And (Shows.ShowID)412)
AND ((Horses.HPNominatedYear)= Year(Date()))
AND ((Horses.Suspended)="N")
AND ((Shows.StartDate)=[Ownersandriders].[NwhaMemDate])
AND ((Shows.Year)= 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;

Here's something else to test. It cleans up the ShowIds some:
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(384, 397, 402, 403, 405, 407, 412)
AND ((Horses.HPNominatedYear)= Year(Date()))
AND ((Horses.Suspended)="N")
AND ((Shows.StartDate)=[Ownersandriders].[NwhaMemDate])
AND ((Shows.Year)= 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;


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"DNuding" wrote:

Thanks Jerry for your quick response. This homegrown db has turned into a
monster and I am having a problem getting my thoughts around how to fix it.
Here is the SQL of one of the select queries that needs to be updated yearly.

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)384 And (Shows.ShowID)397 And (Shows.ShowID)402
And (Shows.ShowID)403 And (Shows.ShowID)405 And (Shows.ShowID)407 And
(Shows.ShowID)412) AND ((Horses.HPNominatedYear)="2010") AND
((Horses.Suspended)="N") AND
((Shows.StartDate)=[Ownersandriders].[NwhaMemDate]) AND
((Shows.Year)="2010") 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;

The fields ShowID, HPNominatedYear and Year must be updated annually. I
need a way that a novice can do this annually without opening the database
manually and touching the actual query.

A form to do this would be great, just not sure where to start with that.
Am using Access 2007 for this.

Thanks again.
--
DN


"Jerry Whittle" wrote:

One way would be to use a parameter prompt where the users are prompted with
a pop-up to enter the criteria.

Another option would be to use a form to put in the criteria and reference
it from there. Something like =[Forms]![MyForm]![FormField]

By chance is the criteria related to a date field? If so you might be able
to use something like =Date() or =Year(Date)) to automatically put in the
criteria based on today's date.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"DNuding" wrote:

I have 2 Select queries where the results criteria changes on a yearly basis
for 2 fields. There are 6 tables referenced in the queries. Only 2 tables
referenced with 1 field each where the criteria would be changed.

How could a user edit and save these changes? These are very Novice users
of a database who would not have access to the open database. This was a
homegrown database, but now trying to make it useable for others who have
little to no experience.

Thanks for any and all suggestions.
--
DN

WHERE

Shows.ShowID384 And Shows.ShowID397 And Shows.ShowID402
And Shows.ShowID403 And Shows.ShowID405 And Shows.ShowID407 And
Shows.ShowID412

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"

Personally I would handle the showid that are to be excluded by having a table
with the exclusions (by Year) and then using that to eliminate records.

ExcludeShows
ShowID (Number field)
ShowYear (Number field)

Simplest way to use that in a where clause would be

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

More efficient would be to use that in an outer join and test for it being
null in the where clause of the query.


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

DNuding wrote:
Thanks Jerry for your quick response. This homegrown db has turned into a
monster and I am having a problem getting my thoughts around how to fix it.
Here is the SQL of one of the select queries that needs to be updated yearly.

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)384 And (Shows.ShowID)397 And (Shows.ShowID)402
And (Shows.ShowID)403 And (Shows.ShowID)405 And (Shows.ShowID)407 And
(Shows.ShowID)412) AND ((Horses.HPNominatedYear)="2010") AND
((Horses.Suspended)="N") AND
((Shows.StartDate)=[Ownersandriders].[NwhaMemDate]) AND
((Shows.Year)="2010") 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;

The fields ShowID, HPNominatedYear and Year must be updated annually. I
need a way that a novice can do this annually without opening the database
manually and touching the actual query.

A form to do this would be great, just not sure where to start with that.
Am using Access 2007 for this.

Thanks again.

Thank you for the suggestions. I have been able to put them to use in the
database. Just this small change has made a big difference. I am starting
to feel hopeful again!
--
DN


"John Spencer" wrote:

WHERE

Shows.ShowID384 And Shows.ShowID397 And Shows.ShowID402
And Shows.ShowID403 And Shows.ShowID405 And Shows.ShowID407 And
Shows.ShowID412

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"

Personally I would handle the showid that are to be excluded by having a table
with the exclusions (by Year) and then using that to eliminate records.

ExcludeShows
ShowID (Number field)
ShowYear (Number field)

Simplest way to use that in a where clause would be

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

More efficient would be to use that in an outer join and test for it being
null in the where clause of the query.


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

DNuding wrote:
Thanks Jerry for your quick response. This homegrown db has turned into a
monster and I am having a problem getting my thoughts around how to fix it.
Here is the SQL of one of the select queries that needs to be updated yearly.

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)384 And (Shows.ShowID)397 And (Shows.ShowID)402
And (Shows.ShowID)403 And (Shows.ShowID)405 And (Shows.ShowID)407 And
(Shows.ShowID)412) AND ((Horses.HPNominatedYear)="2010") AND
((Horses.Suspended)="N") AND
((Shows.StartDate)=[Ownersandriders].[NwhaMemDate]) AND
((Shows.Year)="2010") 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;

The fields ShowID, HPNominatedYear and Year must be updated annually. I
need a way that a novice can do this annually without opening the database
manually and touching the actual query.

A form to do this would be great, just not sure where to start with that.
Am using Access 2007 for this.

Thanks again.
.

I have been testing the queries using last year's data. No data is being
returned. Here is the SQL with the changes:

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. When I run the above query, no
records are returned. The ExcludeShows1 table is populated with 7 records.
It should return records for 31 shows for 2009. In the Shows table, the Year
field was setup as Text, in the ExcludeShows1 table it is set up as number.

One other question, In order to return the the correct year, which Year
format should I use in the query?:
CStr(Year(Date()))
or
Year(Date()))

Thanks again for the support
--
DN


"DNuding" wrote:

Thank you for the suggestions. I have been able to put them to use in the
database. Just this small change has made a big difference. I am starting
to feel hopeful again!
--
DN


"John Spencer" wrote:

WHERE

Shows.ShowID384 And Shows.ShowID397 And Shows.ShowID402
And Shows.ShowID403 And Shows.ShowID405 And Shows.ShowID407 And
Shows.ShowID412

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"

Personally I would handle the showid that are to be excluded by having a table
with the exclusions (by Year) and then using that to eliminate records.

ExcludeShows
ShowID (Number field)
ShowYear (Number field)

Simplest way to use that in a where clause would be

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

More efficient would be to use that in an outer join and test for it being
null in the where clause of the query.


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

DNuding wrote:
Thanks Jerry for your quick response. This homegrown db has turned into a
monster and I am having a problem getting my thoughts around how to fix it.
Here is the SQL of one of the select queries that needs to be updated yearly.

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)384 And (Shows.ShowID)397 And (Shows.ShowID)402
And (Shows.ShowID)403 And (Shows.ShowID)405 And (Shows.ShowID)407 And
(Shows.ShowID)412) AND ((Horses.HPNominatedYear)="2010") AND
((Horses.Suspended)="N") AND
((Shows.StartDate)=[Ownersandriders].[NwhaMemDate]) AND
((Shows.Year)="2010") 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;

The fields ShowID, HPNominatedYear and Year must be updated annually. I
need a way that a novice can do this annually without opening the database
manually and touching the actual query.

A form to do this would be great, just not sure where to start with that.
Am using Access 2007 for this.

Thanks again.
.