Append Query question

[MiserableLawStdnt]

Hello,

I have a large database with financial data dating back to 2008. The problem is when I update my queries, it takes like 30-minutes. I was told I can drastically cut this time if I didn't update 2008-2010 data, as that will not change. The way to do this is some how related to an append query. My guess is I would append a query that pulls 2011 data, onto the the financial data table. And then only update the 2011 query.



However I am not sure about the specifics. I was wondering if someone could sort of help me out and give me a step by step. Thank you.

[CraigDolphin]

In the query you're using to calculate your updated results, find the date field and insert a suitable criteria.

For example, if the field has raw dates then you could use

Criteria: Between #1/1/2011# and #12/31/2011#

Or, you could instead add a field that calculates the year based on the date field value:
MyYearField: Datepart("yyyy",[DatefieldName])
And add the criteria:
Criteria: 2011

Either situation will limit the query to working only with those records in 2011.

However, you could use similar logic to further reduce the amount of work that the query is doing depending on the real world situation.

For example, if your updated results only need to be calculated for the past 30 days, then you could calculate how mnay days have passed between the transaction and the current date, and then use a criterion parameter that limits the query to only those transactions with an elapsed time of less than or equal to 30 days.

DaysElapsedField: Datediff("d",[DateFieldName],Date())
Criterion: <=30

If you're unfamiliar with adding criteria to queries, try doing a google search on parameter queries and you should find some examples.

[MiserableLawStdnt]

Alright, let me explain in a bit more detail.

I have an Excel table which is pulling data from a 3rd party accounting software (FACTOR). This excel spreadsheet is then linked to an Access table. So there is in fact no query that is being used to update the Access table (2008-2011). as far as I am aware I can not add any criteria to the Access table to only update the 2011 info when I open the linked table. Right?

So my idea is, to not update the Access table which is linked to the Excel spreadsheet, but rather only update the Appended Query in which I will have the criteria for only 2011+ data. Does that make sense?

If there is a way I can enter some criteria to only update the 2011 data in he linked table, please let me know. Thanks.