Edit and save query criteria

D

DNuding

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.
 
J

Jerry Whittle

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.
 
D

DNuding

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
J

Jerry Whittle

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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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=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
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.
Click to expand...
 
D

DNuding

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 said:
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=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
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.
Click to expand...
.
Click to expand...
 
D

DNuding

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 said:
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 said:
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=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
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.
Click to expand...
.
Click to expand...
Click to expand...
 

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

criteria on form 1
Open Query Design View or Saving Extremely SLOW 1
Counting multiple memberships 1
Use In() Expression as Query criteria with guts from Function 1
Run query in another database 3
Using expressions for Query criteria 2
Records missing from query/report 2
Edit primary key values 3

Top