Results 1 to 2 of 2
  1. #1
    DanW is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    30

    Query problem with multiple tables

    I need help getting over a hump. It was so easy to use a very denormal table as a recordsource for a megaform. But I have been on the site now long enough to recognize I was very denormal. Now I am normal and very stupid.

    Anyway, I now have 33 tables in a virgin db instead of 6 and a cobweb of relationships. I am trying to make a query to be the recordset for my eventual megaform. I have one record in my tblCustomers, my friend Mickey Mouse, with ANPK_CustomerID = 1. My tables with enforced relationships from PK to FK are:

    tblCustomers
    CustomerID PKAN
    ChartNumber NoDuplicates
    LastName
    FirstName
    DateOfBirth
    TitleID FK
    DegreeID FK
    StreetAddress
    ZipCode ID FK
    PhotoID FK

    tblTitles
    TitleID PKAN
    TitleName

    tblDegrees
    DegreeID PKAN
    DegreeName

    tblZipCodes
    ZipCodeID PKAN
    City, State
    ZipCode

    tblPhotos


    PhotoID PKAN
    PhotoAttachment

    My problem is that when I add something from an outside linked table to the query, my query will no longer capture Mickey. I get a blank datasheet. The SQL is:

    SELECT tblCustomers.CustomerID, tblCustomers.ChartNumber, tblCustomers.LastName, tblCustomers.FirstName, tblCustomers.DateOfBirth, tblCustomers.TitleID, tblCustomers.DegreeID, tblCustomers.StreetAddress, tblCustomers.ZipCodeID, tblCustomers.PhotoID, tblTitles.TitleName, tblDegrees.DegreeName, tblZipCodes.ZipCode, tblPhotos.PhotoAttachment
    FROM tblZipCodes INNER JOIN (tblTitles INNER JOIN (tblPhotos INNER JOIN (tblDegrees INNER JOIN tblCustomers ON tblDegrees.DegreeID = tblCustomers.DegreeID) ON tblPhotos.PhotoID = tblCustomers.PhotoID) On tblTitles.TitleID = tblCustomers.TitleID) ON tblZipCodes.ZipCodeID tblCustomers.ZipCodeID; (This was typed, original done by design mode)

    Why would this not capture the single record in my tblCustomers, Mickey Mouse? This only uses 4 of my 33 tables. I am willing to learn how to use subforms, but do I have to use a subform for a customers title and separate ones for their degree, zipcode and photo? How complex can queries be?

    I had hoped to get one query to recordset for a 10 tab form. Now I'm looking at having 10-30 subforms on each of the tabbed pages. Is the best practice having queries from only 2 tables, a PK from the Identity table for choosing a customer and the datatable? If there is a junction table, do you include those fields as well?

    I felt bliss when I was denormal.

    Thanks,

    Dan

  2. #2
    DanW is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    30

    Tables can't be empty

    I have found that I have to put data in every field of all the tables in the query to get the query to return the recordset with Mickey Mouse in it.

    For example, I cannot put PhotoAttachment in the query, because there is no photo in tblPhotos. If I put data in the Titles, Degrees, and ZipCodes tables, it works.

    Helps to know I can make a complex query, but why can't it work with Nulls? Is there something wrong in my tables or query defaults?

    And also, is there a limit on the number of tables that can be used to make a recordset query for a form or report? Any issues to encourage many smaller queries than one larger query?

    If you could give me some guidance on those questions, I think my original problem is solved and the thread is solved.

    Thanks,

    Dan

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

Similar Threads

  1. From with multiple tables
    By Darth_Katarn in forum Access
    Replies: 1
    Last Post: 09-23-2009, 08:17 AM
  2. Tables and “all data query” ID problem
    By mashe in forum Queries
    Replies: 5
    Last Post: 08-22-2009, 06:05 AM
  3. Replies: 1
    Last Post: 07-07-2009, 01:00 PM
  4. using COUNT for multiple tables
    By kwalt in forum Queries
    Replies: 0
    Last Post: 02-17-2009, 03:06 PM
  5. Replies: 1
    Last Post: 07-06-2007, 08:27 AM

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