Subdatasheet Help
LeAnn
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
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
itali...@aol.com
>
> - 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
>>Show what Procedures, Rev No they are qualified to. (I have a seperate table listing the just the Procedures, Rev No's and distribution)
>>>Distribution of this Document- Ex: Document No., who it was distributed to, date,status (When new documents are issued I need to add/show the new and old distribution information.
Hope that's not to confusing.
Ken Sheridan
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