Access Query Help! 1

[alwayshouston]

Hi All!
I have a table in below format:
Dept_ID Expenses Date
ACCTING 12000000 6/30/2000
ACCTING 10000000 7/31/2000
ACCTING 20000000 8/31/2000
ACCTING 30000000 9/30/2000
ACCTING 50000000 10/31/2000
ACCTING 60000000 11/30/2000
ACCTING 70000000 12/31/2000
ACCTING 10000000 1/31/2001
......
......
......
ACCTING 20000000 12/31/2003

I would like to write a query that would give me an output based on either Quarterly basis or Semi Annual basis or Annual basis from the date that the user selected. When the query is executed it would ask for the parameter of selection of records on Quarterly or Yearly or Semi Annually. For example, if the user selects date of 7/31/2000 and input quarterly in the parameter, it would return following:
ACCTING 10000000 7/31/2000
ACCTING 50000000 10/31/2000
ACCTING 10000000 1/31/20001

If the users select date of 7/31/2000 and select Yearly, it would return the following:
ACCTING 10000000 7/31/2000
ACCTING 90000000 7/31/2001
ACCTING 70000000 7/31/2002
ACCTING 40000000 7/31/2003

How could I write a query that would give me above output. I tried using DateDiff("q",[Date]) function but it does not give the right output.
Any help or feedback is appreciated!

Thanks in Advance!!!!

 

[ksbigfoot]

Hi AlwaysHouston,
What I would do is write some code in a module that would take the date the user entered, plus the value of Quarterly, Semi-Annually, Yearly and write the sql statement out using a UNION and a SUM on the Expenses field
For quarterly, you would do something like
Query1 UNION Query2 UNION Query3 UNION Query4

I used the following code to figure out the date values:

Dim dEnteredDate As Date
Dim dNextDate As Date

dEnteredDate = Me.txtDateEntered
dNextDate = DateAdd("m", 3, dEnteredDate)
Me.txtNextDate = dNextDate

Hope this helps
KS

 

[alwayshouston]

Thank you for your prompt feedback KS!
Actually, my query results can have records for twenty years and plus. Your solution works fine for fewer period (records). Can you tell me how to write a SQL statement that can simply skips record based on Quarterly or Yearly or Semi Annually basis? For quarterly, it should skip two records. For Annually, it should skip 11 records. Please note that I do not want to sum the values.
Thanks!

 

[TonyJollans]

There's probably an easier way but I think this will do it ..

[blue][tt]SELECT [Dept_ID],
[Expenses],
[Date]

FROM YourTable

WHERE [Date] >= [Input Date]

AND (Month([Input Date])-Month([date]))
Mod
Int(3*2^(InStr("QSA",Left([Input Period],1))-1)) = 0;[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[alwayshouston]

Thanks for your reply Tony!
I tried the formula, but it gives me an error on Input Period. What should be the value of Input Period? Should it be Q for Quarter or Y for Year or should it be numbers....

 

[TonyJollans]

Sorry,

It should be (or begin with) "Q" for Quarterly, "S" for Semi-Annually, or "A" for Annually.

You can change these letters by changing the string "QSA" in the SQL - the first letter is for every 3 months, the second every 6 and the third every 12 - the fourth if you had one would be for 24 months etc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[alwayshouston]

Just for clarification... Can you give me some sample numbers/values for the Input Period? I am still not following your condition in the SQL. What should be the value of Input Period if I want the result to be in quarterly basis? What should be the value of Input Period if I want the result to be in Semi Annually basis?

 

[TonyJollans]

Q" for Quarterly, "S" for Semi-Annually

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[alwayshouston]

Thanks!
It worked perfectly )