Fiscal Years Query

[Guest]

Hello,

I'm working on a property management database and would like to query store
opening dates to show which fiscal year they opened. Our fiscal years are
month/day years. Example: FY 2005 was 10/04/04 – 10/02/05. How can I
calculate these dates? There are over 100 stores with opening dates back to
1986.

Any help would be appreciated.

Thanks.

 

[MGFoster]

Hello,

I'm working on a property management database and would like to query store
opening dates to show which fiscal year they opened. Our fiscal years are
month/day years. Example: FY 2005 was 10/04/04 – 10/02/05. How can I
calculate these dates? There are over 100 stores with opening dates back to
1986.

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

You have to create a Calendar table that holds the info on when fiscal
years, quarters and months begin and end. Ex:

CREATE TABLE FiscalCalendar (
start_date DATE NOT NULL ,
end_date DATE NOT NULL ,
fiscal_year INT NOT NULL ,
CONSTRAINT PK_FiscalCalendar PRIMARY KEY (start_date, end_date)
)

Data:

start_date end_date fiscal_year
10/04/04 10/02/05 2005
10/03/05 10/01/06 2006
.... etc. ...

Then to find out when a store opened you'd do something like this:

SELECT S.store_id, C.fiscal_year As opening_date
FROM Stores As S, FiscalCalendar As C
WHERE S.opening_date BETWEEN C.start_date And C.end_date

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBRQxlM4echKqOuFEgEQLcNgCgvmEyAFWFq6m6ApRKclcniMbpuScAnAyH
NUtWawk+LVS20wHE+0mAzk0W
=xGDO
-----END PGP SIGNATURE-----

 

[Guest]

MGFoster,

I am fairly new to Access, would you please explain further? I've created
the table, but I don't know where the "DATE NOT NULL" comes in. Also, where
do I put the SELECT statement?

Thanks.

 

[MGFoster]

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

The CREATE TABLE command is a DDL (Data Definition Language) command.
If you put the command in a Query's SQL view & ran it, it would create
the table for you.

The SELECT statement also goes in the SQL view of a query. Of course
you have to have those tables and column names, otherwise the query
won't work.

Try reading the Access Help articles on SQL Reference for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBRQ+IzIechKqOuFEgEQLiXgCfT4jipnNlf84m9hcbFiWdqkL+pKEAoIYe
58teduYWWBHoNNceaf/GsqqD
=p51z
-----END PGP SIGNATURE-----