Results 1 to 7 of 7
  1. #1
    sgrove is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    4

    How to auto fill FK based on multiple fields in the PK table

    Please help!



    I have students and demonstrators (demis), working on different days in different labs and different benches. Each demi can have multiple students. I need to link each student to his demi, based on the day, lab and bench. I want [student details].[demiID] to be filled in automatically with the correct value from [demi details].[demiID]. I tried to build nested iif expressions (iif([prac day]=1 and [prac lab]=1 and [prac bench]=1,1,null) etc) in [student details].[demiID] to generate the matching value (1 - 40), but by the 12th case (out of 40) it is "too complicated" according to Access.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	18 
Size:	35.0 KB 
ID:	13025

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    When you're doing your data entry for StudentDetail why are you entering two ways of identifying the item from DemiDetails? You have PracDay, PracLab and PracBench but you also have DemiID. Presumably you do not need one of those two. If PracLab, PracLab and PracBench can identify the DEMIID you do not need to store the DEMIID at all. That is assuming you do not want to do something like.. pick the demi from a listbox that shows only the demis available for that student in which case you could just store the DEMIID and any time you needed the day/lab/bench you could look it up by linking the tables.

  3. #3
    sgrove is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    4
    Thank you for replying. I am well and truly stuck, and I don't know if I am thinking myself into corners that don't exist.

    I am running 3 labs, 4 times a week, with 4 benches per lab.

    The demis are assigned to a specific day-lab-bench. The students are also assigned to specific benches, but there are ~20 students per demi and they are numbered according to the locker at that bench. The locker allocation is done manually.

    I need StudentDetail to "pick" the demi that will supervise that student, based on the day-lab-bench detail that I had to assign to the student. IOW, I HAVE to enter the specific locker/bench the student will work at, but I don't want to have to pick the demi as well.

    ****lightbulb moment***
    Is this where I use day-lab-bench as the primary key for DemiDetail? Is it possible?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I do not use multi filed primary keys, I always have an autonumber or other singularly unique PK's.

    If I understand this right, when you are entering data in your Student Details, after you've finished entering the day, lab and bench you want to be able to see which demi related items go with that choice. So for instance if you have:

    DemiID DemiStudentNumber DemiName DemiInitials DemiSurname YearofStudy DemiDay DemiLab DemiBench
    1 0001 AAA A B 1111 1 1 1
    2 0001 BBB B A 2222 1 1 2
    3 0001 CCC C C 3333 1 1 3

    and when you do your data entry and enter 1, 1, 2 in day, lab, bench you want to look up the values related to DemiID 2.

    Is that correct?

    If so you can do this a couple of ways but I'd rather know if what I'm suggesting is what you want to do.

  5. #5
    sgrove is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    4
    DemiDetails with be exactly like you have it. Each demi is a senior student (therefore a student number) who is responsible for many students.

    StudentDetails is an exact replica of that, except that they have marks entered. Each student has only one demi.

    I want all the students with day, lab, bench = 1,1,1 to be linked to the demi who is responsible for day, lab, bench = 1,1,1. IOW, when I enter 1,1,2 in StudentDetails, I want that student to be linked to the record in DemiDetails with 1,1,2

    In the end I want each demi to be able to access only his own students, to enter the weekly marks.

    If it was a simple one_field relates to one_field I could do it. But this multiple thing is throwing me off.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    one field does relate to one field. Your DEMI ID. You're just getting hung up on selecting the correct DEMI for a student.

    You do not need to record the day lab and bench in your STUDENTDETAILS table, all you need is the demiID.

    You can do a couple of things to facilitate that

    1. Create a list box, make the source of that list box the query: SELECT [Demi ID], [Demi Day], [Demi Lab], [Demi Bench] FROM [Demi Details] ORDER BY [Demi Day], [Demi Lab], [Demi Bench]
    2. Make the bound column of the list box Demi ID
    3. Set the widths of the columns to 0,1,1,1) (you can play with the widths to fit your screen)
    4. Set your COLUMN HEADS property to YES

    when you view your list box it'll show all your demi's as a single list and you can choose one.

    You could theoretically concantenate your day, lab and bench as well and you could type something like 1,1,1 and end up with the right item (if you were to use a single combo box instead of a list box)

  7. #7
    sgrove is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    4
    See? I TOLD you I was thinking myself into corners.

    I'm going to see what I can do. Thank you again!

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

Similar Threads

  1. Replies: 3
    Last Post: 03-15-2013, 03:27 PM
  2. auto fill name based on file number
    By mark_w in forum Forms
    Replies: 13
    Last Post: 08-03-2012, 04:59 AM
  3. Replies: 5
    Last Post: 09-14-2011, 03:41 PM
  4. Replies: 5
    Last Post: 01-20-2011, 11:36 PM
  5. Replies: 1
    Last Post: 12-09-2010, 08:29 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