Edit and save query criteria
in [Queries]
|
From: DNuding on 11 Mar 2010 11:54 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
From: Jerry Whittle on 11 Mar 2010 12:39 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
From: DNuding on 11 Mar 2010 13:13 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
From: Jerry Whittle on 11 Mar 2010 15:13 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
From: John Spencer on 11 Mar 2010 15:27
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. |