Subdatasheet Help

[LeAnn]

I am a new user of Access and in desperate need of help.

In my database I have a table that has a subdatasheet and then that
subdatasheet has a subdatasheet of information. The problem I am having is if
I add another record in the 2nd subdatasheet the information in the 3rd
subdatasheet is duplicating and I need for it to either not duplicate or
duplicate but be changeable (but not change the information above.)

Please help!

 

[Ken Sheridan]

Subdatasheets are just a way of showing data in related tables, so if you are
getting unexpected results then it’s the design of the tables and the
relationships between them which is the problem. Post back with details of
the tables, what they represent in terms of real worlds entities, and how
they are related. With three levels of the sort you describe the
relationship 'chain' would be one to many to many, e.g.

Countries----<Regions----<Cities

in which case there should be no duplication, and each would be updatable.

Its not really a good idea to enter data in raw datasheet view. Data is
best entered/edited via forms.

Ken Sheridan
Stafford, England

 

[italianp16]

Subdatasheets are just a way of showing data in related tables, so if youare
getting unexpected results then it’s the design of the tables and the
relationships between them which is the problem.  Post back with details of
the tables, what they represent in terms of real worlds entities, and how
they are related.   With three levels of the sort you describe the
relationship 'chain' would be one to many to many, e.g.

Countries----<Regions----<Cities

in which case there should be no duplication, and each would be updatable..

Its not really a good idea to enter data in raw datasheet view.  Data is
best entered/edited via forms.

Ken Sheridan
Stafford, England







- Show quoted text -

Hi Ken,
Maybe you can suggest a way of setting up the tables or forms, because
I cannot get it to work the way I need it to.

I am the document control person at my office and have to keep track
of all the documents, who they were distributed to and so forth.

This is what I need to do in my table.

Person's Name

Hope that's not to confusing.

 

[Ken Sheridan]

Apologies for the delay in replying; your last post somehow slipped through
the net!

If I've understood you correctly you seem to have three main entity types:

1. People, who I assume are employees so I'll call the table for this
Employees. It will have one row per employee.
2. Document, so I'll assume a table called Documents. One row per documnent.
3. Procedures, revisions etc; I'll call this table Procedures. One row per
type of procedure etc.

There is a many-to-many relationship between Employees and Procedures as
each employee is authorised to undertaken one or more type of procedure etc,
and each type of procedure etc can be undertaken by one or more employee. A
many-to-many relationship type is modelled by a table with two (sometimes
more, but two in this case) foreign key columns each referencing the primary
key of the two tables in the many-to-many relationship, so you'd have a table
Authorisations say with foreign key columns such as EmployeeID and
ProcedureID. Together these two columns constitute the composite primary key
of the table so should be designated as such in the table design (highlight
both fields in design view, right click and select primary key from the
shortcut menu).

To record all the procedures, revisions etc applied to a document you need a
table DocumentProcedures say. This will obviously have a foreign key
DocumentID column, but it also needs a EmployeeID and procedureID foreign key
columns so that a relationship between DocumentProcedures and Authorisations
can be created on both columns, and referential integrity enforced. This is
important as it’s the enforcement of this relationship which ensures that
only authorised employees can apply a particular procedure etc to a document.

Finally you have an entity type Distribution which is related to documents
in a many-to-one relationship, so you need a table Distribution with a
foreign key DocumentID column and an EmployeeID foreign key column (for the
employees its sent to), so you'll have multiple rows in this table for each
document. It will also have columns for DistributionDate, Status etc.

I hope I've understood your business model correctly and that the above
'logical model' reflects it accurately. If so then you should be able to
create forms/subforms for data entry of documents and procedures and
distributions applied to each. You can also create a report to show the
distribution/procedure history of each document by means of a report with
embedded subreports broadly reflecting the form/subform for data entry.

Ken Sheridan
Stafford, England