Re: Adding a new record to a joined table

[Mcdodre]

Hey all,
So i've spent a few hours trying to find a way to do this online to no avail, hopefully you can help me out.

I have a database which in part consists of 6 joined tables. Each is joined by a seperate ID field.
IE:
Table A:
ID1 (primary key)(used to identify information in TableA)
ID2 (Joined to Table B)



Table B:
ID2 (primary key)(used to identify the information in TableB, and Joined to TableA)
ID3 (Joined to Table C)

Table C:
ID3 (primary key)(Identifieing information in TableC, and joined to TableB)
ID4 (Joined to Table D)

And so forth.

The Problem is, we have to copy everything in the second from the "Top" table, which is laid out sorta like this..

Table D
ID5 (primary key) Linked to Table C
ID6 Linked to Table E
ID 7
ID 8 Linked to Table F


Basically we need to copy all of the values in Table D, change one value, and then add them back in to the table. Unfortunately, when we go to do this, because of the joins it won't let us create a new record.

I feel like there is probably a simple solution, but I can't seem to find it. We've tried just appending to Table D with no luck, and even starting from the bottom and creating duplicates of everything, but in order to do that it seems you have to simultaneously create a new record for each table all the way up.

Is there something simple I'm missing?

(Sorry about not being able to just post a screenshot or anything, hopefully it makes sense.)


Thanks so much for your time

[NTC]

no there isn't necessarily a simple solution. you are correct that the joins are the issue - but via a forum I don't see how one could analyze what could be modified to make it an updateable query.

You might address this in an entirely different manner. Sense you want to see all the related data (which is why you are joining all this). Strip back your query by removing tables one at a time - until it is updateable. So that you have the most data as possible in one query. Then make a form based on this query.

Then insert the other tables (that were removed from the big joined query) into the main form as subforms. The wizard will walk you thru linking the cross referencing fields.

This approach will allow you to see all the data - and update whatever field you want.

To make it pretty you can consider making forms for those sub tables - and inserting them rather than the tables themselves.

[Mcdodre]

Thanks for the reply,

I'll try that out. My concern would just be that it is part of a larger program, including upload tools which I haven't had a chance to look at. Its kind of a weird situation in that its an old program thats been updated, and now I've been asked to take a look at it with no experience in VBA for Access. Good fun. So i'm just wondering if I was to remove those tables then it might not upload correctly?

[Mcdodre]

Just re-re read your post.. I'm not sure if this is exactly what you meant, but can I remove tables down to the base one, and then edit them all indvidualy (Either bringing them in without links, or in another project) and then rejoin them?

Sorry, wish I understood it all better.

[NTC]

one would never remove tables

my first post describes a way to be able to enter data - - - while viewing alot of data from alot of cross referencing tables;

it is an approach to use when attempts to bring all the data together via a single query (with lots of joins) results in a non-updateable query.

this is all implemented in queries & forms/subforms; none of this affects any of your tables and so will not affect any imports/uploads involving those tables.