S
Susan H
When I open my subfrm in datasheet view, I can see values in all of the fields. When I link my subform to my form, I can only see the value in zpreqryAddressMaxDate.AddressID, and "#Name?" in all of the other fields. Can anyone tell me why?
The subfrm is linked to the frm by UniqueID (subfrm contains tblAddress.UniqueID, frm contains tblYouth.UniqueID.) Relationships are set as 1 to Many where a youth in tblYouth can have many addresses in tblAddress, and UniqueID is a foreign key in tblAddress. tblAddress has a compound index of Unique ID and AddressID, and AddressID is the primary key.
Query 3 calls Query 2 calls Query 1
Query 1
SELECT tblAddress.UniqueID, Max(tblAddress.AdrBeginDate) AS MaxOfAdrBeginDate
FROM tblAddress
GROUP BY tblAddress.UniqueID
ORDER BY tblAddress.UniqueID;
Query 2
SELECT tblAddress.AddressID
FROM utlqryMaxAdrBeginDate LEFT JOIN tblAddress ON (utlqryMaxAdrBeginDate.MaxOfAdrBeginDate = tblAddress.AdrBeginDate) AND (utlqryMaxAdrBeginDate.UniqueID = tblAddress.UniqueID);
Query 3
SELECT tblAddress.UniqueID, tblAddress.AddressID, tblAddress.AdrBeginDate, tblAddress.AdrEndDate, tblAddress.Address1, tblAddress.Address2, tblAddress..City, tblAddress.State, tblAddress.ZIP, tblAddress.AddressUpdateDate, tblAddress.CountyJuris, tblAddress.CountyRes, tblAddress.RegionJuris, tblAddress.RegionRes, tblAddress.DistrictJuris, tblAddress.DistrictRes, tblAddress.ResidenceType, tblAddress.GroupHomeID, *
FROM tblAddress RIGHT JOIN zpreqryAddressMaxDate ON tblAddress.AddressID = zpreqryAddressMaxDate.AddressID
WHERE (((tblAddress.AddressID)=[zpreqryAddressMaxDate].[AddressID]));
finally, the fiels will simply be viewed by the user in the subform, no updating or deleting.
Thanks! Hope that's not too much explanation.
P.S. one add-on question: is there any way to include all of three queries (nested) in the SQL view of one query, rather than 3 separate queries that call each other?
The subfrm is linked to the frm by UniqueID (subfrm contains tblAddress.UniqueID, frm contains tblYouth.UniqueID.) Relationships are set as 1 to Many where a youth in tblYouth can have many addresses in tblAddress, and UniqueID is a foreign key in tblAddress. tblAddress has a compound index of Unique ID and AddressID, and AddressID is the primary key.
Query 3 calls Query 2 calls Query 1
Query 1
SELECT tblAddress.UniqueID, Max(tblAddress.AdrBeginDate) AS MaxOfAdrBeginDate
FROM tblAddress
GROUP BY tblAddress.UniqueID
ORDER BY tblAddress.UniqueID;
Query 2
SELECT tblAddress.AddressID
FROM utlqryMaxAdrBeginDate LEFT JOIN tblAddress ON (utlqryMaxAdrBeginDate.MaxOfAdrBeginDate = tblAddress.AdrBeginDate) AND (utlqryMaxAdrBeginDate.UniqueID = tblAddress.UniqueID);
Query 3
SELECT tblAddress.UniqueID, tblAddress.AddressID, tblAddress.AdrBeginDate, tblAddress.AdrEndDate, tblAddress.Address1, tblAddress.Address2, tblAddress..City, tblAddress.State, tblAddress.ZIP, tblAddress.AddressUpdateDate, tblAddress.CountyJuris, tblAddress.CountyRes, tblAddress.RegionJuris, tblAddress.RegionRes, tblAddress.DistrictJuris, tblAddress.DistrictRes, tblAddress.ResidenceType, tblAddress.GroupHomeID, *
FROM tblAddress RIGHT JOIN zpreqryAddressMaxDate ON tblAddress.AddressID = zpreqryAddressMaxDate.AddressID
WHERE (((tblAddress.AddressID)=[zpreqryAddressMaxDate].[AddressID]));
finally, the fiels will simply be viewed by the user in the subform, no updating or deleting.
Thanks! Hope that's not too much explanation.
P.S. one add-on question: is there any way to include all of three queries (nested) in the SQL view of one query, rather than 3 separate queries that call each other?