Prev: Iif Statement Problem
Next: SQL question - Cross Tab query
From: DNuding on 14 Mar 2010 17:22
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.
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Iif Statement Problem
Next: SQL question - Cross Tab query