Re: How to make a query choose the last three years

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

My mistake, the order in the between should have the small value first

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#], [Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE [P&P_tbl].[Year] Between Year(Date())-3 And Year(Date())
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark]


"Jon" <Jon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C3313335-3B05-49B0-82B8-DB96C229DA31@xxxxxxxxxxxxxxxx
hi,
still does not work

"Ofer Cohen" wrote:

Hi Jon,
If the Year field contain only the Year and not a full date, try:

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#],
[Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE [P&P_tbl].[Year] Between Year(Date()) And Year(Date())-3
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark]

Note: It's not recomended using build in functions in Access as fields
names
(Year - that will return the year from the date).
It will be OK as long that you place it in square brackets, but to avoid
errors and to have to remember puting square brackets, it's better
changing
the name.

------
Good Luck
BS"D

"Jon" <Jon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2675357E-D56C-4895-B4B1-DB5C1A08F869@xxxxxxxxxxxxxxxx
Thank you all for your cooperation
This is what I did
But it did not work
This is my sql after rplace Date() with 2006 after that it worked

SELECT Avg([P&P_tbl].[P&P_%]) AS [Avg], [P&P_tbl].[ID#],
[Emp_Courses_tbl
Query].[Sum Of Mark], [Sum Of Mark]/25 AS [% of Course], [Avg]+[% of
Course]/100 AS [Total %]
FROM [Emp_Courses_tbl Query] INNER JOIN [P&P_tbl] ON [Emp_Courses_tbl
Query].[ID#] = [P&P_tbl].[ID#]
WHERE ((([P&P_tbl].Year) Between 2006 And DateAdd("yyyy",-3,Date())))
GROUP BY [P&P_tbl].[ID#], [Emp_Courses_tbl Query].[Sum Of Mark];


"Chris2" wrote:


"Jon" <Jon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CF7CCD8C-E27D-4CA0-A5F2-15B9BCDF571F@xxxxxxxxxxxxxxxx
does not work


Jon,

What error did you receive and what was the SQL code you used?


Sincerely,

Chris O.








.

Relevant Pages

  • Re: How to make a query choose the last three years
    ... It will be OK as long that you place it in square brackets, ... This is my sql after rplace Datewith 2006 after that it worked ... "Chris2" wrote: ... What error did you receive and what was the SQL code you used? ...
    (microsoft.public.access.queries)
  • Re: forms authentication problem
    ... After much wailing and gnashing of teeth I found the mistake. ... The mistake was in my SQL code. ... After removing the empty space the .Net authentication worked exactly ... It appears that the user roles are being added ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: How to make a query choose the last three years
    ... It will be OK as long that you place it in square brackets, ... "Chris2" wrote: ... What error did you receive and what was the SQL code you used? ... Chris O. ...
    (microsoft.public.access.queries)