Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Reluctant Query (Access 2003)

    I have a table with class numbers as shown below (4102A, etc.). When I run a query on the table (nothing special, just a straight query on all fields in the table with no criteria), the query reports all records, BUT it omits the 4103A and 4103B records.

    I've inspected the table entries for these two records, I've re-entered them, I've deleted the two records and entered them from scratch. No matter what, the query doesn't return these two records.

    Any suggestons as to the solution to this problem?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reluctant Query (Access 2003)

    Is this a joined query with another table ? If yes, select the properties of the join and select all records from the your table and only those from the other table.
    You can also post the sql from the query so we can have a look at it.
    And if you want, post a copy of the database with the table(s) and the query.
    Francois

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reluctant Query (Access 2003)

    Thanks for prompting me to look further into the problem. As it turns out one of the fields in the table was linked to text entries in another table, and the two records that didn't show up in the query had no entry in that linked field. (They showed up as zeros.) Thus, it seems that linked fields need to have an entry before the query will pick them up. I solved the problem by adding a new record in the linked table and entering "TBD" (to be determined) in it. Thus, I select the "TBD" entry in the main table instead of leavng it blank.

    If you have any suggestions I would love to hear them.

    Thanks again for your help.

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

    Re: Reluctant Query (Access 2003)

    If you link two tables, say TableA and TableB in a query, there are three possibilities:

    - Inner join: include only records that have the same value in the linking field. Records whose linking field is blank are NOT included.
    - Left join: include ALL records from TableA, and related records from TableB. Records from TableA are included even if the linking field is blank.
    - Right join: include ALL records from TableB, and related records from TableB. Records from TableB are included even if the linking field is blank.

    Your query has an inner join, so records with a blank linking field are not included. There are two ways to get round this. One was indicated by Francois in his reply: double click the line joining the tables, and select the 2nd or 3rd option, depending on the situation. This changes the join to a left or right join. The other workaround is the one you found: replace the blank value in the linking field by a dummy value.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reluctant Query (Access 2003)

    Hi Hans,

    Thanks for the explanation -- I'm stll learning about joins. I'll reexamine the joins in my linked tables. That was certainly the problem.

Posting Permissions

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