far left column in table

[dana]

Ihave created the far left columnn in a table--the column
that has a little box with a plus "+" sign in it, by
creating a lookup field.
1. what is this "plus sign" feature called?
2.How do I make the plus sign "work" so that it will show
me all records related to that field?

i want to be able to replicate this, as it's such an easy
and useful up categories of records without actually
creating a query and report.

thanks!

 

[Rick B]

That is a subdatasheet. Type that into help and you will get about five
very detailed articles on how they work and how to set them up. They are
great for one-to-many relationships!

Good Luck,

Rick B

 

[dana]

Thanks.

Here's my hangup now: I have a table of suppliers, with
unique NAMES and with unique SUPPLIER ID. the ID#
(autonumber) is the primary key. I really want the NAME
to be the primary key, but access says it's not unique.
but the names are unique within the supplier table.

I have 2 tables: suppliers and Loans. supplier ID joins
them.

if i have to keep supplier id, how do i create a lookup
column in my LOANS table so that user selects the NAME of
the supplier, and the NAME shows, but in fact the
SUPPLIER ID is selected, too, so that the info plugs into
the subdatasheet?

 

[John Vinson]

Ihave created the far left columnn in a table--the column
that has a little box with a plus "+" sign in it, by
creating a lookup field.
1. what is this "plus sign" feature called?

A Subdatasheet indicator.

2.How do I make the plus sign "work" so that it will show
me all records related to that field?

Click on it.

i want to be able to replicate this, as it's such an easy
and useful up categories of records without actually
creating a query and report.

Well... it's a "quick and dirty" way, but subdatasheets (and Table
Datasheets in general) have *very* limited utility. They'll get you
from A to B, but if you want to go on to C, D, E and K you'll find
that they won't let you.

An Access Database without Queries would be a bit like an Excel
spreadsheet without any cell expressions, just constants. Queries are
ESSENTIAL to any productive use of Access. Also, Forms (for onscreen
viewing and editing) and Reports (for printing) are much to be
preferred for any professional application.

If you really want to use subdatasheets in your table, you can view
the table's Properties and set the subdatasheet property to Auto, or
specify the name of the table which you want displayed as a
subdatasheet. In my databases the Subdatasheet property is set to
[None] for all tables, simply because doing so causes the database to
run MUCH MUCH faster.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks.

Here's my hangup now: I have a table of suppliers, with
unique NAMES and with unique SUPPLIER ID. the ID#
(autonumber) is the primary key. I really want the NAME
to be the primary key, but access says it's not unique.
but the names are unique within the supplier table.

I'd keep the ID. Names are large, can change, and are not unique;
that's three strikes against using them as Primary Keys. Do you want
the name to be the primary key of the Supplier table? Try running a
Find Duplicates query then - the program is saying it's not unique for
a reason!

I have 2 tables: suppliers and Loans. supplier ID joins
them.

if i have to keep supplier id, how do i create a lookup
column in my LOANS table so that user selects the NAME of
the supplier, and the NAME shows, but in fact the
SUPPLIER ID is selected, too, so that the info plugs into
the subdatasheet?

That's one of the many limitations of subdatasheets. You can't. Use a
Form with a Subform, with a combo box on the subform.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jamie Collins]

I'd keep the ID.
three strikes against using them as Primary Keys

Your arguments against aren't so strong:

Names are large ...

Who said storage is an issue? How many names does it take to fill a
2GB database?

... can change ...

Jet supports ON UPDATE CASCADE for this reason.

... and are not unique

Yes, I too know someone named 'John'. But in this sense, integers are
also not unique.

Why not go with ([NAMES], [SUPPLIER ID]) as a compound if this yields
a unique key?

IMO at the very least the advice should be to go with ([NAMES], ID) in
that order as a compound primary key (PK).

ID (autonumber) alone makes a lousy choice for a PK because with Jet
you have no choice over the clustered index (physical order on disk)
for a table. Instead the PK determines the clustered index. How often
do you use GROUP BY, BETWEEN or ORDER BY on an autonumber column?
That's a big strike against an autonunber as the PK. If the autonumber
is required for uniqueness only, just tack it to the end of a useful
column when defining the PK.

BTW the OP may want to review their column names. Spaces mean you will
constantly have to wrap the name in brackets and NAMES is a reserved
work in Jet 4.0, odbc, SQL Sever, etc.

Jamie.

--

 

[John Vinson]

Your arguments against aren't so strong:


Who said storage is an issue? How many names does it take to fill a
2GB database?

Not a major issue; but linking on a 4-byte Long Int requires less
memory and runs faster than linking on a 50-byte Varchar.

Jet supports ON UPDATE CASCADE for this reason.

With again a performance penalty.

Yes, I too know someone named 'John'. But in this sense, integers are
also not unique.

They can be assigned (e.g. with an Autonumber) to be unique within a
table.

Why not go with ([NAMES], [SUPPLIER ID]) as a compound if this yields
a unique key?

That's overkill if [SUPPLIER ID] is already unique; you can't make
unique uniquer!

IMO at the very least the advice should be to go with ([NAMES], ID) in
that order as a compound primary key (PK).

