Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So what you're telling me is that a Parent -> Child <- Parent relationship cannot work at all?
    No, I'm not saying that at all - what I am saying is for it to work with referential integrity, the parent ID must be in both tables - which is not what you wanted, you want it to be in one or the other.

  2. #17
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Okay, so then what's the point of having two parents if they both have the same parent ID? Isn't that redundant?

  3. #18
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In my opinion you just need the two tables but index on the Unicode number rather than the character itself. Trying to put exceptions because of indexing issues in another table is not the way to go.

  4. #19
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Yea but this isn't about indexing issues. It's just that I want to keep components and characters in a separate table, but want the R Character Breakdown field to be able to use the two types of data while still being limited to it. What I can do is just put the components in another field within the Character table, or make another field in the Breakdown table for the different kind of data.

  5. #20
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry to butt in, but isn't this about data? You don't want the same movie/actor combination to be entered twice so after entry run a query or do a DLookup that says - does this combination already exist, yes or no. Make sure the data is entered thru comboboxes so that you don't get spelling differences.

  6. #21
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    butt in by all means, I don't think I'm getting through

    The problem the OP has is his primary key is a single character (from the Chinese alphabet) This 'primary' character might or might not be made up from a number of other characters - so needs a one to many relationship with the other 'sub' characters. The issue is that access is interpreting two different primary characters as having the same value - i.e. a duplicate so will not allow the second character to be added to the table. The Unicode values are different, but for some reason access does not see this - I suspect it is an access bug but one that is unlikely to be sorted anytime soon.

    So living with the problem, my recommendation was to use the Unicode number as the primary key

    tblCharacters
    UnicodePK - long calculated as Unicode of character using ascW(..)
    Character text(len=1) - the actual character
    Meaning - text - what the character means

    tblSubChars
    SubcharPK autonumber
    PrimaryFK - long - link to UnicodePK as Unicode for primary character (1 to many relationship)
    SubFK - long link to UnicodePK as Unicode for sub character

    if you don't want the SubCharPK you can use a multifield primary key over the other two fields

    left joining tblCharacters to tblSubChars on UnicodePK to PrimaryFK will show all the relationships

    then using chrW(PrimaryFK) will display the primary character and chrW(SubFK) the sub characters

    It just needs a very simple form which updates the UnicodePK with the Unicode value of the primary character before inserting into the table

  7. #22
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Thanks for the help, but I told you that the collation error isn't the issue I'm having. The access bug need not be addressed. My problem was that there are sub characters that cannot exist by themselves, like ⺮ in and 㔾 in and  in 昔 but only appear within another character. But they still work as components just like the characters that can work by themselves, like 木 and 目 in 相. So my problem was getting both of these types of characters that have different restrictions to appear in the same foreign key field. This is what I want to work around, not the duplicate issue.

  8. #23
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    then modify this table to

    tblCharacters
    UnicodePK - long calculated as Unicode of character using ascW(..)
    Character text(len=1) - the actual character
    CharacterType yes/no yes=primary, no =component only
    Meaning - text - what the character means

  9. #24
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Interesting. So you don't think having all of those things mixed up together won't make things more difficult? Because the thing about the components is that some of them are meaningless shapes.

  10. #25
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    providing they have a Unicode value, I don't see why they should - just filter them out if you don't want to see them mixed in

  11. #26
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    I see. I'm still very new to Access. Thanks for you help. I'll come back with some more progress later.

  12. #27
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've attached a simple db to demonstrate how I think you should be structured

    ChineseChars.zip

  13. #28
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Hey. Sorry for the late response. Thanks so much for sending that test DB. It will help a lot in the future. I really wish I didn't have to use it though. It looks like it can help solve other problems that I expect to arise later.

  14. #29
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    cjwakesteel,

    It's been about 9+ months since your post at M$oft. Did you contact a Chinese forum or someone more familiar with code pages, character sets, representation of Chinese characters etc?
    I don't profess any knowledge in the area of Chinese characters or unicode, but the responders at the M$oft forum and Ajax are all well respected and knowledgeable. It appears that you may have uncovered a significant issue with collating sequence that could be very important to a lot of people.
    Definitely the title of your post "preventing duplicates", does not do justice to your issue.

    Your initial question regarding Movies and Actors falls into the Many to Many relationship theme and, as Ajax said, a link table or junction table is the recognized solution. But your specific issue with Chinese characters and component pieces that each may or may not be "stand alone" (don't know terminology to be more explicit) adds a wrinkle that many have not experienced.

    I can't come up with a quick analogy that may be helpful in solving the "general problem". The best I can think of at the moment is some pieces that are required to complete some pattern/shape where some pieces can not be used in conjunction with another piece. eg You can't use piece/shape 88 if piece 70 has been used.
    I realize this is different, but I don't have another.

    It's almost like here are all of the characters. Here are legitimate combinations of characters.

    Don't know if any of this is helpful, but some thoughts for consideration.

    Good luck. Anything you find will be of interest to someone.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-05-2014, 07:06 AM
  2. Replies: 4
    Last Post: 12-18-2013, 06:52 PM
  3. How to prevent duplicate records
    By talktime in forum Programming
    Replies: 7
    Last Post: 05-15-2013, 11:02 PM
  4. Prevent duplicate records for calculated field
    By LoveLEE143 in forum Access
    Replies: 3
    Last Post: 05-03-2013, 11:46 AM
  5. Replies: 2
    Last Post: 02-12-2011, 09:54 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums