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
0
Utf
3/11/2010 4:54:03 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
617 Views

Similar Articles

[PageSpeed] 16

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
0
Utf
3/11/2010 5:39:04 PM
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
0
Utf
3/11/2010 6:13:01 PM
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
0
Utf
3/11/2010 8:13:02 PM
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

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.
0
John
3/11/2010 8:27:58 PM
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.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
> 
> 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.
> .
> 
0
Utf
3/14/2010 9:22:01 PM
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.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
> > 
> > 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.
> > .
> > 
0
Utf
3/15/2010 2:08:01 PM
Reply:

Similar Artilces:

OT: IE Save Dialog gone!
Hello... My Explorer now automatically OPENs a zip file, instead of providing the save dialog. Where do I go to restore this association's setting so that it goes back to prompting me for where to save it or run it, etc.? HELP! Thanks, Meow FYI - Just saying "OT" doesn't make this a good place for this question... http://www.speedguide.net/faq_in_q.php?category=40&qid=259 Tim "MeowSayTongue" <MeowSayTongue@thebarattheendoftheuniverse.org> wrote in message news:tmajg5hvggh5vvkg0v01k3oh60svfvfar4@4ax.com... > &...

SQL Server 2000 Standard Edition vs SQL 2000 Personnel Edition
I am using SQL server 2000 standard Edition sp3 with Gp 9 Sp3, I am planning to move GP with database to new server different hardware. can I use sql 2000 personnel edition sp3 or should I use same old version. I hope u understand my question. MK KSA Generally speaking, personal editions are not robust enough to support a product like GP. I would stay away. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "MK KSA&quo...

Selection query
In a column of a couple of thousand addresses, I want to copy to a new spreadsheet all of the entries (in that column only) that fullfill the criteria of 'nnn Short St" where nnn is any number from 1 to 9,999. I also want to sort them by number, should that be a seperate subsequent process on the new list of entries? Thanks for any pointers. Brian Tozer Hi maybe the following addin will help you: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany KiwiBrian wrote: > In a column of a couple of thousand addresses, I want to copy to a new > ...

Editing Text when using Publisher as a web page...
I'm using Publisher to document the status of current taskings in the office. I'm trying to ger Publisher to allow people that pull up the web-page to add and edit text on the web page so we can keep everything up to date. Right now, someone has to come to me and tell me the changes, then I change the info in the Pub doc, then I have to repost the web-page. Is there a way for people to edit the text on the web-page when they pull it up. I have text boxes in there now that people can edit, but the text doesn't update when you close the page and re-open it. Please Help if you ...

Access can not find a table in append query
Hi all, I have a table called MyCustomers. For some reason, when I try to create Append query, it doesnt show on the list of tables. I know the the table is there and I can select it if I want to create a different type of query but not append. What can cause that? TIA, Tom Does your table have a PRIMARY KEY defined? -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ If my post was helpful to you, please rate the post. "Tom" wrote: > Hi all, > > I have a table call...

Dont Save OnClose if..
This form has about 12 Text Boxes But when I click cmdClose I dont want the record to save if [tbName] Control Source [OwnerLastName] Text Field This dose not seem to work..........Thanks for any Help.........Bob Private Sub cmdClose_Click() If IsNull(tbName) Then If Me.Dirty Then Me.Undo End If End If DoCmd.Close acForm, Me.Name End Sub This could be a timing issue, Bob, i.e. the record might already be saved before this code runs. The only way you can be sure to catch this is to use the BeforeUpdate event procedure of the *form*. Access fires this event just before...

editting auto recovery
Hello, is it possible to change the auto recovery directory just for one document and is also possible to ensure that the auto recovery file is not deleted after the file is closed? Cheers Hi AFAIK this is not possible. But you may consider using the following add-in: http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) -- Regards Frank Kabel Frankfurt, Germany Hcoms wrote: > Hello, > > is it possible to change the auto recovery directory just for one > document and is also possible to ensure that the auto recovery file > ...