ID (autonumber) alone makes a lousy choice for a PK because with Jet
you have no choice over the clustered index (physical order on disk)
for a table. Instead the PK determines the clustered index. How often
do you use GROUP BY, BETWEEN or ORDER BY on an autonumber column?

That's obviously a major factor in SQL/Server; I don't see that it's
as relevant in Access.

That's a big strike against an autonunber as the PK. If the autonumber
is required for uniqueness only, just tack it to the end of a useful
column when defining the PK.

Again... unique is unique. Why use a 54-byte unique composite key when
you have a perfectly good 4-byte unique singlefield key, which can be
stored in related tables as a simple long integer?

BTW the OP may want to review their column names. Spaces mean you will
constantly have to wrap the name in brackets and NAMES is a reserved
work in Jet 4.0, odbc, SQL Sever, etc.

GOOD point.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jamie Collins]

I think you'll agree that neither your not my arguments are so strong

That's obviously a major factor in SQL/Server; I don't see that it's
as relevant in Access.

Eh? In SQL Server you have an explicit choice over the clustered
index, it doesn't have to be the PK (rarely is?) The only reason I can
think of for having an automnumber/IDENTITY column as the clustered
index would be for a table used as a simple event log i.e. in
timestamp order without using a TIMESTAMP column.

The clustered index is a fundamental principle of all relational
databases, including Jet (MS Access). Think of a paper copy telephone
directory: would you compile it in telephone number order? Or would
you go for something more useful such as (last_name, first_name, area)
etc? Choosing an automnumber as the clustered index is analogous to
listing in telephone number order and how often do you e.g. query data
BETWEEN two telephone numbers?

I guess with Jet in a way you *do* have a choice over the clustered
index by being a bit more 'clever' over the choice of PK. Say I had a
table with columns employee_ID (unique) and employee_last_name
(non-unique). In SQL Server I might choose employee_ID as the PK and,
because I most often use the table for reports in last_name order, I'd
create a separate index on employee_last_name and make it the
clustered index for the table. For Jet, I'd make the PK a compound of
(employee_last_name, employee_ID) in that order so that my clustered
index is on the useful last_name column and, because I want to use
employee_ID as a foreign key in other tables, I'd create a separate
unique index on employee_ID.

What would you go with as PK, just employee_ID? Doing so would mean
your data was always *stored* in the wrong physical order and every
query with an ORDER BY would have the overhead of rearranging into the
correct order. With my approach, I'll still have the ORDER BY clauses
but only the rows added since the last compact would actually have to
be ordered, therefore my queries would run faster than yours.

To say the clustered index is not as relevant in Jet/MS Access is to
miss an important performance issue. You site ON UPDATE CASCADE on a
PK column as a performance issue but surely GROUP BY, BETWEEN or ORDER
BY on a non-PK column is encountered much more frequently?

Jamie.

--

 

[Tom Lake]

What would you go with as PK, just employee_ID? Doing so would mean

your data was always *stored* in the wrong physical order and every
query with an ORDER BY would have the overhead of rearranging into the
correct order. With my approach, I'll still have the ORDER BY clauses
but only the rows added since the last compact would actually have to
be ordered, therefore my queries would run faster than yours.

MS told me that data is always stored in the order it's entered. The index
takes
care of the viewing order. If that's the case then the physical order of
the records
is meaningless and ORDER BY will always have to reorder the data by the
index
unless the data was put in in index order, which never happens at our place
since we
have many people inputting records simultaneously and there's no control
over
the order.

Tom Lake

 

[Brett Collings [429338]]

Dana, I think perhaps one important factor needs to be considered too.

That is, it is unwise to use tables for viewing data in. As you are
finding, tables generally have Foreign Keys and they are just
autonumber PK's. This makes the data viewed meaningless.

There are no shortcuts in data management. Queries in particular are
the powerhouse of your database. They bring together many tabes and
relate the data, they display the NAME not the ID as you want and they
provide the RecordSource for all your Forms and Reports.

Bite the bullet and use them with a passion - you'll be glad you did

Brett

Ihave created the far left columnn in a table--the column
that has a little box with a plus "+" sign in it, by
creating a lookup field.
1. what is this "plus sign" feature called?
2.How do I make the plus sign "work" so that it will show
me all records related to that field?

i want to be able to replicate this, as it's such an easy
and useful up categories of records without actually
creating a query and report.

thanks!

Cheers,
Brett

 

[Jamie Collins]

MS told me that data is always stored in the order it's entered.

Hmm, I think you must have misunderstood.

Here's something Microsoft has *published* on the issue:

Performancehttp://support.microsoft.com/default.aspx?scid=kb;en-us;209769
ACC2000: Defragment and Compact Database to Improve

"the Compact Database utility ... rearranges how the database file is
stored on disk... If a primary key exists in the table, compacting
re-stores table records into their Primary Key order. This provides
the equivalent of Non-maintained Clustered Indexes, and makes the
read-ahead capabilities of the Microsoft Jet database engine much more
efficient."

I can think of three scenarios where the statement "data is always
stored in the order it's entered" is true:

