Results 1 to 6 of 6
  1. #1
    Mtyetti is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    8

    Table with multiple relationships results in No Results (query)

    I am building a query for my 'pilot' database. The basic outline is an 'Event' table which stores the flight info. (date, flight time, location, and up to 2 pilots).



    I have created a search form so I can search the Events under several different criteria. However, in my query design, each time I add the Pilot table, the query returns no results. I think it has something to do with the fact that the Pilot table has multiple relationships to the Event table, but I don't know how to get around this.

    The Pilot table is linked (with an ID field) to the Event table. There are 2 relationships; (one for captain and one for co-captain of the flight linked to the EventID). Ideally, I would like to be able to search the pilot, and have the query return all Events where that pilot was the captain or the co-captain. Thoughts???

    Thank you all so much
    Scott

  2. #2
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Might better help if we could see your table relationships and the SQL your are trying.


    Dale

  3. #3
    Mtyetti is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    8
    Click image for larger version. 

Name:	EventQuery.jpg 
Views:	6 
Size:	142.6 KB 
ID:	12422
    I hope this screen shot works. You can see, the Pilot table is related to 'PIC' (which is the Captain) and 'SIC' (which is the Co-Captain)

  4. #4
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Does your query return anything without any criteria in it?
    Also, I can not see the date1 criteria.

    If it is a date type make sure you enclose the date in #. #[date1]#

    Dale

  5. #5
    Mtyetti is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    8
    Click image for larger version. 

Name:	EventQuery2.jpg 
Views:	5 
Size:	77.6 KB 
ID:	12429Dale,
    Thanks for the reply! I'm sure it has something to do with the 'Pilot' table. If I remove the pilot table from the query, it runs correctly with all the other criteria, including the dates. As soon as I add the pilot table, the query returns zero records, even if I leave the criteria blank for 'LastName'.

    Could it be that the query doesn't know weather I am looking for the 'PIC' or 'SIC' LastName? I am trying to get to where it will return all records with the last name in PIC or SIC. Make sense??
    Scott

  6. #6
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Try removing the copilot relationship in the query.

    Looks wrong to me.

    Dale

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

Similar Threads

  1. Replies: 3
    Last Post: 05-02-2013, 10:36 AM
  2. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  3. Replies: 1
    Last Post: 02-04-2012, 02:07 AM
  4. Replies: 2
    Last Post: 08-05-2011, 01:24 PM
  5. Multiple Query Results Sorted Together
    By Rawb in forum Reports
    Replies: 1
    Last Post: 12-10-2009, 04:05 PM

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