Automatic Updates

[Confused]

I noticed that on the Northwind Database if you update one table, say
customer name for example, it updates everywhere like in the orders table.
I have the same one to many relationship set up and my tables don't update
automatically.

If it's not the relationship, then what makes it work?

Please help.

 

[Clif McIrvin]

I noticed that on the Northwind Database if you update one table, say
customer name for example, it updates everywhere like in the orders
table.
I have the same one to many relationship set up and my tables don't
update
automatically.

If it's not the relationship, then what makes it work?

Please help.

First guess is that you have the same data *stored* in multiple
locations, rather than having multiple *forms* getting that customer
name from only one location -- which is what Northwind does.

If you dig "underneath the wrapper" in Northwind, you'll find that the
various UI (user interface) screens that you see are Access *Forms*, not
*Tables*.

Here's a list of references with good information on getting acquainted
with working with relational databases; Access (JET) in particular ....
Crystal Long's tutorials are a good place to start:

--
(Thanks to John W. Vinson [MVP] for this info
"Access has a steeper learning curve than (say) Word or Excel; not
least, in order to make productive use of the program you have to
understand the theoretical basis of database design - a concept called
'Normalization'. It is very logical and not at all difficult once you
get the concepts down."

Here are some tutorials and other resources that you should find
helpful:

A free tutorial written by Crystal (MS Access MVP):
http://www.accessmvp.com/Strive4Peace/Index.htm
also at http://allenbrowne.com/casu-22.htmlMVP

Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Here's a primer with 23 well defined, well written, clearly named
chapters:
http://www.functionx.com/vbaccess/index.htm

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

Access MVP Larry Linson has additional Access resources in a list at:
http://sp.ntpcug.org/accesssig/default.aspx

From Access MVP Tom Wickerath:
May I recommend that you help jumpstart your Access-related knowledge by
downloading a copy of a Word document that I have available in zipped
form? I
call it "Access Links". The first four pages include important
information
that anyone working with Access should be aware of. This includes
reserved
words and special characters, naming conventions, database design, etc.
My
advice is to avoid using any reserved words (Name, Date & Description
are
three prime examples) or special characters (#, $, spaces, etc.) in
anything
that you assign a name to within Access.
http://www.accessmvp.com/TWickerath/

 

[Jeff Boyce]

I've not looked into Northwind in detail, but suspect what you're seeing is
a result of the use of the "lookup" data type in table definitions. From
your post, it appears you are looking directly in the tables.

While Access tables may look like spreadsheets, they aren't.

You (and your users) are much better off using Access forms to display/edit
data, and letting the tables handle the storage of data.

Back to "lookup" datatypes -- they display one thing (in a table), but
actually store something else. Confused?!

Many Access developers who post here (and in .tablesdbdesign) are adamantly
against using lookup datatypes in tables.

If you know what you're doing, if you are the only one who will ever work
with the database, and if you have an infallible memory (so you can remember
the idiosynchracies of lookup datatypes a year from now), go ahead and use
them ... and hope.

The preferred approach is to use the underlying key's data type, and to use
tables and combobox controls to do "lookup".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Confused]

Here's what I'm trying to do that led me to this. I do appreciate all of the
advice on the topic and will look into the training.

I have a table Customers with a subform Contacts, which turned out to be way
easier than I tried to make it.

But when at the top of the form the customer name shows up, the customer
name doesn't show up on the contacts subform but the Customer ID does.
Because I read some examples and they said there's no need to be the Customer
Name twice on the same form. So what I end up with when I type in the
contact information is that of course the one thing missing on that Contacts
TABLE is the contact name. Makes sense but to remedy this and to keep a
user from having to simply type the Customer name again, I looked at
Northwind and I thought the way they were doing this is by creating a combo
and setting the Control source to- in my case Customer ID but taking it from
the Contacts Table. Then doing Select Distinct from Customers.customerID,
customes.customername from Customers order by Customer name.

So is there a way to not have to fill in the customer name?

 

[John W. Vinson]

Here's what I'm trying to do that led me to this. I do appreciate all of the
advice on the topic and will look into the training.

I have a table Customers with a subform Contacts, which turned out to be way
easier than I tried to make it.

But when at the top of the form the customer name shows up, the customer
name doesn't show up on the contacts subform but the Customer ID does.
Because I read some examples and they said there's no need to be the Customer
Name twice on the same form. So what I end up with when I type in the
contact information is that of course the one thing missing on that Contacts
TABLE is the contact name. Makes sense but to remedy this and to keep a
user from having to simply type the Customer name again, I looked at
Northwind and I thought the way they were doing this is by creating a combo
and setting the Control source to- in my case Customer ID but taking it from
the Contacts Table. Then doing Select Distinct from Customers.customerID,
customes.customername from Customers order by Customer name.

So is there a way to not have to fill in the customer name?

Yes.

Using a Combo Box based on the customer table, bound to the CustomerID but
displaying the customer name.

I think you may be missing the point - you SELECT an existing customer, using
the combo box; you don't RE-ENTER the customer information.