Creating unlimited records

[John Ch]

Well at least for 10 years. I have a database that tracks reports submitted.
I am keeping data for a year and a half since two different reports that
need to be generated (end of calender year and end of fiscal year).

Every 18 months, once reports are done, I manually clear out the data to
start over. What I want to do is enter the date of the report by month and
year, and have access translate it to the cooresponding fiscal year.

I am thinking of doing a table that will take for example, July 2004 and
translate it to Fiscal Year July 2005 (Our fiscals start each July). This
way I need not clear any data or reset the tables manually since this table
can have 120 entries (12 months times 10 years) and be good for that amont
of time. Possible?

[MGFoster]

John Ch wrote:

Well at least for 10 years. I have a database that tracks reports submitted.
I am keeping data for a year and a half since two different reports that
need to be generated (end of calender year and end of fiscal year).

Every 18 months, once reports are done, I manually clear out the data to
start over. What I want to do is enter the date of the report by month and
year, and have access translate it to the cooresponding fiscal year.

I am thinking of doing a table that will take for example, July 2004 and
translate it to Fiscal Year July 2005 (Our fiscals start each July). This
way I need not clear any data or reset the tables manually since this table
can have 120 entries (12 months times 10 years) and be good for that amont
of time. Possible?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure it's possible. Access (JET db) can hold 2GBs of data.

The fiscal year is a function of your company, so you'd have to massage
the date. I've used The DateAdd() function like this (Debug window):

[Offset = (Months After Jan) + 1. In your case 6 + 1 or 7]

Get today's Qtr when 1st Qtr starts in July:

? datepart("q", DateAdd("m", Offset, date()))
4

Get the FY when FY starts July 1:

? year(dateadd("m", Offset, #6/1/2004#))
2005
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIAt2IechKqOuFEgEQIwUwCg8Xam2WMAhuWrPhm3/3XmCo/iGNcAn0J1
snmTUXdxTaGgcyW7NT+SbHhQ
=2fZv
-----END PGP SIGNATURE-----

[John Chiurato]

Thanks for responding. I really appreciate the help and will try it out.

"MGFoster" <[e-mail]> wrote in message
news:d5******************@newsread1.news.pas.earth link.net...

John Ch wrote:

Well at least for 10 years. I have a database that tracks reports submitted. I am keeping data for a year and a half since two different reports that
need to be generated (end of calender year and end of fiscal year).

Every 18 months, once reports are done, I manually clear out the data to
start over. What I want to do is enter the date of the report by month and year, and have access translate it to the cooresponding fiscal year.

I am thinking of doing a table that will take for example, July 2004 and
translate it to Fiscal Year July 2005 (Our fiscals start each July). This way I need not clear any data or reset the tables manually since this table can have 120 entries (12 months times 10 years) and be good for that amont of time. Possible?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure it's possible. Access (JET db) can hold 2GBs of data.

The fiscal year is a function of your company, so you'd have to massage
the date. I've used The DateAdd() function like this (Debug window):

[Offset = (Months After Jan) + 1. In your case 6 + 1 or 7]

Get today's Qtr when 1st Qtr starts in July:

? datepart("q", DateAdd("m", Offset, date()))
4

Get the FY when FY starts July 1:

? year(dateadd("m", Offset, #6/1/2004#))
2005
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIAt2IechKqOuFEgEQIwUwCg8Xam2WMAhuWrPhm3/3XmCo/iGNcAn0J1
snmTUXdxTaGgcyW7NT+SbHhQ
=2fZv
-----END PGP SIGNATURE-----