MAX AVERAGE MIN with additional criteria, ignoring blank cells
Hi all, After some recent help from the forum I'm successfully using the following array formulas to calculate the MAX values in several columns of cells. The values used to calculate the MAX depend on other numerical values located in adjacent columns: {=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,K3:K1002)))} {=IF(P1019="","",MAX(IF($E$3:$E$1002=$F$3:$F$1002,IF($G$3:$G$1002=$H$3:$H$1002,P3:P1002))))} {=IF(U1019="","",MAX(IF($G$3:$G$1002=0,IF($H$3:$H$1002=0,U3:U1002))))} {=IF(AL1019="",""...

Filter and save result
After filtering a list, how do I save only the filtered result "as it is" on the same sheet and discard the rest? Please help...thanks. Paste it unto a new sheet and discard the old, or reverse the filter so you gat everything except what you intended to filter for and then delete all rows -- Regards, Peo Sjoblom "annoni" <annoni@discussions.microsoft.com> wrote in message news:B4C7227A-2063-4710-A1D4-0A58BA7ACA99@microsoft.com... > After filtering a list, how do I save only the filtered result "as it is" > on > the same sheet and...

template edit question, add more? edit template?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel i'm using a resume template and it's beautiful, but only allows four job positions before it moves one the education, etc. <br><br><i>guess theres a way to do it on the pc version, but with mac, how do i insert an addition formatted block? it will only let me work within the template currently.</i>&#32;<br><br><i>hope that makes sense, thanks for the help!</i> Mac or PC makes no difference � it's a matter of how the template is constructed. With...

BOM & Edit List
Hi, I created an Assembly transaction and printed it from the Assembly transaction entry window before posting it. In the posting journal that prints it does not print the Assemble cost, extended assemble cost, Unit cost of the components and the extended cost. But if i post the transaction then the same posting journal is printed with the costs. Why does it not print? Please let me know Thanks Dev It only pulls posted costs, like other Great Plains edit lists including items. You will need to modify the report to include costs. "Dev" wrote: > Hi, > > I created...

How to edit entitys attributes directly?
I messed up with entity mappings and ended up with errror: attribute not found. How can I manually remove attribute from entity? Tried export -edit -import but it seems not to be the right way. You will first need to remove the "bad" mapping that you created. Since it is referncing the field, you need to do that prior to trying to remove the field. You also need to remove it from any forms & views that may be displaying it. The customization Import will not remove fields (as you have discovered). That is done as a safety feature to rpevent accidental loss of data (ima...

Editing
Hi! Could you please tell me how to remove a linked transaction within the budget planner. For example, I have a double entry under the debt category. ...

editing a drop down #2
that's not one of my choices. -- kateweb ------------------------------------------------------------------------ kateweb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16301 View this thread: http://www.excelforum.com/showthread.php?threadid=276931 what do you mean by this .. is it greyed out on the toolbar or shows "any value"? if you'ld like to tell me where you downloaded it from i can go have a look at it cheers JulieD "kateweb" <kateweb.1fictb@excelforum-nospam.com> wrote in message news:kateweb.1fictb@excelfor...

Macro to edit details
Hi, i am currently trying to create a macro regarding editing staf details on a worksheet....... therefore i recorded a macro and named it editstafflist, i highlighte my headings...and then went to 'data > form' and then had to close th form and then stop my macro. It wouldnt allow me to stop my macr whilst the dialogue box was open. Anyways after stopping the macro drew out a button and assigned this 'editstafflist' macro to it. saved my sheet and then clicked on the button, but a error '1004' kep occuring, can anyone please help me on this as i am rather stuck ...

Static and query based dist groups
Hello, I'm implementing a QBDG (query based distribution group) in exchange 2003 to contain all full time employees and another to include all part time employees. I've got the LDAP search down and it works perfectly. The problem is that my bosses want to be able to opt in to the particular lists. I need to be able to create a static DG that contains the opt ins as well as the QBDG. How do you add a QBDG into a static DG? "adam432823" <adam432823@discussions.microsoft.com> wrote: >Hello, I'm implementing a QBDG (query based distribution group) in excha...

Excel File saved as Temp file
I have "upgraded" to Windows 7 from Vista. I am having numerous problems with Excel (Office 2000) . One of those is that when I try to do a file save, I get the message that It wouldn't save and that a temp file has been created that I must rename and then try again. When it does this, it won't allow me to save the file in any other location. The Excel file completely disappears. I can save new files in the same location as the file in question. In another forum I followed links to a suggested fix, I tried those steps and they did not give me anythi...

Data collection Form Edit
We have a form which will be used almost daily, for data collection, based on a query. During the test we found a typo in one of the field labels where a brief description is included. Is there some way to edit the form? (recreating it with all the custom lables is just too long a task). Another issue on this topic, every time the form is sent, the messge reverts to the default "Please fill out the form and retunr it to me". Can this default message be changed? Nothing in the help functions. Any advice, or deirection to a learning resource will be appreciated. Thanks is adva...

Saving emails to harddrive
I get several emails regarding various topics and I'd like to save them by topic on my harddrive. Usually I have to copy the text to notepad and then to .docx. on Word 2007. Surely there is a better way to save emails to the harddrive. I just drag them from Outlook Express into the folder window where I want to keep them. Hope this helps. Pete On Oct 15, 2:06 pm, bretsharon <bretsha...@discussions.microsoft.com> wrote: > I get several emails regarding various topics and I'd like to save them by > topic on my harddrive. Usually I have to copy the text to notepad a...

Setting caret in edit control & activating it.
Hello Does anyone have any example code for setting and enabling the caret in an edit control ? Nicholas Take a look at the CEdit::SetSel() member function. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Nicholas Kingsley" <nickk@nildram.co.uk> wrote in message news:3f9d418a$0$106$65c69314@mercury.nildram.net... > Hello > > Does anyone have any example code for setting and enabling the caret in an > edit control ? > > Nicholas > > If you are asking how to create & use your own caret in an edit control then check...

saving without prompt within code
I am using this code: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) ActiveWorkbook.SaveAs FileName:="J:\agency\" ActiveSheet.Range("s43") End Sub to save whenever an employee changes this document. Can I have it sav without prompting the user? Thanks in advance. Tawne -- tawnee jamiso ----------------------------------------------------------------------- tawnee jamison's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1613 View this thread: http://www.excelforum.com/showthread.php?threadid=27785 Hi Tawnee Workshee...

