Query based on budget year.

[Guest]

Hi!

I’ve a question about selecting records from a table into another table.
I’ve 2 tables, T_OE_Occurences and T_OE_Occurences_Queried.

What I want is a query that copies current budget year into the table
T_OE_Occurences_Queried. Our bookyear is from April till March.

Can someone help me?

Kind regards, Stefan van der Hooft.

 

[Jeff Boyce]

First, reconsider adding records to another table. One of the advantages of
using a relational database (i.e., Access) is that you rarely need to store
duplicate data.

It sounds to me like you could create a query that returns all the records
in a time period from your table named [T_OE_Occurences]. You'd do that
using the "Between ... And ..." criterion.

Once you have a query that returns the records you're seeking, just create a
new query based on that first one, for whatever other processing you were
looking to do.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Dear Jeff Boyce,

Thanks for your reply, however it did not fixed my problem.

Adding data to another table isn't the problem, but get the right data is
difficult.
I want that this query only show the current bookyear.
Eg; if it is December 2006 I want the months April 2006 - March 2007. When
it is January 2007 I want the months April 2007 - March 2007.

I know that there is a function DateSerial, but I cannot figure it out. An
example:
Between DateSerial(Year(Date()) -1 + (Month(Date())<=3),4,1) and
DateSerial(Year(Date()) +1 + (Month(Date())<=3),4,0)

Do you have any solution?

Kind regards, Stefan van der Hooft.

First, reconsider adding records to another table. One of the advantages of
using a relational database (i.e., Access) is that you rarely need to store
duplicate data.

It sounds to me like you could create a query that returns all the records
in a time period from your table named [T_OE_Occurences]. You'd do that
using the "Between ... And ..." criterion.

Once you have a query that returns the records you're seeking, just create a
new query based on that first one, for whatever other processing you were
looking to do.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi!

I've a question about selecting records from a table into another table.
I've 2 tables, T_OE_Occurences and T_OE_Occurences_Queried.

What I want is a query that copies current budget year into the table
T_OE_Occurences_Queried. Our bookyear is from April till March.

Can someone help me?

Kind regards, Stefan van der Hooft.

 

[Jeff Boyce]

See comments in-line below:

Dear Jeff Boyce,

Thanks for your reply, however it did not fixed my problem.

Adding data to another table isn't the problem, but get the right data is
difficult.

Adding duplicate data to another table IS a problem in a relational
database. Which table's copy of the data is the correct and current
version? What happens if the data is changed one place, but not the other?

I want that this query only show the current bookyear.

I'm not familiar with how you are using the term "bookyear". More
information please.

Eg; if it is December 2006 I want the months April 2006 - March 2007. When
it is January 2007 I want the months April 2007 - March 2007.

I still don't understand ... If it is December 2006, you want twelve month's
data starting with April of the year. But if it is January 2007, you
want -1 month's data, starting April of the year and ending March of the
year?!

I'll guess you meant April 2007 - March 2008 ... so does this change only
with the calendar year? That is, any day in 2006 uses the April 2006 -
March 2007, and any date in 2007 usesx April 2007 - March 2008, and so on?

If so, something like:

Between DateSerial(Year(Date()), 4, 1) And
DateSerial(Year(Date())+1,3,31)

By the way, if your underlying date-holding field actually has date & time,
you may need to deal with that, too.

I know that there is a function DateSerial, but I cannot figure it out. An
example:
Between DateSerial(Year(Date()) -1 + (Month(Date())<=3),4,1) and
DateSerial(Year(Date()) +1 + (Month(Date())<=3),4,0)

Do you have any solution?

Kind regards, Stefan van der Hooft.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

I think what you want is

DateSerial(Year(Date()) - IIF( Month(Date())>=4,0,1),4,1) will get the start
of the book year

DateSerial(Year(Date()) + IIF( Month(Date())>=4,1,0),3,31) will get the end
of the book year

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear Jeff Boyce,

Thanks for your reply, however it did not fixed my problem.

Adding data to another table isn't the problem, but get the right data is
difficult.
I want that this query only show the current bookyear.
Eg; if it is December 2006 I want the months April 2006 - March 2007. When
it is January 2007 I want the months April 2007 - March 2007.

I know that there is a function DateSerial, but I cannot figure it out. An
example:
Between DateSerial(Year(Date()) -1 + (Month(Date())<=3),4,1) and
DateSerial(Year(Date()) +1 + (Month(Date())<=3),4,0)

Do you have any solution?

Kind regards, Stefan van der Hooft.

First, reconsider adding records to another table. One of the advantages
of
using a relational database (i.e., Access) is that you rarely need to
store
duplicate data.

It sounds to me like you could create a query that returns all the
records
in a time period from your table named [T_OE_Occurences]. You'd do that
using the "Between ... And ..." criterion.

Once you have a query that returns the records you're seeking, just
create a
new query based on that first one, for whatever other processing you were
looking to do.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Stefan van der Hooft" <[email protected]>
wrote
in message news:[email protected]...

Hi!

I've a question about selecting records from a table into another
table.
I've 2 tables, T_OE_Occurences and T_OE_Occurences_Queried.

What I want is a query that copies current budget year into the table
T_OE_Occurences_Queried. Our bookyear is from April till March.

Can someone help me?

Kind regards, Stefan van der Hooft.