Results 1 to 2 of 2
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    I've built a query to display the names of periodical publications. Some of the periodicals have Title & Month & Year. A few have just Title & Year. I built the following field in the query:

    Issue: [tblMagNames].[MagTitle] & (" "+[tblMonths].[PubMonth]) & " " & [IssueStartYear]

    The SQL view reads as follows:

    SELECT tblIssues.IssueID, [tblMagNames].[MagTitle] & (" "+[tblMonths].[PubMonth]) & " " & [IssueStartYear] AS Issue
    FROM tblMonths INNER JOIN (tblIssues INNER JOIN tblMagNames ON tblIssues.IssueTitle = tblMagNames.MagID) ON tblMonths.PubMonthNbr = tblIssues.IssueStartMonth;

    When I run the query, it displays every publication that has all three elements (title & month & year). The ones that do not have a month, however, do not appear. Where did I go wrong with my query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You have an inner join of tblMonths and tblIssues. By definition, an inner join only returns records for which the join field is the same and not null on both sides. So you won't get records whose month field is null (blank, empty).

    Try changing INNER JOIN to RIGHT JOIN for the join between tblMonths and tblIssues. You may have to change the other join too.
    In design view, you change the join type by double-clicking the join line and selecting a different option.

Posting Permissions

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