I was told by an access programmer that since the total number of records that's going to be in the db is around a few thousand tops it would be a good idea to index everything as that would speed things up. Is this true?
thanks,
scratch
Could anyone tell me about the pros and cons of Primary Key and Indexing?
View 3 Replies View RelatedG'day,
I have a products table which contains products from 4 vendors. The records have the following fields "Vendor", "Vendor Part Number"," Description", "Unit" and "Price"
I have indexed same by Vendor Part Number and Description.
The vendor's describe the same product using different descriptions.
VENDOR ....DESCRIPTION................................... .........PART NUMBER
Acme........TEKS METAL HEX+SEAL 10X16MM 100 (PKT)..123456-01
Banner.......METAL TEKS 10X16 MM..............................93a-1234
If I search by part number the search is extremely fast.
However a search on the DESCRIPTION field looking for individual words say 'Like TEKS*' and 'Like METAL*' are very slow. Obviously the indexing on the description field is probable based on sorting the 1st character of the Description field by A-Z then the second character etc.
I am wondering is there a smart way of indexing the Description field at the point of importing or creation of the Products Table where I could identify all the records with say 'TEKS' or 'METAL' or '10X16' etc. By doing this the search should be a lot faster.
Has anyone done this or do you have a pointer to an article that covers this. (I did try the search function without a suitable result)
Thanks in advance
In my table, I have a number field set to Index (dups OK). I have another field that I do not want to index but need "no dups." Any ideas?
View 4 Replies View RelatedIndexing fields can dramatically improve efficiency by speeding up sorts and searchs. My database tracks mail sent on various mail dates to multiple customers with insurance policies that are assigned unique policy numbers. I've indexed the policy number.
Question 1: Is there value in indexing a mail date field which has duplicate values even for a given customer that has received multiple mailings?
Question2 : Should one index every field in every database ? Are there scenarios where one would choose not to index a fied ? If so, can someone provide an example ?
Thanks in advance for your assistance.
Hi folks!
I'm a new user and need a hand. I'm running a query with two tables joined. I have 4 columns: Name, Day of Week, Date, Start Time. I want to create a 5th column that will number each record: 1,2,3,4,etc. I've tried researching help with indexes and primary keys etc. The main reason that I want to do this is to match up the uniques value with another query. The other query is identical with the exception of stop time versus start time. Bottom line is I want to automate a schedule for people but I'm trying to deal with shifts that go from one day into the next and I need a common denominator.
Any help would be appreciated.
Hello All
I have a field called MEMBERID
I wish to index the following so that all members in 2005 can be idexed A to Z and all members in 2006 can be indexed A to Z:
00125/05
At the moment, the last two digits that indicate the year of joining are ignored when sorted in a query, and they are all jumbled up. I have about two hundred antries for 2005, and 160 for 2006. What am I doing wrong?
Regards
Terence
London
I have an Access 2000 customer database with a hidden unbound text field that feeds customer history notes into a Main Notes section. The problem I have is the order of notes after they are given a default time stamp. At the moment the first note in the main field is the oldest but I would like to arrange the notes so that the newest note is placed at the top of the field. Can this happen?
Below is the code I use for the Notes section, where Text_14508 is the unbound hidden text box and Details is the Main Notes section.
Thanx in advance
Private Sub Text_14508_AfterUpdate()
Me.Details = Me.Details & vbCrLf & Now() & vbCrLf & Me.Text_14508 & vbCrLf
Me.Text_14508 = ""
End Sub
Private Sub Open_Notes_Click()
Text_14508.Visible = True
End Sub
I need an advice by indexing fields with low entropy.
I have some large tables with some columns that i use in selects, but they store only few vaues like (0 or 1) or ("R" or "N" or "F").
Somewhere i read, that fileds like these (with lov variety of values) do not need (or should not) to be indexed.
http://www.dbpd.com/vault/9801xtra.htm
Is this true also for access databases accessed via ODBC, or not?
Please give me an advice.
Marcel
I have a complex series of calculations to perform, and I have found the best way to achieve my end is to create new tables by the Make Table queries.
The trouble is I have many tables to create and the downstream tables refer to the previously created tables; I have recently discovered that queries perform much quicker if they are indexed (duplicates OK). I've looked at my tables created by the MakeTable queries, and they all seem not to be indexed.
How do I make them indexed automatically?
I would like to Create a database for indexing / organizing PDF documents. My Company currently use a 3rd party accounts program and we raise sales and purchases using numerical numbers ( but we can Jump to a new range of numbers within the program) for example if the purchase order numbers become simliar to the sales order numbers. But this 3rd party software does not let us scan documents into it hence the need for an alternative database.
For the data base I was thinking if only creating a index of the sales order numbers to start with. the first few question I have are.
1.) Should I Use the Autonumber as my first index column or should I replace it with our own sales order numbers in that column.
2.) is there a way to autopopulate the sales order numbers in a table for historical sales numbers.
3.) can you scan directly into Access
How to start planning the design off the database as I know this is almost the most important aspect of a database.