Whats the use

[Guest]

I have been banging my head up against a wall trying to get my databases to
work. I have posted questions in the Discussion Groups and have gottne very
helpful, yet at times vague answers. My major problem is that I have a
database with 8 tables that are linked to one Main table in a query. I am
unable to make any changes or additions on my form. What is the use of using
a relational database if you cannot have more than one relationship (two
tables) present? This doesn't make any sense to me and is proving to be
extreemely frustrating. Any words of advise would be greatly appreciated.

 

[Steve Schapel]

RTimberlake,

There are certain conditions that determine whether or not a query is an
"updateable" query. For example, a query which is based on a Totals
Query, or which contains a domain aggregate function, will not be
updateable. Nor will a query that contains a join between tables where
one of the joining fields is not indexed as unique. Nor is a query
containing a many-to-one-to-many relationship structure. Maybe if you
can post the SQL view of the query you are trying to work with, someone
may be able to suggest a resolution.

 

[Guest]

There are several ways to get around this issue. The one that typically
works best is nesting subforms (up to two). This allows you to have 4 tables
present and still be editable.
If you post a more specific problem, perhaps we can troubleshoot a solution
for you.

FYI: I do agree with your frustration; it can make things very complicated.

 

[Guest]

Thank you. I will do my best to get it posted

RTimberlake,

There are certain conditions that determine whether or not a query is an
"updateable" query. For example, a query which is based on a Totals
Query, or which contains a domain aggregate function, will not be
updateable. Nor will a query that contains a join between tables where
one of the joining fields is not indexed as unique. Nor is a query
containing a many-to-one-to-many relationship structure. Maybe if you
can post the SQL view of the query you are trying to work with, someone
may be able to suggest a resolution.

 

[Chaim]

For what it's worth, I went through the same thing when I started with
Access, just 7 short months ago. There is certainly a lot of stuff that I do
not know about maneuvering in Access, but this news group is a superb
resource- if you are inclined to learn this way.

I looked up your other posts. Am I correct in inferring that you are under
the gun? The frustration level is that of someone who is stressed by a
rapidly approaching delivery date.

Not that any of this helps your specific problems, but maybe just knowing
that others are suffering/have suffered through the same kinds of
angst/frustration/stresses can help.

Have you made any progress since you started posting? Are you throwing up
your hands in despair? Or is this simply short term frustration/venting?

Would it help, do you think, if you started over? Maybe- since you did not
initially- describe the problem you are trying to solve? It seems that
you've asked questions that are not cutting to the heart of your problem, so
the answers are not as helpful as they could be.

Don't give up yet!

 

[Albert D.Kallal]

Well, keep in mind that queries are MOSTLY used for retrieving data from a
single table.

When you retrieve multiple tables, then usually that is for a REPORT.

The means by which those add tables are to be edited is generally accomplish
by building a form, and for those additional tables (which represent the
"many" side of a relation), you use sub-forms. So, a rule, you will NOT base
your forms on a quires that are the result of multiple tables. You simply
use combo boxes, listboxes, or more often sub-forms to display that related
data. So, as a rule, you will NOT base your forms quires that join in
multiple tables. This apples to Oracle, Sql server, and yes, even ms-access.

So, queries with related data is mostly for reports, but not forms to edit
the data. The reason for this that you OFTEN need to have MANY records in
the "many" side. If you create a query that joins in that many side, then
got "many" records, and that does not allow you to edit, or separate the
data you got into logical parts. For example, if you join in a child table
to the master record, you will get "many" records

The Main record will be returned over and over for EACH line of detail. How
is a end user going to edit this data?

And, if the relationship is a one to one relationship, then why not just put
those fields in the main table? (this is called normalizing).

So, the real art of software development is building a user interface that
allows the user to edit that data in pieces in one form (and of course the
needed sub-forms). So, as a general rule, don't use quires to join data for
editing...it just don't work that way....

-
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[John Vinson]

I have been banging my head up against a wall trying to get my databases to
work. I have posted questions in the Discussion Groups and have gottne very
helpful, yet at times vague answers. My major problem is that I have a
database with 8 tables that are linked to one Main table in a query. I am
unable to make any changes or additions on my form. What is the use of using
a relational database if you cannot have more than one relationship (two
tables) present? This doesn't make any sense to me and is proving to be
extreemely frustrating. Any words of advise would be greatly appreciated.

I'd second Heather's suggestion. DON'T use datasheet view for anything
other than debugging; and DON'T try to create One Great Master Query
which does everything. It's simply not the correct tool for the task!

Instead, base a Form on your main table. Put Subforms, based on the
related tables, onto this form - you may want to use a Tab Control on
the form, with one or two subforms on each tab page. If the Master and
Child Link Properties of the subform controls are the linking fields
between your related tables, Access will keep the data in synch for
you.

Your nine-table query, joining everything, may still be of use as the
recordsource for a Report - just don't assume that it's the only (or
even ANY) way to edit data.

John W. Vinson[MVP]