Making a query updatable

[jabarlee]

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

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

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

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

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

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

[CJ_London]

sometimes you can make a query updateable by changing the type from dynaset to dynaset inconsistent updates, worth trying but probably wont work for 3 tables/queries.

Which tables to you want to update? What with? And if you only want the latest date from each table, use a dmax function or left join to a simple aggregate query (group on customerID, max date)

[June7]

You want most recent date or should that be data, as in the entire record?

Well, could use DLookup() expressions in third query to pull single value from each of the other two.

[jabarlee]

Thank you for your answers, I now see that I may have oversimplified things.
Both date tables have text fields (with no referrals whatsoever), and actually those are the one that I want to be updateable.
So both tbl_DateA & tbl_DateB structure is more like:
DateA_ID | DateA | FieldA1 | FieldA2
DateB_ID | DateB | FieldB1 | FieldB2

[CJ_London]

In that case, you can't create 1 query to update two different tables. Only way would be to use a form with subforms.

Alternative is to combine the two data tables - I appreciate you say 'each table represents completely different events that cannot be entered in a single table' but perhaps there is a halfway house where common fields (or at least those you want to be able to update) are in one table with links to records in the two other tables for the remaining fields.

[Micron]

In that case, you can't create 1 query to update two different tables.

I wasn't sure so I tested. You can.
Running out of time now so can't test if the major issue is the outer join, TOP predicate or something else.

[June7]

Yes, a query can allow edit to multiple dependent tables joined to same parent table if the relationships are all 1-to-1 (I do this). Your relationships are both 1-to-many.
I've never tried editing Top 1 query so just tested and query with all 3 datasets cannot be editable regardless of JOIN type.

Nothing you posted substantiates having two data tables.

[isladogs]

For clarification, if a SELECT query contains three or more tables AND is editable, you can change data from any of those tables manually.
However, AFAIAA, using an UPDATE query, only one table can be updated.

Does anyone disagree?

Also, as I don't think anyone has mentioned it so far, the best article on what makes queries read only is on Allen Browne's website: Microsoft Access tips: Why is my query read-only? (allenbrowne.com)

[June7]

Nothing in OP's narrative mentions UPDATE action SQL. Seems to me "updateable" is used as synonym for "editable".

[Micron]

Well, maybe I misinterpreted what updatable meant but not sure I agree with some of the comments that followed my last post. These are not my tables so don't worry about the spaces in names and such.

Relationship is 1 to many


query shows those 2 tables


2 records in one table, 1 in the other - properly updated


Take my word for it - those records were 123777 before the update. One record on the pk side, 2 on the many side. There are no other records on the many side with that pk value, so the update was correct. I guess I won't bother with testing anything else if update wasn't what was being asked for.

[isladogs]

Hi Micron
Look like I was wrong. That's a long established belief just proved incorrect!
I joined the PK field on 2 tables , set the query to unique values and was able to update a field in each of the tables

UPDATE DISTINCTROW tblLanguages INNER JOIN tblInstalledVoices ON tblLanguages.Code = tblInstalledVoices.Code SET tblLanguages.[Language] = "Arabia", tblInstalledVoices.[Language] = "Arabiz"
WHERE (((tblLanguages.Code)="ar"));

[jabarlee]

Sorry for being late to reply to any of these, but it seems there's a time difference...

As June7 mentioned, the problem is when having 3 datasets (the 2 of them being TOP 1), they're not editable (my "updateable" term was referring to access's message : "this recordset is not updateable")
I'm posting the resulting dataset that I'd like to be able to edit, plus the sample database, if anyone wants to give it a shot.
I was thinking that maybe there was a completely other way to achieve the same result (having the most recent entries of both tbl_Date tables for every customer). It just makes me wonder why having a query involving one TOP 1 subquery is editable, but having 2 of them is not

Cheers, Manolis

[Micron]

Maybe you should explain the purpose of the db in a way that tells us something about the process it supports. Your root problem might be that you've split the date tables (as perhaps alluded to in post 7). Why are there 2 date tables? What do numbered fields represent? Those are the real field names in the actual db?

[jabarlee]

Ok, this is a medical database. Customers are patients (with extra fields in the according table, that are not shown here for simplicity), DateA represents the patients visits (with corresponding fields A1, A2 etc having relevant info) and DateB represents their lab tests (with fields B1,B2 etc holding the actual results).
Up till now, the existing form(s) showed Data from the Customers table along with the most recent entry from the DateA table, and it was editable (involving one TOP 1 subquery wasn't a problem, apparently)
Now I tried to add the most recent entry from the DateB table and it shows fine, but now it's not editable anymore.
I tried to avoid using a subform for this new data from DateB table, because it would require redesigning some forms, I thought it would be easier modifying the original query, but it seems that's not the case :-)

[Micron]

By that description your tables are not normalized and you can expect to encounter one issue after another as you go along. You need to research db normalization - if the links below are not to your liking, find some that are. Each "thing" is an Entity and attributes (fields) of each entity belong in its own table. So tblPatients with only Fname, LName, DOB, etc. and maybe contact info. A visit/appt is its own entity. You may decide to break visits into one table and results into another, but all the tests that can be done should be in their own table. All of that depends on the process that needs to be supported. F'rinstance, if I attend my appointment and see 2 specialists in the process, this can complicate relating tests and results to not only who the patient was, but also who ordered the tests. IMO, the subject matter is a rather complicated one for anyone who lacks a thorough understanding of db normalization (not saying that's you) and the process of determining design before building any tables. Access is not Excel, but that's how you've started off your design. You might also want to look for sample db's for patient management once you think you're ok wrt normalization.

Actually, easier just to give you every link I've got for this in my Notepad file and let you have at it.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html