Data entry in a query

  • Thread starter Thread starter Buzz
  • Start date Start date
B

Buzz

I created a query but I cannot add data while in it? Isn't
it possible to add data to tables through a Query.

I have about 20 tables in this query
 
20 tables in a single query? That is a bit many. Are all of the tables
joined on primary and foreign keys? Is there a reason why you need to join
all of these tables?
 
Buzz,

There are many factors which render a query "not updateable". For
example, the use of Left Joins, or the use of Aggregate functions.
THere is a topic in Access Help called "When can I update data from a
query?" which gives more detail on this. I would say that it is
*extremely* unlikely that any query which includes 20 tables would be
updateable.
 
I created a query but I cannot add data while in it? Isn't
it possible to add data to tables through a Query.

I have about 20 tables in this query
Click to expand...

A twenty-table query? Almost certainly, NO. That's an ENORMOUS query.

Please explain what these twenty tables are, how they are related, and
what you're trying to accomplish. I suspect that you may have
overnormalized or be setting your tables up in a very unusual way!
 
I am building a database to track projects. Each one has
about 40 fields that need to be completed. I have created
tables containing fields like ProjMgrID & ProjMgrName.
There are probably 10 of these types of tables- all with
only 2 fields.

Then I created one table with miscellaneous info that most
likely will not be duplicated. In order to connect this
table with the 10 other ones I created a join table for
each relationship between one of the 10 and this one
bigger table since there are no foreign keys that I can
use. Is this not the best approach? Should I just put
everything in one big massive table? I thought that the
whole purpose of using Access was to normalize data?

Thanks for your help.

---Original Message-----
 
I am building a database to track projects. Each one has
about 40 fields that need to be completed. I have created
tables containing fields like ProjMgrID & ProjMgrName.
There are probably 10 of these types of tables- all with
only 2 fields.

Then I created one table with miscellaneous info that most
likely will not be duplicated. In order to connect this
table with the 10 other ones I created a join table for
each relationship between one of the 10 and this one
bigger table since there are no foreign keys that I can
use. Is this not the best approach? Should I just put
everything in one big massive table? I thought that the
whole purpose of using Access was to normalize data?

Thanks for your help.
 
You rarely need to include the lookup tables in the query. For instance if
you have a ProjMgrID field, I assume there is a lookup table. On your form,
use a combo box bound to the ProjMgrID field and set the Row Source of the
combo box to the lookup table. You have just removed one table from your
form's record source. Try the same with other lookup tables.

It isn't clear but if you have a number of fields that identify different
"roles" in the project, it might be advisable to normalize this by remove
all person roles from the project table and placing them in a related table
like:
tblProjRoles
==============
ProjRoleID
ProjID
PersonID
RollID
 
I am building a database to track projects. Each one has
about 40 fields that need to be completed. I have created
tables containing fields like ProjMgrID & ProjMgrName.
There are probably 10 of these types of tables- all with
only 2 fields.
Click to expand...

Hm. Just lookup tables should work though it might be a lot easier to
do the data entry on a Form; you could base the form on your table
directly, and have Combo Boxes set up to store the ProjMgrID while
displaying the name.
 
Back
Top