"Windows PowerShell Cookbook" by Lee Holmes second edition draft available for viewing and comments
O'Reilly Books has made the working text of the PS V2 second edition of Lee Holmes' "Windows PowerShell Cookbook" available for viewing and comments at http://powershell.labs.oreilly.com/ - Larry In message <exi7SKRyKHA.2552@TK2MSFTNGP04.phx.gbl>, Larry__Weiss <lfw@airmail.net> writes >O'Reilly Books has made the working text of the PS V2 second edition >of Lee Holmes' "Windows PowerShell Cookbook" available for viewing and >comments at > > http://powershell.labs.oreilly.com/ > > - Larry > Aw...

Form, Query, and Updating Records
I have an employee profile form that I would like to filter by what location the employee works out of. The location is already determined based on a login I have in the beginning. The location value is stored in a Query (call it query1). When I get the records filtered to what I want, I still want to be able to edit/update them. Is this possible? I have played around with the Adavanced Filter but haven't been successful because when I filter the correct field in the employee profile form, and put my EXPRESSION (I think this is part of the problem) of what I want it filted by ([Query1]...

Why are my Excel files being saved to temp files?
When excel saves the file, it saves it as a temporary file with a funny name (8 characters--no extension). If the save is successful, xl will delete the original (or rename it to its backup name (like "backup of book1.xlk)) and if that's successful, xl will rename the funny named file to the original's name. Common things that get blamed for interruptions to this process are antivirus software poking its head in or network errors--either permissions or physical problems. mikerta wrote: -- Dave Peterson ec35720@netscape.com ...

Having Insert button functionality while editing
Hi, I want to have the functionality of Insert button (on keyboard) in my while application while editing in a cell or a edit box. For eg if i am editing my text say "alpha" and my cursor is between "l" and "p" and I insert an alphabet say "z", i want the "p" should be replaced by "Z" and the text should become "alzha". Please suggest. Thanks in advance. Regards, Arjun CEdit does not support that by default. Here is a good example of how implement this (Although I think UpdateCaret should just be a method of CXEdi...