Date

[Hugh]

I am trying to make a query that calculates a weekly handicap score for the
players in my league. Each player has a handicap based upon their score each
week. The handicap score is what they shoot minus the handicap established
the previous week. Once I enter this weeks score I can not get the query to
look back at last weeks handicap to establish this weeks handicap score.
(Hope this is not too confusing).

I have a form set up to enter a playdate that all my queries look to for the
record date. I tried two control boxes, one with =Date() and the other with
=Date()-7 but this only works for the present date. Anyone know how I can
accomplish my goal?

Hugh

 

[Jeff Boyce]

Hugh

Not sure I entirely understand what you are trying to do.

From your description of your form, you have a control for a date, and a
control for that date minus 7 days. Instead of using the hard-coded
function "Date()", could you allow the user to enter a valid date? Then the
"other" control could calculate that date minus 7.

Just a thought

Jeff Boyce
<Access MVP>

 

[Hugh]

Jeff, I tried that only to receive "error" in the second control.

Hugh

 

[John Spencer (MVP)]

Are the prior weekly handicap dates always exactly a week before? Assuming that
this is not necessarily the case.

Sounds as if you may need to include the table twice or get the handicap for
each player from the prior week.

GUESSING since you have not posted any table structure. Here is an UNTESTED Set
of queries with generic field names.

SELECT PlayerID, TheHandicap
FROM TheTable As T
WHERE TheDate =
(SELECT Max(TheDate)
FROM TheTable as A
WHERE A.PlayerID = T.PlayerID
AND A.TheDate < [The Cutoff Date])


Select <listofFields>,
T.TheScore - Q.TheHandicap
FROM TheTable As T INNER JOIN TheAboveQuery as Q

 

[Jeff Boyce]

What expression did you use to set the second control?

 

[Hugh]

Thank you gentlemen for your help, I am now able to access two different
dates on the same form.

Hugh

Are the prior weekly handicap dates always exactly a week before? Assuming that
this is not necessarily the case.

Sounds as if you may need to include the table twice or get the handicap for
each player from the prior week.

GUESSING since you have not posted any table structure. Here is an UNTESTED Set
of queries with generic field names.

SELECT PlayerID, TheHandicap
FROM TheTable As T
WHERE TheDate =
(SELECT Max(TheDate)
FROM TheTable as A
WHERE A.PlayerID = T.PlayerID
AND A.TheDate < [The Cutoff Date])


Select <listofFields>,
T.TheScore - Q.TheHandicap
FROM TheTable As T INNER JOIN TheAboveQuery as Q

I am trying to make a query that calculates a weekly handicap score for the
players in my league. Each player has a handicap based upon their score each
week. The handicap score is what they shoot minus the handicap established
the previous week. Once I enter this weeks score I can not get the query to
look back at last weeks handicap to establish this weeks handicap score.
(Hope this is not too confusing).

I have a form set up to enter a playdate that all my queries look to for the
record date. I tried two control boxes, one with =Date() and the other with
=Date()-7 but this only works for the present date. Anyone know how I can
accomplish my goal?

Hugh