multi month / multi year question

[thesven]

I have a database (currently one table) that uses one form and one report every month. In addition to this it will be used every month for the next 5 years.

What I'm trying to figure out if I need to replicate the table, naming it monthly along with replicating the associated form and report for month to month and year over year comparisons? Is there an easier way to do this? Seems very redundant to me obviously.

Structure-
Form = April 2009, May 2009, etc., etc.,
Report equals same as above

Each report has to be titled for a specific month and a specific year. I'm trying to avoid manually recreating headers showing month and year.

Does this make sense? No apologies but I'm new at this.........and I do appreciate the help!

 

[pbaldy]

No way no how! Have the data in one table that includes a date field, and pull out your data with a query that gets the data you need. That enables you to get the data for a day, a week, a month or a year, as needed.

 

[thesven]

Thanks for the quick reply.

Based on your answer I'm not sure how to modify my form to nadle all of the dates. There is a photo involved with every date in the form and report.

Example

If I use a date picker and a form is filled out in April 2009 and then the same form is used for May 2009 the table field data will get overwritten.

Should I then use a menu of Monthly Forms are Reports to eliminate this issue?

Thanks.

 

[raskew]

Hi -

Assuming your table has a date field identifying action occuring during a particular month/year, you should only need:
1 Table
1 Query (assuming that's behind your form/report)
1 Form
1 Report

It'd be helpful if you'd post a sample db.

Bob

 

[pbaldy]

I'll get out of Bob's way.

 

[thesven]

Hi Bob,

It will take a me a bit to post db as the data itself is of a confidential nature. I don't have a date action as I am not VB savvy if in fact this is what you are making reference to.

As lame as it sounds I would rather not create 60 date columns and have a form to select the reporting month if you know what I mean.

I have to figure out how to show you my DB. Will a heading column screen shot work??

Thanks again for your help.......I've been at this for a week.

 

[raskew]

I would rather not create 60 date columns

...whoa, we're talking spreadsheet, not relational database, when there's a desire to create 60 date columns

Let me show you how you can use one query (this example is based on Northwind's Orders table) to return all records within a specified month.

When run, the user will be prompted [enter mm/yyyy]. If the user enters 09/1994, the query will return all records with an OrderDate between 1-
September-1994 and 30-September-1994. If the user enters 02/1995, the query will return all records with an OrderDate between 1-
February-1995 and 28-February-1995. There's no instance of adding additional date columns. Place this query as the source for a report and again, when the report loads the user will receive the [enter mm/yyyy] prompt and the report will display details of the specified month/year.

Parameters [enter mm/yyyy] text;
SELECT Orders.*
FROM Orders
WHERE (((Orders.OrderDate) Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1));

A screenshot might shed a little light, but it'd be more helpful to see the entire db, with your table populated with a few sample records (they needn't be actual information, just something that simulates how the table is populated)

Bob.

 

[thesven]

Hi Bob,

What do I need to input to the form to allow the database to hold multiple versions of the data based on the date input?

To clarify:
Have a form that needs to be completed monthly while maintaining all prior data.

Thanks

 

[raskew]

Adding records to a table does not eliminate previously input data. If that's happening, it's some process built into your form.

Unless you've set your date field as unique, duplicate dates are not going to provide a problem. Once again, I suspect your form may well be the problem.

Bob

 

[thesven]

Bob,

It likely may be my form but I'm not adding new records as much as I am adding new details and photos based on a specific date.

I've switched to creating two tables:
One houses the data that will never change
The other houses the data that will always change

I've tried joining the two so that once a new date is entered along with updated "changing" data this will continue to build my database containing the new data and photos for historical data.

I have failed so far and have decided I am nore Access challeneged than I thought.......I'm ready to pay someone to look at what I'm doing wrong.

Have any good reliable sources that might assist me?

 

[raskew]

Once again, a sample db would go a long way. We don't know what's going on with your form if we can't see it and dissect it.

Bob