Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query not returning all records (Access 2003)

    I have a query thats not returning all records where the audio field is a null. Anyone out there can steer me in the right direction? Many thanks

    SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXCLIPS.NName, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition, Audio.AudioType AS A, Audio_1.AudioType AS b, Audio_2.AudioType AS C, Audio_3.AudioType AS D
    FROM (Audio AS Audio_3 INNER JOIN (Audio AS Audio_2 INNER JOIN (Audio AS Audio_1 INNER JOIN (Audio INNER JOIN TXMASTERS ON Audio.AudioID = TXMASTERS.Audio1) ON Audio_1.AudioID = TXMASTERS.Audio2) ON Audio_2.AudioID = TXMASTERS.Audio3) ON Audio_3.AudioID = TXMASTERS.Audio4) INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
    WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))
    ORDER BY TXCLIPS.Start;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query not returning all records (Access 2003)

    Your query links the tables by inner joins. This means that only records are returned where the join field has the same value on both sides of the join, and this value is not null. To allow the field to be null on one side of the join, you must use an outer join - either a left join or a right join. You can select the join type in design view by double-clicking the join line; three options will be presented that correspond to an inner join, a left join and a right join.

    You'll probably have to change all joins to the same type.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query not returning all records (Access 2003)

    Thanyou very much Hans. I tried option 2 on the links and it said it was not allowed, however option 3 works. so thankyou.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query not returning all records (Access 2003)

    Opps, spoke too soon. It's again not showing all records? All three join options relate to something being equal?
    Unfortunately this is all due to the fact that 4 audio fields on each record all store an ID instead of the decription which is wanted in a report. ID = 1 = English, ID =2 = French etc. Regards

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query not returning all records (Access 2003)

    Have you changed the join type for all of the joins from TXMASTERS to an instance of the Audio table?

  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query not returning all records (Access 2003)

    Thanks. Yes I changed all 4 links between TXMASTERS and each of the audio The options of the joins seemed to all refer to equal values, ie on both sides, 1 and the other, 2 and the other. Problem is some of the audio fields no not contain data, ie audio 1 only filled etc. Many thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query not returning all records (Access 2003)

    It's not quite clear to me whether the problem was solved by selecting the option to return all records from TXMASTERS and only related records from the Audio tables. If not, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query not returning all records (Access 2003)

    Note: you posted your reply with the database in the wrong thread, in <post#=699127>post 699127</post#>. Please see my reply there, but continue the discussion here.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query not returning all records (Access 2003)

    Sorry Hans, I don't know why my posting/attachment went to the wrong posting. The query is in the listbox, ie the rowsource. Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query not returning all records (Access 2003)

    As far as I can tell, you don't need TXCLIPS in the query, I'd remove it from the query.

    (With TXCLIPS you don't get all records because ID1 132 occurs in TXMASTERS but not in TXCLIPS.)

  11. #11
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query not returning all records (Access 2003)

    Thanks, however TXCLIPS does need to be in the query, as fields from this also appear in the list LP and get passed to a report. I stripped that all out to get the db filesize down. However I will look at the ID1 problem. Regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query not returning all records (Access 2003)

    Alternatively, double click the join line between TXMASTERS and TXCLIPS and select the option to include all records from TXMASTERS, just like you did for the joins to the instances of the Audio table.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query not returning all records (Access 2003)

    Many thanks Hans. In stripping down the db to post it, I accidentally removed related records in the TXCLIPS table. I modified the link between the TXMASTERS and TXCLIPS, and it works. I shows all records from the TXMASTER table and those in TXCLIPS where data is missing. So thanks again for your valuable help, I was getting worried that my tables integrity was falling over. I can sleep now. Very best regards. This site is excellent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •