Creating an Index Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have one large Table (200,000 records). Each month I get another 25000
records. Some fields contain many duplications, eg The date field contains
the same date for all recods in one month. For use in various queries, I need
to maintain a Table that contains only a short list of these dates as a kind
of index.

I need this list to be updated whenever a new date appears in the main
table, so it is automaticaly uptodate. I need this index table for speed, I
have tried Indexing the main table, but to query it for a grouped list of the
dates is too slow. I thought I could use relationshipe with relational
integrity to update the small unique index table, but this only wants to work
the wrong way (insists on the Primary table being the index table and
updating or deleting records in the Main table.

How should do this?
 
-----Original Message-----
I have one large Table (200,000 records). Each month I get another 25000
records. Some fields contain many duplications, eg The date field contains
the same date for all recods in one month. For use in various queries, I need
to maintain a Table that contains only a short list of these dates as a kind
of index.

I need this list to be updated whenever a new date appears in the main
table, so it is automaticaly uptodate. I need this index table for speed, I
have tried Indexing the main table, but to query it for a grouped list of the
dates is too slow. I thought I could use relationshipe with relational
integrity to update the small unique index table, but this only wants to work
the wrong way (insists on the Primary table being the index table and
updating or deleting records in the Main table.

How should do this?
.
Click to expand...
 
You should seldom, if ever, duplicate data in the same or different tables.
Is your [date] field really a date, or a text value for the month? You'll
be better off recording a real date/time value; there are many functions
built-in to Access to handle date calculations. This will also give you the
greatest flexibility should you need to aggregate the last six weeks rather
than last month.

Try again and create an index in your main table for the [date] field. Your
queries should, of course, include that indexed field, but only others as
needed. I have databases with many thousands of records that run very
quickly when searching/filtering/sorting indexed fields.

-Ed
 
Duplicate data for me is inevitable. The data I receive is in large Excel
files. 100+ fields and about 25,000 records per month. The data is "archived"
in a single Access table "as is" with just a simple number for YYMMDD to
identify the date. Using an actual date field may be preferable, but that is
not really my problem. In truth it is not just one index table I want to
update, but several.

I have kind of resolved to make small seperate tables for the summaries, and
to update these with queries all run from one macro. It is a bit messy as it
requires many queries. I just wonder if there were some other way to keep
these tables updated. Referential Integrity in relationships would have been
perfect, but it appears only to work one way, and that's the opposite of how
I am working. Indexing a field sounds good, but if I have 250,000 records
where the date field contains an assortment of only 10 different dates, if I
query the table just to tell me the last date, it takes a while. Whereas if I
have a seperate table updated with all the ten dates, querying that table
takes no time. Speed is important (though the monthly update of the data can
run for as long as it likes), it is the speed after the summary/index tables
are created that matters. In a way I need the original data "not normalised"
(for archiving) and create the summary tables to give me the benefits of a
normalised database.



Ed Robichaud said:
You should seldom, if ever, duplicate data in the same or different tables.
Is your [date] field really a date, or a text value for the month? You'll
be better off recording a real date/time value; there are many functions
built-in to Access to handle date calculations. This will also give you the
greatest flexibility should you need to aggregate the last six weeks rather
than last month.

Try again and create an index in your main table for the [date] field. Your
queries should, of course, include that indexed field, but only others as
needed. I have databases with many thousands of records that run very
quickly when searching/filtering/sorting indexed fields.

-Ed


Ham said:
I have one large Table (200,000 records). Each month I get another 25000
records. Some fields contain many duplications, eg The date field contains
the same date for all recods in one month. For use in various queries, I
need
to maintain a Table that contains only a short list of these dates as a
kind
of index.

I need this list to be updated whenever a new date appears in the main
table, so it is automaticaly uptodate. I need this index table for speed,
I
have tried Indexing the main table, but to query it for a grouped list of
the
dates is too slow. I thought I could use relationshipe with relational
integrity to update the small unique index table, but this only wants to
work
the wrong way (insists on the Primary table being the index table and
updating or deleting records in the Main table.

How should do this?
Click to expand...
Click to expand...
 
Well, it sounds as though you decided to stick with your original plan. I
understand that you have time invested, but can speak from experience, that
those types of non-normalized schemes always come to failure point. And
it's considerable harder to build the needed foundation after the house is
occupied.

That said, look into compound indices and totals queries. You can build
indexes on a combination of fields (date+part#+empID). A "totals" query
(rather than the standard "select" kind will easily find the "last" (or
"first" or top 10 or count, total, and many others).

Also there are functions to convert text to real dates and many ways to
display the results. Most database treat dates as serialized numbers -
counting the number of seconds since 1800 - which is why you can do real
math on date/time values.

Good luck -Ed
 
You don't need to use a relationship, or referential integrity here.

Simply put a index on the date field and make the index NOT allow
duplications.

You can the simply make a append query to grab all the dates from the main
file, and append it to the smaller table. Any duplicates will simply be
ignore when you run the append query.

And, for reporting flexibly (and performance), you likely should convert the
text date data to a actual date field..as this allows you do grab data by
month, or year etc. (but, lets fry one fish at a time here).
 
Back
Top