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]

Re: Creating unlimited records

John Ch wrote:
[color=blue]
> 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?[/color]

-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQIAt2IechKq OuFEgEQIwUwCg8X am2WMAhuWrPhm3/3XmCo/iGNcAn0J1
snmTUXdxTaGgcyW 7NT+SbHhQ
=2fZv
-----END PGP SIGNATURE-----

[John Chiurato]

Re: Creating unlimited records THANKS

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

"MGFoster" <me@privacy.com > wrote in message
news:d5Wfc.1302 1$A_4.5920@news read1.news.pas. earthlink.net.. .[color=blue]
> John Ch wrote:
>[color=green]
> > Well at least for 10 years. I have a database that tracks reports[/color][/color]
submitted.[color=blue][color=green]
> > 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[/color][/color]
and[color=blue][color=green]
> > 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).[/color][/color]
This[color=blue][color=green]
> > way I need not clear any data or reset the tables manually since this[/color][/color]
table[color=blue][color=green]
> > can have 120 entries (12 months times 10 years) and be good for that[/color][/color]
amont[color=blue][color=green]
> > of time. Possible?[/color]
>
> -----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:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQIAt2IechKq OuFEgEQIwUwCg8X am2WMAhuWrPhm3/3XmCo/iGNcAn0J1
> snmTUXdxTaGgcyW 7NT+SbHhQ
> =2fZv
> -----END PGP SIGNATURE-----
>[/color]