Transfer old orders

[Royal Presence]

Hi,

I am still working on my tree view. Thank you very much. I will get this concept fully one day. Right now, I am just using buttons and hyperlinks for the main menus.

Anyway, I have a new question:

Is this a good idea or a bad idea? I have an order processing database that has experienced several design changes. It is working lovely for the data entry people, and okay for the viewers, I'm working on that now. However, I want to take 2004 orders and put them all in a separate db when the end of this calendar year occurs and then just keep our
clients and addresses, etc., same table structure, order form, etc., for new 2005 orders. Then I can just refer via linking when people need to reference any history.

Sounds like a good idea and will help with production week dating, but does it make sense or am I on the wrong path.

Thanks.

Paula

 

[SydneyGeek]

Guess it depends on how many records you have. You can archive the old stuff to another database, but a better method might be to create a Yes/No field called Archive and build an Update query to flag old records.
Then, in the other queries in the db, put the Archive field there and set the Criteria row to 0.
If at some point you want to see all records, remove the criteria and they're back.

Denis

 

[Norie]

Do the orders have a date field?

If so you could create queries to only show old/new data based on that field.

I would say that you should keep all the orders in the same database, users mat want to query/analyze historic data.

 

[Royal Presence]

Yes the orders have a data field, lots of them actually, order date, ship date, change date. You have me thinking, now. Could my present reports pull up new (2005) data as is, or would I need to add anything to all my queries?

 

[SydneyGeek]

You can set a filter to run in code, when the report is opened.
IN Design view in the report, go to the report's event code (Event tab in Properties). Double-click the blank line next to On Open. You should see [Event Procedure] in this line. Now click the ... button on that line to put you in the code module. Enter these lines:

Me.Filter="Year([NameOfDateFieldToFilter]=Year([FieldThatHoldsTheFinancialYearCalc])"
Me.Filter=True

Change the field names to suit. Once it's working, you should see only the current financial year's data in the report.

An alternative is to go to the query behind the report and put a filter in the Criteria row.

Denis