By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Controlling duplication in a subform

P: 26
I have a form which shows information about our controlled documents. I also have a subform which tells you everything about the many places this document was distributed to. So, Form 1234 would have one record but for that form, there could be as many as 30 records in the subform showing that it was distributed to this, that and the other place. Within the subform is a field, Doc_Ctrl_No which is not the primary key and can't be the primary key as most documents have the same repeating doc_ctrl_nos. This means that form 1234 could have doc_ctrl_no 001 associated with it but so could form 4321 and there is no way to change that. So, when a user is updating the record for form 1234 I don't want them to be able to duplicate the doc_ctrl_no for that specific document. Is there a way to make this happen??? Any help/thoughts would be highly appreciated. Its not the easiest thing to explain so let me know if further clarification is required.
I should mention, I'm working in Access 2002.
May 13 '08 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, PotatoChip.

Did you think about setting multifield index allowing only unique combinations of [Doc] and [Doc_Ctrl_No] fields values.

Regards,
Fish
May 13 '08 #2

P: 26
Fish,

Will that work with two tables? My documents are stored in one table and the Doc_Ctrl_no is part of another. Perhaps I wasn't clear enough in my orginial post; the same document type & number can have the same DocCtrlNos, it just depends on the version. This is why in my main table, I have had to make the doc type, no and version as the primary keys. Believe me, gong show doesn't even begin to describe this database and all the issues surrounding it...that's why I was thinking of linking the DocCtlNos to the specific record you are looking at in the form. I just can't think of any other way to do it. Again, any input would be greatly, hugely helpful.
May 14 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Ok. Would you like to post your tables set metadata?

Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Regards,
Fish
May 14 '08 #4

P: 26
I don't fully understand how to post my tables and have it make sense. Most of what I do/read about in Access is totally over my head. I just tweak and play around until I get the results I need without being able to understand what I've just done. I'll just tell my boss it can't be done. I don't want to waste any more of your time. Thanks.
May 15 '08 #5

FishVal
Expert 2.5K+
P: 2,653
I don't fully understand how to post my tables and have it make sense. Most of what I do/read about in Access is totally over my head. I just tweak and play around until I get the results I need without being able to understand what I've just done. I'll just tell my boss it can't be done. I don't want to waste any more of your time. Thanks.
Hi, PotatoChip.

[off-topic]
I appreciate your care about my time, but believe me it is my own decision to "waste" it posting on the forum. (Otherwise sight admins come my home and cut me to pieces. ;) [joke .... maybe :))]).

[topic]
I've asked you to post tables metadata because I couldn't figure what is going on in your db from your initial post. The way you want to organize your data is many-to-many relationship and is traditionally implemented with 3 tables: 2 contain records of items to be related to each other (for example [Students] and [Lectures]) and the third contain combinations of foreign keys from the first two thus marking particular students taken particular lectures.
Below is an example of tables metadata:

[tblStudents]
keyStudentID, Long(AutoNumber), PK
txtStudentName, Text
..... and so on

[tblLectures]
keyLectureID, Long(AutoNumber), PK
txtLectureName, Text
.... and so on

[tblStudentsLectures]
keyStudentID, Long, FK([tblStudents])
keyLectureID, Long, FK([tblLectures])

Now to avoid multiple assignments of particular student to particular lecture we may add unique index to [tblStudentsLectures] to prevent identical combinations of the fields in the table.

Does it look like what you want to achieve?

BTW, if you want to get basics of relational databases, then you may benefit from reading Database Normalization and Table structures

Regards,
Fish

P.S. Feel free to ask questions ... even if you think they are stupid or if you suspect they look stupid for anybody else.
May 15 '08 #6

P: 26
Fish,

Thanks for the link to the article. I think I understood it, which is a pretty good indicator of where I'm at in terms of computer literacy...*sigh*
I'll give posting the tables a shot:

[tblControlledDocuments] - drives main form
Type, text(driven by tblType which has no PK), PK
Number, text, PK
Version, text, PK
Status, text
EffectiveDate, date/time
...and several others

[tblDDL] - drives subform
DDL_ID, autonumber, PK
Type, text (driven by tblType)
Number, text, FK? - same as number from my tbl above
Version, text, FK? - sama as version from my tbl above
Location, text(driven by TblLocation, also has no PK)
DocCtrlNo, text
Returned, Yes/No

I have no defense for the lack of PKs in my associated tbls. However, in my defense to three PKs in the first table, when I first created the database I was told not to put in a primary key as there is no way to establish one. My suggestion to use an autonumber as a primary key was shot down and being new to the department, I caved. Knowing the system a little better, I concluded that [type] + [number] + [version] = unique value. The problem is there are too many manys in this database. One type can have many of the same numbers (or vice versa), a myriad of versions and many of the same DocCtrlNos. For example:

[Type] [Number] [Version]
SOP 1234 1.0
SOP 1234 2.0

With relation to the second table, SOP 1234 v.1.0 can have a DocCtrlNo of 001, 002, 003 but SOP 1234 v2.0 can also have a DocCtrlNo of 001, 002, 003. There is nothing I can do to change the DocCtrlNo numbering system, as I may have mentioned before. So, while there are duplications of the DocCtrlNo in the tbl, I was hoping to prevent the duplications of the DocCtrlNo as it pertains to a specific record e.g. a user can't enter DocCtrlNo 001 twice for SOP 1234 v.1.0. Clear as mud?
Thanks heaps for any help you can give me!
May 15 '08 #7

FishVal
Expert 2.5K+
P: 2,653
.... I was hoping to prevent the duplications of the DocCtrlNo as it pertains to a specific record e.g. a user can't enter DocCtrlNo 001 twice for SOP 1234 v.1.0.....
Ok.

You just need to set multifield unique index on the following fields: [Type], [Number], [Version], [DocCtrlNo]. I hope you've read article about indexes I've posted link to in msg#2.

Though it would be more useful to let you swim with your current tables design, I would not withhold comments. Bear with me. ;)
  • First and foremost I would blame :) lack of PKs in tblType and tblLocation.
  • The second. Triple-field PK in this case means lack of normalization. The data is in definite three-level (requires three tables) one-to-many relationship. Actually, you already have two tables, why not to add the third? ;)

    Something like the following:

    [tblTypes]
    txtType, Text, PK

    [tblDocumentNumbers]
    lngDocumentNumber, Long, PK
    txtType, Text, FK([tblTypes])

    [tblDocumentVersions] (or even two tables here for version and subversion)
    txtDocumentVersion, Text, PK
    lngDocumentNumber, Long, FK([[tblDocumentNumbers])
  • so [tblDDL] contains FK's from [tblDocumentVersions] and [tblLocation] thus implementing many-to-many relationship between them

    [tblDDL]
    DDL_ID, autonumber, PK
    txtDocumentVersion, Text, FK([[tblDocumentVersions])
    Location, text, FK([TblLocation])

    DocCtrlNo, text
    Returned, Yes/No
  • Now you just need to set unique index to [txtDocumentVersion] and [DocCtrlNo] fields.
  • You also should read article about SQL JOINs

Regards,
Fish
May 15 '08 #8

P: 26
Thanks for all the help!

Chip
May 15 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.