Hello to everyone. I'm having trouble to make a query updateable, and I'd like some help, if there's a solution for it.
I have my main customers table (tbl_Customers) and two tables (tbl_DateA , tbl_DateB) that represent event dates related to the customers. (each table represents completely different events that cannot be entered in a single table, this is simplified for argument's sake)
In order to display only the most recent entry of each table for each customer I used the 2 following queries:
qry_Top_DateA
Code:
SELECT tbl_DateA.*
FROM tbl_DateA
WHERE (((tbl_DateA.DateA) In (SELECT TOP 1 DateA
FROM tbl_DateA AS Dupe
WHERE Dupe.Cust_ID = tbl_DateA.Cust_ID
ORDER BY Dupe.DateA DESC)))
ORDER BY tbl_DateA.Cust_ID;
qry_Top_DateB
Code:
SELECT tbl_DateB.*
FROM tbl_DateB
WHERE (((tbl_DateB.DateB) In (SELECT TOP 1 DateB
FROM tbl_DateB AS DupeB
WHERE DupeB.Cust_ID = tbl_DateB.Cust_ID
ORDER BY DupeB.DateB DESC)))
ORDER BY tbl_DateB.Cust_ID;
Then I created a new query that displays all the customers from tbl_Customers with their most recent date from tbl_dateA, which is an updateable query:
qry_CustTopDateA
Code:
SELECT tbl_Customers.*, qry_Top_DateA.DateA, qry_Top_DateA.DateA_ID
FROM tbl_Customers LEFT JOIN qry_Top_DateA ON tbl_Customers.ID = qry_Top_DateA.Cust_ID;
...and there's where I'm stuck because I cannot create an updateable query that displays the most recent dates from both tables. Neither this:
qry_CustTopDateAB
Code:
SELECT tbl_Customers.*, qry_Top_DateA.DateA, qry_Top_DateA.DateA_ID, qry_Top_DateB.DateB
FROM (tbl_Customers LEFT JOIN qry_Top_DateA ON tbl_Customers.ID = qry_Top_DateA.Cust_ID) LEFT JOIN qry_Top_DateB ON tbl_Customers.ID = qry_Top_DateB.Cust_ID;
or this:
qry_CustTopDateAB_alt
Code:
SELECT qry_CustTopDateA.*, qry_Top_DateB.DateB, qry_Top_DateB.DateB_ID
FROM qry_CustTopDateA LEFT JOIN qry_Top_DateB ON qry_CustTopDateA.ID = qry_Top_DateB.Cust_ID;
I understand that I could handle my data using separate subforms, but these tables are new entries to an existing database with quite a lot forms and I'm trying to avoid redesigning all of them
Thanx in advance