Access...Linking part of a table?

[Sypher2]

We have a Microsoft Access database with many tables. One in particular has close to a million records. What I would like to do is make a linked table that takes just the past 30 days of info from this large table (date is one of the fields).

I know you can link an entire table, but is it possible to do this with part of one? I need it to be linked so it's real time (changing the source table also changes the linked table).

Any solutions?

 

[Dreamboat]

What you'll want to do is have two tables. One linked, one not. Then, create a union query that combines the two tables in your query. Make sense?

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[CaptainD]

Maybe I'm not understanding exactly what you want but it sounds like you just need to run a select query with a date parameter that pulls all the records from Now() and the past 30 days. Does that sound along the lines of what you are looking for?



Table hold data, not pull data. You can set up a temporary table and have an insert query pull from one table and place it into the other but what would be the point. What are you doing or planning on doing with the information? do you need to just reference it, update it what?

 

[Sypher2]

Sorry I wasn't more clear, CaptainD and all. Here's what I want to do. . .

We have a custom VB app which pulls stats from the large million record table. This table has info from years back. Each day another block of records is added to the table. Each time the VB app has to pull stats it wades through a million records and is quite time consuming. The bulk of what we want to pull is during the past 30 days.

I don't think a select query would suffice here because somebody would have to run the query every day, plus if any of the records changed throughout the day it would not reflect until the select query was run again. It needs to be real time. (If a record is changed in the million record table, it changes in the past 30 day table/query.)

 

[ETID]

Maybe your million records table should be an inactive archive and your active table contain only 1 yrs worth of data, then on a schedule, append/update the new records to the archive.

Then select querys would run faster.

 

[CaptainD]

Thanks, that makes more since. A million record database, how often do you search the back data? Have you concidered archiving part of the data as "ETID" suggested. Another Idea might be to create tables for each year. You could add a drop down list that would select the table depending on what year you selected. (I'm assuming you have the code to the VB front end you are using since you're suggesting adding a linked table)You could add a button that runs a create database query to add new "year" tables as needed and add that new year to the drop down.