Results 1 to 15 of 15
  1. #1
    alistair_m is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    8

    Two Foreign keys in same Table referencing the same Primary Key

    Hi All,



    Apologies if this has been asked before, I had a search and couldn't find an answer.

    I have two linked tables: tblStaff and tblRoster.

    tblRoster has fields: date, staff1, staff2. My design requires both staffID fields to be linked to staffID primary key in tblStaff. Access won't let me assign two 1-to-Many relationships between the same tables, unless I add a duplicate tblStaff in the relationship screen. My application is now quite complex and Id like to know if this is method is sound, or am I going to encounter some weird stuff going on? Any better method of creating this relationship?

    Many thanks
    Alistair

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    tblRoster does not look normalised so you may be building problems for down the line. but I add a duplicate tblStaff in the relationship screen is what you would do

    Any better method of creating this relationship?
    as mentioned, tblRoster does not look normalised. I know nothing about your business and the structure may be perfectly valid for this situation. However I would have expected to see something more like

    tblRoster
    PK....Date...Position....StaffID
    1......1/1/18...1...……….22
    2...….1/1/18...2...……….10
    3...….2/1/18...1...……….33
    4...….2/1/18...2...……….10

  3. #3
    alistair_m is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    8
    Thanks for this info again!

    My tblRoster was normalised(ISH), with the date as the PK:

    tblRoster
    PK..................staffID1..........staffID2
    19/10/2018.....22..................19
    22/10/2018.....36..................21
    23/10/2018.....19..................36

    Unless there is a reason why I shouldn't be using a date as PK? I have no other relationships with this table, so the PK is only there for uniqueness, and indexing.

    A

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't know what StaffID1 staffID2 mean, but usually such a naming convention implies a denormalised table.

    using a date as a PK maybe OK so long as it doesn't contain a time element, in which case a definite no no - dates are stored as decimal numbers with the time part being the decimal part so you would hit precision issues. Dates also take up twice as much space as a long so your index will 50% slower, but for a small number of records it won't really matter.

    Personally I would store the date as a long, rather than a date, it is easy to convert back. use the clng to convert to a long and cdate to convert back

    ?clng(date())
    43392
    ?cdate(43392)
    19/10/2018

    and to demonstrate the time impact
    ?cdbl(now())
    43392.4554398148
    ?cdate(43392.4554398148)
    19/10/2018 10:55:50

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is purpose of this table? Why are there 2 staff fields, why not 3 or 4 or 30?

    Yes, multiple fields can be linked to same table and yes, table would have to be added multiple times in Relationship builder or in any query.

    It's unusual for a date to be unique in a table but if it truly will be then should be okay, especially since it really is a number type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    "Date", must never be used as a field name let alone a Primary Key.

    Date is a Reserved Word and is there for Access to do it's thing. You should do a search for Reserved Words and save a copy for your own personal use.

    If as Primary Key you use Autonumber and then use Long in the other table then you will have a perfect relationship that should never cause you and harm.

    You can use MyDate or similar if you wish

  7. #7
    alistair_m is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    8
    Thanks for all these replies. To clarify, the application is for a school detention monitoring system (I hope that doesn't put members off replying....:-/ ).

    Firstly, Date, isn't actually the name of the field - its detentionDate - and it is only the date element I need. There are two staff members required to cover the daily detention and these staff come from a pool of 100+, which are held in tblStaff (PK is staffID). The only uniqueness is the date, as detentions are only held once a day. It maybe that the same two staff members are required together at a later date, or they may be required to pair up with someone else at some other date. There are no other tables related to this, so the PK will never be used in that respect.

    This is just one small element of the application, and as Im sure you can appreciate, it becomes considerably more complex.

    Many thanks again

    Alistair

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    now we know the purpose - the original method (bring tblstaff through twice) stands.

    just be aware with this structure if the rules change or for some other reason and you need three members of staff, your design falls over

  9. #9
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Firstly, Date, isn't actually the name of the field - its detentionDate -
    You are not the only person to hide this sort of information and I don't know why. We are happy to help so the better the information we get the easier it is for both of us.


    The only uniqueness is the date, as detentions are only held once a day.
    Using Autonumber as the unique identifier is something I started using after getting into all sorts of trouble with other types or PK. A composite Key is one of the worse. Please reconsider its usage.

    It maybe that the same two staff members are required together at a later date, or they may be required to pair up with someone else at some other date. There are no other tables related to this, so the PK will never be used in that respect.
    The PK is used every time you use the Table. The better the key the easier it is to use. It is also the fastest, (Most of the time)



    This is just one small element of the application, and as Im sure you can appreciate, it becomes considerably more complex.
    Again make it easier by using AutoNumber.

    Have you created all of your relationships in the Relationship Window. It helps a lot to print this our and put it on the wall so you can always glance at it to help in your further design.

    Many thanks again
    Hope I have at least got you thinking differently.
    I could post a Demo database that has all the elements that you wish to use. Just ask

    Good luck with the Project.

  10. #10
    alistair_m is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    8
    Absolutely appreciate all he info you guys have given! I actually have the relationships printed out, and it really has helped.

    Part of the problem Ive got is the data Im having to work with. We have a "government provided" Management Information System which provides the back bone of the school system, but as with most of these large scale generic applications, it's jack of all trades, and master of absolutely diddly squat! Its cumbersome and inflexible and as far as I can work out has been developed at large cost, with very little input from its end users. Because of the restrictions on it, I cant get Access or any other platform to talk directly with it, so am having to run csv exports and pull them into the system Im working on. Its hardly adequate, as we then have two parallel sources of data which are only manually linked. Also, Im not in control of the datatypes - hence the reason earlier for preferring to use strings rather than integers for staffID's. I had shied away from using Autonumber, as the data does have sufficient naturally occurring Primary Keys - but your probably right...Im creating a lot of hard work for myself - partly I was hoping that at import, any anomalies would be picked up at the time. Although in hindsight, a little more coding could have taken care of that. So....Ill learn for the next one!

    A

  11. #11
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I can now appreciate the position in which you have been placed.

    I once worked for a School and had the same problems as you are now faced with.

    I suspect that you are stuck with the Table Structure you have been given.

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by alistair_m View Post
    Thanks for all these replies. To clarify, the application is for a school detention monitoring system (I hope that doesn't put members off replying....:-/ ).


    Just kidding

  13. #13
    alistair_m is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    8
    Quote Originally Posted by Rainlover View Post

    I suspect that you are stuck with the Table Structure you have been given.

    Nearly....its a nightmare actually, but with extensive vba going on, I've got most of the data how I want it. Its turned a simple database problem into a programming headache!

  14. #14
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Where are we up to. Do you still have the same problem.

  15. #15
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I am still thinking about your problem.

    My thought is that you should not have so much VBA. The more VBA you have the greater the chance of writing mistakes. I would think you should import from the Master D Base the handle the changes via SQL.


    Are you downloading or do you have a link between the two Databases

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help allocating Primary/Foreign Keys and Relationships
    By rosscortb in forum Database Design
    Replies: 2
    Last Post: 07-08-2015, 08:12 AM
  2. primary and foreign keys?
    By themebuddies in forum Access
    Replies: 1
    Last Post: 12-14-2014, 09:23 PM
  3. Replies: 11
    Last Post: 06-11-2012, 12:23 AM
  4. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

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