Should I create dates?

[calvinsmith]

I have an access table with 8145 entries, each with a date from year 2004. I need to replicate these entries for year 2005. Is there an easy way to do this? If not, is the correct method for doing this? My ultimate goal is for the user to use a form, select a month, year, and range (i.e. 1 week, 2 week, etc) and it will display their results. This works great if I already have dates, but I will need it to create a date if it is not already in the database otherwise.

Thanks so much in advance!

-cal

 

[67peshawar294]

calvin,
check out the "dateadd" function.
You can make a query a "make table". Select the fields you want in a new query. In a blank field;
=dateadd(yyyy,1,[yourdate])
Change the query type to "maketable" save and then run. You will have a table with the new date. (if you don't want the old date, simply uncheck the "show" box.)
Or, if you want to preserve the original dates, forget the "make table" create a query, do the above in a blank field;
=dateadd(yyyy,1,[yourdate])
and leave the original date off of the new query. Then use the query for your forms. (rename the new query field from "Exp1:"to what ever name you want, like "newdate","date2005"
jim

 

[MichaelRed]

on the other hand, creating such, although not a huge item, is contrary to basic relational database concepts and practices.




MichaelRed

 

[calvinsmith]

Thank you both for the responses!

xaf294,

I have tried using your method, but I guess I am a little confused. Why am I creating another table? Is it best to have my data in multiple tables? The forms I am creating currently look at previous year's data, but we want them to look at all data present, filtered by comboboxes. This way, the user can see 2003 data, 2004 data, 2005 data, etc.

MichaelRed,

You mention that the above method is contrary to basic relational database concepts and practices. If you needed to gain the same results as I am, how would you do this? I appreciate your feedback!

calvin

 

[traingamer]

There is typically no reason to 'replicate a record' until you have unique data with which to populate that new record.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MichaelRed]

There is some degree of latitude depending on additional details of your process. You mention that you have ~~ 8K records for the (a?) calendar year, and want to replicate them for another (calendar) year ... to retrieve them via the date field (range). What is not offered, is the why you feel the need to do this. of to fill in dates. If there is no specific additional need for the records or missing dates, I would just construct the query using some simplistic date manipulation to substitute the year portion of the date entered with the year of the date(s) in hte existing record(s).




MichaelRed