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