Query update problems

[ian.caulfield]

Hello,

I've got a problem with an Access 2003 database. I have a table which
has a primary key over several fields (call them a,b,c) and has a field
called 'Value'. I have a query which generates all the relevant
permutations of a, b and c. I've created another query which left joins
the table onto the first query using a,b,c. This then returns all the
possible values of a,b,c and if there is an associated record in the
table, the value as well. However, I can't update the query at all if
the recordset type is set to 'Dynaset'. If I set it to 'Dynaset
(inconsistent updates)', I can alter the values of existing table rows,
but I cannot edit other rows from the query in order to add a record to
the table - I just get an error saying "The field 'a' cannot contain a
Null value because the Required property for this field is set to
True.". If I set required to No, then the record is entered into the
table, but with Null values for a,b,c.

If I run a maketable query on the inner query, and then alter the outer
query to join the two tables and set appropriate relationships between
them, it works fine - however I can't get it to work joining against
the query.

Can anyone provide any help/insight?

Thanks

 

[Amy Blankenship]

What is the actual end goal to what you are doing? It sounds like you've
got some serious problems with how your data is designed. Once that is
addressed, you'll probably find the rest falls into place.

 

[ian.caulfield]

Basically I have a table containing a number of items. The items are
grouped into collections, and each collection can be of a different
type (one could have four lots of three items, another could have three
lots of six, etc). The items table has an item value, the id of the
collection, the number of the lot within the collection and the number
of the item within the lot. There's a table which describes the
different collection types, and another which maps collection ids to
types (as well as containing some other data).

What I need is a query which will list all the collections and all the
items within them - including any where there isn't (yet) a record in
the items table, which I can edit to update or insert values into the
items table.

Thanks,
Ian