month year date

[slimjen]

All. I have a ms access 2003 database that has a month/year table for 2012 i.e. january 2012, february 2012 that is used on a form as drop down. i want to add a month year table that has future years. The user would run reports based on the month and year. I will also be archieving data every 90 days and every year. Is it best to set up the tbl with all the months and a separate tbl with the year and concatenate the month and year in the field the uses need?
I need something managable because eventually they need to select january 2013. By the way; I inherited this database as is.


Thanks

[June7]

Two tables probably faster to setup than the single table but either will work.

By archive, do you mean moving records to another table or just setting a field in table?

[slimjen]

Achieving data to another table so that the current table won't be so large. I am migrating this to SQL Server 2005 and I think i can just put this as a date field and format it mmmm/yyyy in the report so it will show month year(January 2012). This will eliminate the need for a separate date table; am I correct??

[June7]

If you want to only offer users dates for existing records, yes that is way to do it. I had thought you wanted to offer users a list of future month/year dates for selection to enter into a field.

You will have to do the search on a query that has a field constructed with expression to format the date as month/year and then apply the month/year criteria selected by user to the constructed field.

[slimjen]

You mean so that the dates will show up in the report as January 2012 or February 2013 .....? I can't just say mmmm/yyyy in the query?

[June7]

Whether or not you want to display the month/year on report is secondary to the sorting & filtering you want to do. If you want to sort & filter by month/year, need a field that month/year criteria can by applied to. If the date in table is a full mm/dd/yyyy (with or without time part), need to calculate a field in query to extract month/year.

[slimjen]

Got it! Thanks so much