1) Where the primary key (PK) is date/time order anyhow, which
includes the PK column being a monotonic integer such as the
autonumber feature. Compacting would not usually change the *order*
because it's already in order (however pages may be re-written to
account for rows that have been deleted etc).

2) Rows added since the last compact will be added in date/time order
and will only get physically reordered when the file is subsequently
compacted.

3) You are using a Jet 2.x database: here is another MS publication:

http://support.microsoft.com/default.aspx?scid=kb;en-us;137039
New Features in Microsoft Jet Version 3.0

"Compacting the database now results in the indices being stored in a
clustered-index format. While the clustered index isn't maintained
until the next compact, performance is still improved. This differs
from Microsoft Jet 2.x where rows of data were stored the way they
were entered. The new clustered-key compact method is based on the
primary key of the table. New data entered will be in time order."

So the case is, the physical order of the records is a very meaningful
performance consideration. An ORDER BY should always be used but will
do less work if the physical order is already there. The real
performance advantages, however, come with GROUP BY clauses (i.e.
faster if the row to be fetched are already grouped) and BETWEEN
clauses (i.e. faster because the rows to be scanned are already
contiguous).

Jamie.

--

 

[Tom Lake]

Hmm, I think you must have misunderstood.

I can think of three scenarios where the statement "data is always
stored in the order it's entered" is true:

2) Rows added since the last compact will be added in date/time order
and will only get physically reordered when the file is subsequently
compacted.

That's what I was thinking of. Until a compact (which many people don't
ever do)
the data is stored as entered. It wouldn't make sense to reorder the data
on the fly
every time a record is entered, especially when you have 60 or 70 people
entering data
simultaneously!

Tom L

 

[Brett Collings [429338]]

Is somebody here viewing data in a table? If they are, they
shouldn't.

Brett

That's what I was thinking of. Until a compact (which many people don't
ever do)
the data is stored as entered. It wouldn't make sense to reorder the data
on the fly
every time a record is entered, especially when you have 60 or 70 people
entering data
simultaneously!

Tom L

Cheers,
Brett

 

[Douglas J. Steele]

I can think of three scenarios where the statement "data is always
stored in the order it's entered" is true:

1) Where the primary key (PK) is date/time order anyhow, which
includes the PK column being a monotonic integer such as the
autonumber feature. Compacting would not usually change the *order*
because it's already in order (however pages may be re-written to
account for rows that have been deleted etc).

The Autonumber would only be in date/time order if it's set to "Increment".
If it's set to "Random", I don't believe this would be true.

 

[Jamie Collins]

Is somebody here viewing data in a table? If they are, they
shouldn't.

Eh? I really don't know what to make of this comment! I have
permissions to read a table, it contains data, why shouldn't I view
the data?!

Jamie.

--

 

[Jamie Collins]

You seem to doubt the performance advantages of a clustered index. Of
course don't take my word for it. Google it or search MSDN (suggested
search phrase: "clustered index dictates the physical storage").

Otherwise I'm struggling to follow you...

That's what I was thinking of. Until a compact (which many people don't
ever do)
the data is stored as entered.

What point are you making? That not compacting yields no performance
advantage? A bit of an obvious one, that.

It wouldn't make sense to reorder the data
on the fly
every time a record is entered

This is what would happen in a more capable DBMS such as SQL Server.
In Jet you must do a compact to rebuild the clustered index. Is it
worth compacting every time a row is added? Probably not. Is it worth
doing regularly? Definitely.

especially when you have 60 or 70 people
entering data simultaneously!

Yes, all users must have exited to do a compact on a Jet database.
Even in SQL Server you'd be advised to take the table offline before
rebuilding its clustered index.

BTW are you telling me you have one of those mythical MS Access apps
that support more than 10 concurrent users without falling over <g>?

Jamie.

--

 

[Brett Collings [429338]]

Eh? I really don't know what to make of this comment! I have
permissions to read a table, it contains data, why shouldn't I view
the data?!

Jamie.

Well there's a bunch of them but mainly this centres around new
developers who still have an Excel mentality as soon as they see an
Access table.

Database Design 101
-------------------
A properly constructed Access database will have many tables, all
related using Foreign Keys. Viewing such tables is meaningless as
they contain just ID numbers. This confuses people and they try and
"fix" it.

Queries and Forms are the place to arrange, view, and sort data. This
thread is about the confusion and difficulties arising from trying to
use a table to producea a meaningful viewable dataset.

This highlights the need to move away from the table, use a query and
build a form based upon it.

Brett

Cheers,
Brett

 

[Jamie Collins]

Database Design 101

Brett,
I assume you meant to say, 'Many Jet (MS Access) databases will
contain a number of relationship tables (a.k.a. junction tables,
many-to-many tables, etc) which contain only Foreign Keys from other
tables in the database. Viewing the data in such tables will reveal
merely keys values from the other tables.'

MS Access hint number one: when you open the 'data view' of a Jet
table in the MS Access UI (note: other front ends are available), you
are no more viewing the 'actual' table than if you wrote a Query and
showed the result set in an appropriate control on a Form.

Jamie

--