Summing a different way

[Neil]

I am stumped by a table and how the data is laid out and need some help
figuring out how to sum the data in this format. Heres the layout:

Year Qtr Hours
2006 1 2
2006 2 1.5
2006 3 5
2006 4 1.25
2007 1 3
2007 2 1.5
2007 3 6
2007 4 1

Here's what I want to do. I want to sum 2006 Qtr 2 to 2007 Qtr 1 Hours.
The result would be 10.75.

Additionally I want to be able to have a user prompted to enter start year,
start Qtr followed by end year, end Qtr.

Any ideas?

 

[Dale Fye]

Are your [Year] and [Quarter] fields numeric, or strings?

If numeric, you could Multiply the year by 10 and add the quarter, those
values would sort correctly such that you could do a Between [Start_Year] *
10 + [Start_Month] AND [End_Year] * 10 + [End_Month]

BTW, Year is an Access reserved word, so I would advise against using it as
a field name. If you do use it, you should take care to wrap it in brackets
[ ] whenever you refer to that field.

 

[Neil]

Yeah they are numeric, I will change the name to Yr. Thanks for the tip!

Anyone else have a an alternative solution?

Are your [Year] and [Quarter] fields numeric, or strings?

If numeric, you could Multiply the year by 10 and add the quarter, those
values would sort correctly such that you could do a Between [Start_Year] *
10 + [Start_Month] AND [End_Year] * 10 + [End_Month]

BTW, Year is an Access reserved word, so I would advise against using it as
a field name. If you do use it, you should take care to wrap it in brackets
[ ] whenever you refer to that field.

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I am stumped by a table and how the data is laid out and need some help
figuring out how to sum the data in this format. Heres the layout:

Year Qtr Hours
2006 1 2
2006 2 1.5
2006 3 5
2006 4 1.25
2007 1 3
2007 2 1.5
2007 3 6
2007 4 1

Here's what I want to do. I want to sum 2006 Qtr 2 to 2007 Qtr 1 Hours.
The result would be 10.75.

Additionally I want to be able to have a user prompted to enter start year,
start Qtr followed by end year, end Qtr.

Any ideas?