Deleting lines from a query

[mcucino]

Background: I have a query called DuplicatesQ. It is an inner join of two tables, DuplicatesT and TimeEntryT. It is an editable query and the users can look through the lines and delete ones as they see fit. The problem I am encountering is that it's only deleting these items from DuplicatesT and not TimeEntryT. I would be fine with either deleting it from both or deleting it from just TimeEntryT but the important thing is that it must be removed from TimeEntryT. I've tried making sure the SQL lists TimeEntryT as the main table inner joined with DuplicatesT and vice versa with no luck. I know I can just put in SQL on the on delete code to also delete the item from the TimeEntryT, however I really just want to know the logic behind why it chooses one over the other.

Question: How does access determine which of two joined queries to delete from when the user manually deletes a line from a form based on said query?

[Micron]

The main problem as I see it is, it's not kosher to work in tables. This would include queries that involve joined tables. Both are what are called "domains" and you should not be editing in 'domains'. The proper approach would be handling this via forms. That being said, maybe you could accomplish this if you created a relationship between said tables and employed "cascade update" and "cascade delete" - not that it would be my preferred solution. The foregoing assumes you're working in a SELECT query and not a DELETE query.

EDIT - forgot to inquire why there are 2 tables that seem to contain the same information. Perhaps this is temporary and you're trying to consolidate records? Seems to me that it should not be an on-going situation.

[orange]

Please show the SQL of the query involved.

Here is an example of a DELETE QUERY With Joined Tables from FMS
(Still only deletes records from 1 table.)

Delete Records with Values from Fields in Another Table

In this example, the records in a call list are deleted if they placed an order and got added to the customer list with their CallListID:

DELETE tblCallList.* 
FROM tblCallList INNER JOIN tblCustomers 
ON [tblCallList].[CallListID] = [tblCustomers].[CallListID]

[mcucino]

Thanks Micron, excellent question. Maybe getting to the root of why I needed a temporary DuplicateT to begin with would be a better approach. In typical me fashion, I can't remember exactly why I did it that way. I think I was getting the "too complex to be evaluated" error when querying directly on TimeEntryT, so I made a smaller data set by making the DuplicatesT from a subset of the TimeEntryT (I first tried running it on a query that had the same criteria restrictions as the maketable query but still got the "too complex error" and it finally worked when I tried a temp table). Another issue is that it runs much faster off of a temporary DuplicatesT than it does querying even the much smaller TimeEntryQ. The query in question uses dcount to sequence the entries in TimeEntryT with duplicate information with the following function:
dcount("*","DuplicatesT","Client = '"& client & "' and in = " & in & " and out = " & out & " and ID < " & ID)

I'm open to any and all suggestions to do this in a better way.

[mcucino]

It is a select query that feeds into a form where the user can delete an individual line, it is not a delete query

[mcucino]

Since this thread migrated from a more generic question about how access handles queries to the topic of improving on what I am trying to do, I will give some more background:

This database is for an agency which offers adult day services for people with developmental disabilities. Each client clocks in every morning and their time can be coded to one of many different service types (like employment coaching or community activities). Each client can be in only one service type at a time, so if there are two records for John Smith on 9/19/18 from 9 am to 12 pm, and one says he was in employment coaching and the other says he was in community activities, the billing coordinators needs to be able to see both and determine which one is incorrect so she can delete it. I am trying to accomplish this by counting and sequencing entries with duplicate info in the client, date, in and out fields. The Duplicates form displays only entries that have a count of more than one instance for her to review. The sequence is also included so she can click "Delete all duplicates" which removes anything with a sequence > 1

[orange]

I recommend you show us your table design (a jpg of your relationships window with all tables extended to show all fields).

Without seeing your database or details of the user data capture, it would seem some verification/validation to prevent duplicate entries for the same time period is critical.

[mcucino]

I can't post a screenshot now because the query is running and I don't want to interrupt it, but there are no relationships, it's all the same data set. I only have a maketable that limits the data set in order to sequence/count duplicate entries faster. TimeEntryT fields are Client, In, Out, Date, Service Type. It's very straightforward.

As far as data validation, they are filling out paper timesheets and one person is entering the data. They don't have the resources to make the time cards digital. If the data entry person receives conflicting info (duplicates with different service types), she has to enter all of it and her manager reviews it and makes a decision.

I recommend you show us your table design (a jpg of your relationships window with all tables extended to show all fields).

Without seeing your database or details of the user data capture, it would seem some verification/validation to prevent duplicate entries for the same time period is critical.

[Micron]

"too complex to be evaluated" error

This could simply have been a join issue. Agree with orange; a view of the relationships should help, assuming you have created one. Even a relationships view of the tables with no joins would help if you haven't created one. Sounds to me like you should have a form/subform design. Main form has the person data, subform the time sheet data. Move to a new person on the main form, new ts data shows for new person. Unwanted records could be deleted from the subform list. This assumes that the subform records are not based on a query that is not updatable. Before creating said subform based on a query, I'd create that query (assuming you are not/cannot base it on a table) and try to update/delete using the query. If you cannot, then further thought is required. If based on a table, should work easily enough - provided that you don't have relationships that prevent deletion.