Results 1 to 2 of 2
  1. #1
    Bob le flambeur is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Location
    US
    Posts
    1

    Unhappy Trouble with not showing all records in Query with 2 tables

    Here is my problem:

    Query has two tables:
    1. Registration (Data re: people who will attend a conference)
    2. Books (Book order code, book title, book price)

    The query I have lists most of the information from Table 1 and just one field from Table 2 (Book Title). This is so the form I use to enter people doesn't get too crowded - the order code is simple* and a form letter in MS Word needs the full title of the book or books that the person has ordered. The query is my data source for this letter (you can only have one while doing a Mail Merge).

    Problem: Not everyone has ordered a book. So, the query only shows those who have a book order. This is not a problem with other fields that are left blank (such as: 'late registration charge' or 'e-mail address'). If I eliminate the second table and/or the Book Title field, all registrants are displayed by the query.

    Possible issue: I have a text box in my form that autopopulates the price of the book order based on a ComboBox with book orders. The default value for this is 0. However, the first registrant I entered needed something so another field- 'Total'- could sum up the various charges. This was before I set the Default Value of Book Price to 0. So, I had to create a Book Order (Table 2) called 'No book' with a price of $0.00. Obviously, I'd rather not have to select 'No book' every time someone doesn't order one. I can just leave the field alone and the default 0 is added in the 'Total' field. Everything seems fine.

    Then, in my query, only the first registrant- the one with 'No book' ordered- displays. No other registrants display (so far only one person has ordered a book - this is the first day). If I go into my form and delete the 'No book' from the order field in Registrant #1's record, then NO records display in my query! It's as if I put a condition on the Book Order field in my query (i.e. <> "blank").

    I have no idea why this is happening, other than it is the only field in the query from the second of two tables.

    I have a relationship between my main table (with all registrants) and my Book Table by a direct relationship with 'Book Order.' This is why I can autopopulate the price, as far as I understand it. So I don't know if the relationships can be a problem.

    My experience with Access and VBA is limited, as you can probably tell.

    However, this part of my job is affording me the opportunity to learn about and I really do enjoy it and I've received help from many threads on here without posting until know, so thanks very much.

    And thanks in advance for any help on this one!!


    *I know I could just shoe-horn the book title into my main form and then into my main table, but I am interested in finding out what I did wrong here. And why records won't show without my limiting the results. Thanks.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    As to the query issue, you will need to change the type of join between the tables involved in the query, but before we get to that I had some other observations.

    From what I understand of your application, you need a minimum of 3 tables and possibly more.

    The first table would hold only the registrant info. The second table will hold only the book information. The third table will hold the registrant and the books they are ordering.

    To determine if more tables are needed, I need some additional information about your application.

    It sounds like you are tracking registrants for a conference.

    Does the conference occur more than once?
    Can some people who attend the conference this year, also attend next year?

    Can a person order a book not in conjuction with a conference?

    Can a person order more than 1 book of the same title (i.e. a quantity issue)?

    Can a person order multiple books with different titles?

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

Similar Threads

  1. Replies: 6
    Last Post: 02-10-2011, 07:09 AM
  2. Replies: 1
    Last Post: 07-27-2010, 08:02 AM
  3. Replies: 7
    Last Post: 06-16-2010, 09:19 AM
  4. Replies: 7
    Last Post: 12-07-2009, 07:27 AM
  5. Replies: 0
    Last Post: 08-16-2008, 09:10 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