query/table

[Matthew]

I have two tables that I would like joined with a Cartesian product query
(25 records in each table produces 625 records). However, there is a catch.

I need to have the resulting query/table have additional editable fields
that can be distinct for each record. i.e., defaults to "0" but we can
change this to anything and have that saved.

Also, it needs to be updated when data is added to one of the source tables.

Matthew

 

[John Vinson]

I have two tables that I would like joined with a Cartesian product query
(25 records in each table produces 625 records). However, there is a catch.

I need to have the resulting query/table have additional editable fields
that can be distinct for each record. i.e., defaults to "0" but we can
change this to anything and have that saved.

Also, it needs to be updated when data is added to one of the source tables.

Matthew

You're out of luck, as the question is stated. A Cartesian join query
will not be updateable, since there is no way for Access to uniquely
identify which of the 25 records you want to update. And of course a
query cannot contain editable fields unless they reside in a table.

The only way I can think of doing this is to have a separate 625 row
table that you empty, populate using an Append query, edit, and then
empty again when you're done with it. How you'll migrate the data back
to the base tables is a real problem though - with this design, there
is a) no place to put the extra fields and b) a real ambiguity if you
edit six of the 25 records in TableA and leave the other nineteen
alone - what's the correct value to put back into TableA!?

Could you explain what real-life problem you're trying to solve?

 

[Matthew]

I have a part catalog of wood products. It has the main table with part
numbers and another table with the wood types.

If I add a new wood type, it needs to be added to all the part numbers.

It would be fine to create a third table and run an append query when
something changes or something like that.

Matthew

 

[John Vinson]

I have a part catalog of wood products. It has the main table with part
numbers and another table with the wood types.

If I add a new wood type, it needs to be added to all the part numbers.

Why? You will automatically, every time, have parts in stock for every
type of wood? Can't you generate them as needed?

It would be fine to create a third table and run an append query when
something changes or something like that.

If that's really what you want then yes, this would be a good
solution. I don't think the Cartesian query is going to help you
particularly!