Page 2 of 7 FirstFirst 1234567 LastLast
Results 16 to 30 of 93
  1. #16
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, but I have data in the tbPersonnel and in tblMeetings. Therefore why is tblAttendance important? Say we are just beginning the new year and we have meetings scheduled and we have personnel. That would make tblMeetings and tblPersonnel quite important and tblAttendence with no data.

    The attendance is recorded as the meetings occur. This is the record source for fmrEmail. Thus prior to the meetings occur we mail invites. Then we they occur we take some form of attendance.

    This is mainly as I said a record source for frmEmail. The MeetingID and the MeetingDate are control sources for the respective textboxes in frmEmail.

    I just do not know what is going on here.

    I would think that I would have with 12 meeting dates and 4 employees that would make for 48 records or slots. The meeting invites would either be filled in or not according to who was invited to the meeting.



    I know there is a problem here, it is just I do not know how to deal with it.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  2. #17
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I think that I know my mistake, but I am unsure as to how to fix it. The tblAttendeance is a misnomer. It has little to do with attendance, and much to do with MeetingID, MeetingDate, and whether or not an invitation was sent and to whom it was sent.


    Attendance just plays no part here. I do have a record space for AttendenceID, but it might as well be called InvitationID. That is what it should really be called. The table should be renamed tblInvitation. It would seem to be so much more accurate that way.

    It would just make more sense. Do this idea hold any water here? Any input appreciated.

    Please understand that these however, are only labels. They have no meaning other than that. Whether the table is called tblAttendence or tblInvitation it still should be empty and that means that the query gives no meeting info.

    That is the purpose of the post. Change the labels and still things do not work out.

    How to change is the question.



    Thanks in advance.

    Respectfully,


    Lou Reed

  3. #18
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    The tblAttendance is a misnomer. The tblInvitation is a misnomer. I think this should something along the lines of tblAttendance-Invitation. In other words a combination of the two tables. I want to record data about meeting attendance, not just meeting invitations sent. The most likely place to put this is in what is now tblAttendance. It is a junction table as you can see from attached file, but it must contain info about attendance as well as invitations.

    I know that junction tables are not to contain a lot of information, else they would not be junction tables. Where else should I put the attendance information? It just seems the logical place.

    I am attaching a file of the table relationship to make my point. Any comments welcome.

    Respectfully,

    Lou Reed
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  4. #19
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    yes, the query will show only data that's related. if there is no relationship between them then it has nothing to show.

    you can right click the relationship and chose to include all records from one table and only related records from the other.
    Last edited by Homegrownandy; 05-08-2017 at 06:43 AM.

  5. #20
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Ok, but I have two criteria that I would like to keep track of. The first is whether or not someone has been invited (meeting-invitation-sent) to a meeting.

    The second is whether someone had actually attended the meeting. I think both of these could go into the junction box table in what is now called tablAttendance.

    However I am reluctant to do it, because I know that the junction table should contain only a sparse amount of fields. In my case that would not be true.

    I am just wondering in the relationship tables shown in the previous post where would I put the meeting attendance information?

    It seems that it would go into the tblAttendance, but I could be wrong.

    As I said loading up a junction table is unseemly. But what choice do I have?

    Respectfully,

    Lou Reed

  6. #21
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    That would be fine, the record in the junction table can contain whatever you want. Anything relating to that specific relationship should be in there. So you have the meeting and the person invited in a big list, just have the option to mark attendance too that's fine.

  7. #22
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    In a junction table in an MS Access 2010, is it permissible to have unique records? I thought that all the fields in a junction table are simply foreign keys for the parent table with the exception of the primary key which is usually not connected to anything? This excludes fields like notes. In other words, no uniqueness in keys is allowed.

    Also, it is okay to have fields that are in the parent table and are also in a junction table, but these fields in the junction table are not connected in any way to the parent table?

    For instance I may have a foreign key name PerosnnelID in the junction table, that is connected to the primary key of PersonnelID in the tblPersonnel, but in the junction table I may also have a field named Fullname that is in the tblPersonnel, but it is also in the junction table, but there is no connection.

    I know this may sound like a complicated question, but I hope it is not.

    Respectfully,


    Lou Reed

  8. #23
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    you can have whatever information in there you want. but to have details in there that are specific to the person would not be a good idea. Obviously that information should exist once on the persons record in tblPersonnel. But information that is specific to that relationship is fine.

    The relationship being this person is invited to this meeting. If he attended is only relevant here.

  9. #24
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, but I see junction tables that are quite sparse. So I am not sure how to do this. I was going to put the PersonnelID in the junction table as a foreign key connected to the primary key of the same name in tblPersonnel. Also, putting MeetingID in the junction table as a foreign key connected to the primary key of the same name in tblMeetings.

    Now what do I do with things like meeting_invite-sent and who attended the meeting in the junction box/ However, I just do not see that being done in any examples and remember junction tables are sparse.

    Another way is to put meeting_invite_sent in tblMeeting and also who attended the meeting in tblMeeting. The later being shown by PersonnelID which is already a primary key in tblPersonnel, and a foreign key in the junction table. So to put PerosnnelID in tbMeeting might be redundant, but somehow I must keep track of attendance.

    This goes for meeting_invite_sent. Should it be in tblMeeting or in the junction table or even in the tblPersonnel?

    The it just seems that it should be in at least one table, but which one?

    I know this answer is complex, but we are talking about a one to many relationship.

    Respectfully,


    Lou Reed

  10. #25
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    all the junction table is doing is MAKING it a one to many relationship. You have a many to many in the form of, many people attend many meetings. This additional Table creates a situation where one person can attend many meetings (one to many) and one meeting can have many people (one to many).

    That is the only purpose of this set up. The amount of data held per record is based on necessity. Its not common for there to be a great need to store information on each line on a junction table. But if that is the best place for it then this is the right place.

    Remember that examples are made to help understanding of the problem. Any additional information will be left out. Often they are just created scenarios to highlight the 'solution'.

    Forget about the practicalities of making this work for a second. How do you want to input the attendance.


    a check box to tick if the people who were invited attended? Is that right?

  11. #26
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Yes, that would be right. Now. where does this go into junction table, or tblMeetings?

    No matter which one it goes in there will be the issue with PersonnelID and Fullname fields. They are either in the junction table or in tblMeetings. So how does it go in?

    I thought that they would belong in the junction table? They use info from the tblPersonnel and tblMeetings so they might be better placed in the tblMeetings. That means in either case that PersonneD and Fullname fields (this last being needed because on a list PersonnelID , is just a number would be useless). But wherever it is, it is also in the tblPersonnel and it is neither a key or foreign key. This bothers me.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed




    That is why I asked if you can have fields from say a tblPersonnel in other tables and they are neither key. It just seems redundant. These fields have to go into at least one other table and they are neither key.

    Also, this must be done also for meeting_invite_sent and whether the person attended the meeting.

    Any help appreciated. Thanks in advance.

    Respectfully

    Lou Reed

  12. #27
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Here is the screenshot of the selected table relationships. It shows that I added the field, FullName, into tblAttendance. The field Fullname is already in tblpersonnel, but I included it in tblAttendence, because as I said before, just listing PersonnelID is not of much help, FullName would show much greater info.

    Obviously, tblAttendeance is a junction table it just does not have the word junction in its name. In spite of just holding attendance info it also holds meeting_invite_sent info.

    My question is this acceptable?

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  13. #28
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    this is incorrect. you just want a table of numbers. (don't take that literally. as I have said before you can store anything here that's relevant.) BUT.

    table view is not where any user sees this information. yes to look at a number you don't know who it is.. But this is the purpose of defining these relationships. So we can build a query that will show the name for ID one. and the meeting room for ID 7. for example.


    You should reference where that information is stored rather than duplicating it. Watch a few videos about building querys. It should help you. There are a few good courses free on youtube.

  14. #29
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I understand. I guess that I just want the junction table to only have numbers and PersonnelID would do it. As I said though it gives little info. What good is it to know that PersonnelID 5, attended meeting 3?

    It seems that we are talking at odd here. First, I was told it was okay to add things like FullName (my intuition said it would not be) in the junction table and then I put FullName in the junction table and it
    is incorrect.

    I am just confused when it is it okay to put in things like FullName into the junction table.

    I realize that PersonnelID should be all that is required in the junction table, but that tells a user very little. Adding Fullanme, is for the user's convenience.

    Clearly FullName is from tblPersonnel and it is not linked, it is neither a foreign or primary key. It just seemed wrong to repeat this in the junction table. Relational db's should have a minimal amount of info
    in their tale and preferable listed once. I just do not know about this. Any websites talk about this?

    I have seen the Youtube videos.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  15. #30
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    the user should never ever see data in a table. ever.

    the user sees only forms that you construct and display the information you want. This is why the relationships must be correct. so the DB knows person 1 is lou reed. then on your form if required it shows your name.

    just remember DATA in its raw format is not intended to be user friendly.

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

Similar Threads

  1. Replies: 13
    Last Post: 01-22-2015, 05:27 PM
  2. Double Value Shoes in query
    By nhkhurum in forum Queries
    Replies: 14
    Last Post: 11-16-2013, 03:55 AM
  3. Replies: 6
    Last Post: 04-26-2013, 10:07 AM
  4. Replies: 2
    Last Post: 02-13-2013, 04:14 PM
  5. how to solve this double query
    By gunterhoflack in forum Access
    Replies: 11
    Last Post: 01-28-2013, 07:58 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