Suggestions on how to proceed - sub office (1 Viewer)

[intrep11]

I needs some advice on how to proceed.
Our company is devolving some operations to a sub office for 3 out of 11 business units. we have a central DB that we use to record monthly events the problem is that the parent group has to be able to update records for all units but the sub office is only allowed to see and edit their records

At the moment the data is all stored in one table and we use a Front end back end with local front ends for access I have a table to say if a unit is part of the sub office or not and in the sub office front end i have a Query which links the 2 tables together and filters the records to show only sub office

SELECT tbldatatable.*, tblShipName.Manager
FROM tblShipName RIGHT JOIN tbldatatable ON tblShipName.ShipName = tbldatatable.Ship
WHERE (((tblShipName.Manager)="nv"));
the remainder of the forms and querys all run from this query rather than the tables

the problem is the join makes it a non updatable record set

what is the advice on how to achieve what i need as i cant see a simple way to proceed
thanks
ND

 

[Pat Hartman]

It isn't the join that makes the query not updatable. Make sure that both tables have primary keys and that you have permission to update both tables.

 

[intrep11]

ok am lost now whats wrong here

Pat thanks for that

i can confirm that both tables have a primary key (which is not field they are joined on) and that you can write to the data in both tables when they are separate but thgat query is not updateable ????

any suggestions on where to look ?
thanks

 

[Pat Hartman]

The join should be from the primary key of one table to a data field in the other table if this is a 1-many relationship. If you are attempting to join the tables on two non-key fields, Access cannot determine the cardinality of the relationship (whether it is 1-1 or 1-m) so it assumes the relationship is m-m which produces a cartesian product which is NOT updatable.

Just because two tables contain a common field does not mean that they can logically be joined on that field. Use state for example. Just because Suppliers contains a state code and so does Employees doesn't mean that it makes sense to join these two tables on that field.

 

[intrep11]

thanks pat

Thanks Pat i have the tblShipName.ShipName indexed but it allowed duplicates and the 1 to many the wrong way round because i had been trying all combinations wondering why it didnt work

it should be this the index no duplicates and the query as

SELECT tbldatatable.*, tblShipName.Manager
FROM tblShipName LEFT JOIN tbldatatable ON tblShipName.ShipName = tbldatatable.Ship
WHERE (((tblShipName.Manager)="nv"));

interesting in all the books i have read to get this far that has never been explained (or more likley i missed it)

 

[RV]

Why you do persist in using inappropiate joins?
Follow Pat's advice.

RV

 

[intrep11]

?

RV sorry i dont understand which part of my posting dosnt follow pats advice ?

the shipname field in the 'tblShipName' table is a non duplicate index joined one to many(left) to a data field (ship) in the 'tbldatatable' table which gives me the updateable query as advised and allows me to set up the filtered results that i want for the sub office

if that is inapproprate can you please explain it is simpler terms that i can understand to assist me in improving my DBs for the future afterall i am a trainer not a programmer

 

[RV]

An index is not the same as a primary key.
It's mere a device to speed up data retrieval.

You stated

i

can confirm that both tables have a primary key (which is not field they are joined on)

You should always join using primary keys, as per Pat's advise and as per the reasons PAT came up with.
If there are more conditions to be met, simply add an additional WHERE clause.
In your case, the additional WHERE clause would be:

WHERE tblShipName.ShipName = tbldatatable.Ship

RV

 

[Pat Hartman]

intrep11,
you have made the query work by adding the unique index but you seem to be using the wrong field as the foreign key in the many side table. Rather than using the shipper's ID as the foreign key, you seem to be using the shipper's name. You really need to change this because you WILL have trouble with it at some point.

Let me say it again:
A foreign key is ALWAYS the primary key of another table. That's why it is called "key". Foreign is used in the context of "from a different place". So, a Foreign key is a key from a different place.

When you need to see the shipper's name for a report or form, you need to join to the table that contains it. I know that keeping the text name seems to work but you cannot enforce referential integrity when you do that.

1. Change the foreign key to be the other table's primary key.
2. Open the relationships window and create relationships between related tables.
3. Select the checkbox for Enforce Referential Integrity. If Access won't let you do this, you'll need to clean up the data. It means that you have BAD data in one of the tables.
4. In some cases you will want to select Cascade Delete. If you always use Autonumbers as your primary keys, you will NEVER use Cascade update.

 

[intrep11]

thanks

Pat / rv

Thanks my understanding is much improved
the DB was written a long time ago and i now dont have time to rewrite it.

access makes it much easier when you are learning to use query based relationships than table based ones and in most simple apps that is good enough
that is until you want to do more complex things a few years down the line

thanks again