By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Indexes and views

P: n/a
Hi!
1-I read in MS Access help that Indexes are automatically made when we
declare a primary key and they are used for fast searching and sorting
purpose. But i am still confused that how can i use Indexes,where i can
see the in my table? and if i want to make my own indexes, should i use
nique attribute column such as vehicle registration number etc?
2-Another thing i wanna ask is; in MS access when we make a query, Is
it a view also?
What is the difference between query and view in MS access.
I wrote 4 queries and they are stored and whenever i want, i can use
these queries(can we say them views also?).
3-Another confusion is about mapping derived attributes such as
GST(general sales tax) which is a fixed value in MS Acces table.Is it a
good approach to use derived attributes in access?Is it allowed? What
are the alternatives? Should i use such values directly in Query?
I need the urgent response from you guys.
Thank you
Umair Cheema

Sep 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<um**********@gmail.comwrote in message
news:11**********************@d34g2000cwd.googlegr oups.com...
Hi!
1-I read in MS Access help that Indexes are automatically made when we
declare a primary key and they are used for fast searching and sorting
purpose. But i am still confused that how can i use Indexes,where i can
see the in my table?
You can see the indexes used when you bring up the table in design view.
While in table design view, from the menu go

View->indexes

(if the indexes form is already displayed, then the above will actually
"hide" the index form).
and if i want to make my own indexes, should i use
nique attribute column such as vehicle registration number etc?
Well, using a unique attribute column is totally YOUR CHOICE. I mean, if you
want to increase search speed for a persons last name, the setting the index
to unique don't make much sense, as then the database would not allow more
then one person with the same last name? So, should you use unique?...well,
that is entirely up to you, and will be a case by case answer. There is no
"one" answer to this, but only use the appropriate index for what you need.
So, you can have several indexes on a table, and we often do. For generate
one-to-many relationships, we would not use the vehicle registration number,
but likely use a autonumber field (we do this, because we want our
relationships in eh database to continue to function and work EVEN WHEN you
don't have a vehicle registration number.).
2-Another thing i wanna ask is; in MS access when we make a query, Is
it a view also?
In database terminology, yes, you can consider a "select query" a view.
However, since we can also store update queries, and delete queries, and
append queers, then we don't call ALL SAVED QUIRES a view. But, for all
discussion purposes, you can call a save query that is a SELECT QUERY is the
same idea as a view. As mentioned, since in ms-access we can save ADDITIONAL
types of quires in the query builder, we don't call them views. So, sure,
you can most certainly think of a select query as a view.
What is the difference between query and view in MS access.
Well, as mentioned, none if you save a select query..but, since "other"
types of quires can be saved, then logic dictates that

all saved select quires are views
but
not all saved queries are select queries,
Therefore, all quires are not a view!!
I wrote 4 queries and they are stored and whenever i want, i can use
these queries(can we say them views also?).
Yes, you can use a query in virtually ANY place that you would have
previously used a table name. So, reports, forms, combo boxes etc can use
that "view" or what we called saved query in ms-access. For the most part,
you likely should use the term saved query when talking to ms-access people.
The term "view" is usually used when taking about oracle, or sql-server.
3-Another confusion is about mapping derived attributes such as
GST(general sales tax) which is a fixed value in MS Acces table.Is it a
good approach to use derived attributes in access?Is it allowed? What
are the alternatives? Should i use such values directly in Query?
It really is your choice. In most of my access applications, I have a table
like

GstSTartDate GstRate
01/01/1991 7.00
07/01/2006 6.00

That above thus allows me to fetch the correct gst rate based on today's
date, or perhaps the invoice date.

Start-up code then sets a global variable that can be used in forms, or even
sql

Set gblRstGstRates = CurrentDb.OpenRecordset("select * from tblGstRates
order by Gdate DESC")

gblRstGstRates.FindFirst "[Gdate] <= " & qudate(Date)
gblGstRate = gblRstGstRates!GstRate / 100
And, then I have sql querys, and forms

Public Function fGst() As Currency

fGst = gblGstRate

End Function
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Sep 22 '06 #2

P: n/a

Thank You it really helped me a lot.

Sep 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.