Page 1 of 7 1234567 LastLast
Results 1 to 15 of 93
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Working with a Double Query on a a Form


    In the attached db I am having trouble on the form frmEmail. That form has textboxes for MeetingID and MeetingDate. The boxes when I use them on form frmEmail seem frozen.

    The record source for the form is qryPersonnelMeeting2 iis actually made up of two queries, with the first qryPerosnnelMeeting1 being part of qryPersonnelMeeting2. I had to do that the original query has ambiguous outer joins.

    In a earlier version of this document - which was done incorrectly these boxes would move through the db (on the form frmEmail) when the arrows right or left (on frmEmail) was clicked.

    The form that was the source was I think a Cartesian form (link?) which basically meant it listed all combinations so the meeting ysnMeetingID and MeetingDate would certainly
    be there if one clicked on the form often enough. It was an error.

    Now I have changed the source to qryPersonnelMeeting2. In which meeting ysnMeetingID's and MeetingDates should be returned. However, it is not working and I do not know why.

    When I run qryPersonnelMeeting2 it shows all of the employees, but leaves the space blank for ysnMeetingID's and MeetingDates. That would explain why
    I not getting them in frmEmail. But how to change, I do not know. I want to make this work.

    The query is set up. I get no choices in frmEmail.

    How do I fix this?

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have no problem adding tblPersonnel into qryPersonnelMeeting1:

    SELECT tblMeetings.*, tblAttendance.AttendanceID, tblAttendance.ysnMeeting_Invite_Sent, tblAttendance.Status, tblAttendance.Notes, tblPersonnel.*
    FROM tblPersonnel RIGHT JOIN (tblMeetings RIGHT JOIN tblAttendance ON tblMeetings.ysnMeetingID = tblAttendance.ysnMeetingID) ON tblPersonnel.PersonnelID = tblAttendance.PersonnelID;

    You want the form set to only allow new records?

    MeetingID textbox is bound to autonumber field. You cannot type into that control.

    Exactly what is purpose of frmEmail? In what table do you want to create new record? A BOUND form can enter data into only one table. Options for form arrangement are:

    1. a single form bound to tblAttendance junction table with comboboxes to select from tblMeetings and tblPersonnel tables

    2. a main form bound to tblMeetings table and subform bound to tblAttendance junction table with a combobox to select from tblPersonnel

    3. a main form bound to tblPersonnel table and subform bound to tblAttendance junction table with a combobox to select from tblMeetings
    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.

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, but will that take care of the issue? It seems somewhat foolhardy to put all the tables in your query (that had ambiguous outer joints) into your first query. If you do that successfully, then you really do not need a Double or compound query. It seems it could work. I will try it.

    But how to avoid the ambiguous outer joints?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Didn't say it made sense to do that, just that I could. However, your second query doesn't seem to make any better sense in context of this form and its purpose.

    Edited my previous post for more info.
    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.

  5. #5
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, here is the design of that query. I am trying to find. The original query that gave me ambiguous outer joints.

    I will post it when I do.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  6. #6
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, here is the db that when one runs qryPersonnelMeeting one gets the error : ambiguous outer joins.

    This is why I had to resort to the double query.
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you read revised post2? Might help to answer the question I posed.
    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.

  8. #8
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, here is my dilemma. I want to record all issuances of an email invitation to a meeting. So if we have say 10 employees and six scheduled meetings then we should have 60 lines in some table listing each employee and each meeting he/she could be invited to, plus a checkbox next to it that is checked if the meeting invite was sent; conversely if the meeting invite was not sent the box would be unchecked.That would make for a lot of entries, 60 in my example.

    Maybe, there is a better way?

    Not all employees would be invited to all meetings. So why show a record of an employee and a meeting that he was not sent an email invite to? That would considerably reduce the entries for each employee.

    The table for this record keeping could be a existing one or a new table. It does not matter to me. My concern is accuracy, not saving computer memory.

    This means either a table with each employee's name and each meeting that he was invited to (while also showing the ones that he did not get invited to).

    Or just showing a record for each employee and only the meeting in which he was invited. That would assume that by not showing a meeting and whether an invite was sent would be info by elimination.

    Thus, a smaller more neat and compact image. But the question becomes how to write that in MACRO or VBA code? I prefer the latter, but now I am not sure
    how to do it,

    That is the question at hand.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    If I understand correctly you would need a junction table that shows the relationship between the meeting and the employees.

    EG:
    meeting_ID Employee_ID
    1 1
    1 2
    1 3
    1 4
    2 5
    2 6
    2 7

    This would show for your first meeting (meeting info stored in the meeting table) four people were invited. (1,2,3 and 4) Persons info stored in their table.

    for meeting 2 we have 5,6,7 as the people but they could be 1,2,3,4 its literally just saying this person was invited to this meeting.

    For a better understanding of junction tabled there's a lot online if you search for that term.

    If I'm telling you to do something that is not what you're asking I apologise in advance. haha.

  10. #10
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    This seems to be very helpful. Thanks! You are telling me and answering my question.

    Respectfully,


    Lou Reed

  11. #11
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Record sourcing table versus re ord soucing queries

    The attached files that contain my db, and a pdf file that is a diagram of the relationship tables. I was going to create a query that essentially had one table: table tblAttendance. The table has everything that I need to create a junction table. It just does not have the the word junction in its name.

    I can connect the query up to the form frmEmail as a record source. But that seems unneeded..

    Could there be an easier way?

    We use queries to organize data usually putting them together using one or more tables. However, the query that I just described has just one table and that table essentially contains everything that is needed in form frmEmail.

    So why create a table to organize data, when the data is already organized? Why not just use the table directly?

    Anyway, that is my question. The relevant files are attached.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  12. #12
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, I think the answer has been overcome by events. It is probably better to use a query now since I am going to use more than one table.

    I built that query already, it is qryPersonnelMeeting3. It clearly uses three tables and the tblAttendence is the junction table. However, when I run it I only get part of an answer.

    I get the employees listed, but I do not get any meeting listed or their MeetingID. This make no sense because the tblMeetings is used in the building of qryPersonnelMeeting3 and there are at
    least 12 meeting with their respective dates. So why am I not getting any meeting?

    The attached file contains the db in question.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  13. #13
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I created a query with just tblMeetings and I got all of the meetings dates and MeetingID. It just easily showed up when I ran the query. I just need to know why it is not showing up in qryPersonnelMeeting3. I just do not know. I have attached the db in the immediately above post, any ehlp appreciated.

    Respectfully,

    Lou Reed

  14. #14
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I hate to ask the question again, but I am confused. I tried to create a two table query using tblMeetings and tblAttendence. I did not have much luck. The query just showed no meeting info again. I am really confused.

    I also tried to rebuild the query and got on running the query ambiguous outer joins. I just need to know how to get all the info displayed when I run the query.


    Any help appreciated. Thanks in advance.


    Respectfully,


    Lou Reed

  15. #15
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Lou your tblattendance has no data in it. This suggests that no one attended the meetings

    Try adding values to that table to link with the meetings

Page 1 of 7 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