Incremental Table Updating

[pyrman]

This is probably a simple question, but I appreciate suggestions.

There is a large database (7+ years worth of data). I want to use the "Make Table" query to get a more manageable set of data. How can I update the table with new data from the database without having to make a whole new table?

Thanks.

[RayMilhon]

This is probably one of the most confusing questions I've seen here. You cant use a "Make Table" Query without Making a new table. You can create a query that uses date parameters to limit your results or you can use a makt table query to make a new table with a subset of your data. If your large database is SQL Server You can use SQL Server to create a view of your table within specific parameters. Unfortunately you can't do that in MS Access.

[RayMilhon]

However, if you include the questions I've posted here it's not even in the top 10 most confusing questions here.

[pyrman]

Sorry for the confusion of my question. Let try to clear it up a bit. I used the "Make Table" to get a subset from a large database. Is there a was to use the "Append" or "Update" options to add new records from the database to the table without having to create an entire new table? Is there a different or better way that would allow the table to be updated with records? Or, would it be better or take just as much time to generate a whole new table every day or so?

Hope that makes a little more sense.

Thanks.

[RayMilhon]

Much better.

in the query view there is a button next to the make table button that is an append button. Click on that and you will append new records to the existing table. Now you'll have to make some decisions based on how the data is entered into the larger table.

For example, here a new record can be entered today for a date up to 3 months in the past. So when I'm using the append I want new records but the date can't be used so I use a left Outer join between the table to be appended to and the linked master table and only append records that do not exist in the local table and the date >= DateAdd("m",-6,CDate(month(Now()) & "/01/" & year(now()))

Your situation may be different so the main thing to decide is what is the time frame for adding new records that will give you all of the records you need.


Although be advised you may be better off given your circumstances to create a view of your larger table within the Database holding your large table and just link your Access db to the view. Your data is then continually updated on the server. You do not need to run any kind of append on your local machine and depending on the structure of your append that could take a considerable amount of time.