ms access query conditional set flag (1 Viewer)

idxyz

New member
Local time
Today, 11:32
Joined
May 18, 2020
Messages
24
I am running latest MS office. In my .mdb database got two tables, table 1 and table 2. The flag in a record in table1 has to be reset if that record is not found in table 2. The two tables have different number of records. I run the following record and takes too much time. I have to cancel it before it finishes. What is the best solution/approach?. My left join solution did not yield in correct result. It did not find the record that was in table1 but not in table2.

Code:
update table
set table1.flag1 = 0
where not exist (select 1 from table2
        where (NZ(table1.field1,"null") = NZ(table2.field1,"null")) AND
        where (NZ(table1.field2,"null") = NZ(table2.field2,"null")) AND
        where (NZ(table1.field3,"null") = NZ(table2.field3,"null")) AND
        where (NZ(table1.field4,"null") = NZ(table2.field4,"null")) AND
        where (NZ(table1.field5,"null") = NZ(table2.field5,"null")));
 

plog

Banishment Pending
Local time
Today, 13:32
Joined
May 11, 2011
Messages
11,611
UPDATE queries are generally hacks. Why must data be updated instead of just referencing a query?

My left join solution did not yield in correct result

What was that SQL?
 

Isaac

Lifelong Learner
Local time
Today, 11:32
Joined
Mar 14, 2017
Messages
8,738
Also, can you state in plain english terminology, what is the definition of "found in table 2" ?
I'd rather that than try to interpret it based on your post.

Hopefully this is a data remediation project that either implies the need for 1) a design change/improvement/correction or 2) a correction to business process mistakes, not a regular thing?
 

idxyz

New member
Local time
Today, 11:32
Joined
May 18, 2020
Messages
24
sorry for some shortcuts in the description. It refers to table1 record that has an extra boolean field. If some/critical fields in given record in table1 is not in table 2 then table1 flag for that given record has been reset.

I tried some approach like the following and still did not return the given records that are in table1 with their relevant records not in table2.

Code:
select *
from table1
let join table2 on (
        where (table1.field1 = table2.field1) AND
        where (table1.field2, = table2.field2) AND
        where (table1.field3, = table2.field3) AND
        where (table1.field4, = table2.field4) AND
        where (table1.field5,= table2.field5))
where table2.field1 is null;
 

Isaac

Lifelong Learner
Local time
Today, 11:32
Joined
Mar 14, 2017
Messages
8,738
You don't have any key columns to join on?
 

idxyz

New member
Local time
Today, 11:32
Joined
May 18, 2020
Messages
24
no. that is probably why. This is not my design. I do not wok on databases as profession. I just use them on my research. Need to extract some data with reasonable speed.
 

Isaac

Lifelong Learner
Local time
Today, 11:32
Joined
Mar 14, 2017
Messages
8,738
Hmm.

One method might be:
1) Create a query with the records from table1 that you DON'T want to flag. By doing an inner (straight) join between ALL columns that should match, and thus are found in both tables.
2) Create a 2nd query that shows items from table1 which are not in query1, (left join, where fields are null).
Actually I'm not sure why that method isn't working to simply used left joins and null criteria in the first place...can you post your database?
Along with a written set of instructions on which fields need to match in both tables in order to NOT be flagged?
 

plog

Banishment Pending
Local time
Today, 13:32
Joined
May 11, 2011
Messages
11,611
Time for some sample data to demonstrate what you have and what you want. So please post 2 sets of data:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect the query to end up with.

Again, 2 sets of data, not just starting data and an explanation. 2 sets of data where B is based on A.
 

idxyz

New member
Local time
Today, 11:32
Joined
May 18, 2020
Messages
24
Thanks for the helps.
I provide sudo tables as demo of what should look like. The record orders and the ID fields in two tables are not necessary the same. Need a query that does the check and reset the flag fastest.

Code:
table 1
ID field1 field2 field3 field4 field5 field6 flag
x    aaa     ccc    ddd     eee  ggg    ppp   1
x    aar     cbc    ddd     ewe  ggg    ppp   0

table 2
ID  field1  field2  field3  field4 field5 field6
x    aab     cce     ddd     eee    ggg    ppp
x    aaa     ccc     ddd     eee    ggg    ppp
x    jjj     ccc     ddd     eee    ggg    ppp

etc.
 

plog

Banishment Pending
Local time
Today, 13:32
Joined
May 11, 2011
Messages
11,611
You didn't provide expected results, so all I can say is that to find the records in table1 with all matching fields in table2 you would use this SQL:

Code:
SELECT table1.ID
FROM table1
INNER JOIN table2 ON table1.field1=table2.field1 AND table1.field2=table2.field2 AND table1.field3=table2.field3 AND table1.field4=table2.field4 AND table1.field5=table2.field5 AND table1.field6=table2.field6

That code will find the field1=aaa record of table1 and just that record.
 

Isaac

Lifelong Learner
Local time
Today, 11:32
Joined
Mar 14, 2017
Messages
8,738
Thanks for the helps.
I provide sudo tables as demo of what should look like. The record orders and the ID fields in two tables are not necessary the same. Need a query that does the check and reset the flag fastest.

Code:
table 1
ID field1 field2 field3 field4 field5 field6 flag
x    aaa     ccc    ddd     eee  ggg    ppp   1
x    aar     cbc    ddd     ewe  ggg    ppp   0

table 2
ID  field1  field2  field3  field4 field5 field6
x    aab     cce     ddd     eee    ggg    ppp
x    aaa     ccc     ddd     eee    ggg    ppp
x    jjj     ccc     ddd     eee    ggg    ppp

etc.
Demonstrated in attached database.
The tables are populated with the data you mentioned.
The first query (qry1_) finds items in table 1 which ARE matched in table 2. (i.e., the ones you do not want to toggle the Flag on)
The second query (qry2_) identifies items in table 1 which are NOT in qry1 (i.e., the ones you do want to toggle the Flag on).

All that's left is for you to change the qry2 to an UPDATE query, and update the flag accordingly..
 

Attachments

  • Testing 20200911 Flag.accdb
    468 KB · Views: 560
Last edited:

idxyz

New member
Local time
Today, 11:32
Joined
May 18, 2020
Messages
24
plog i need to set the flag in table1 for a given record that its field1 ..... field6 are not found in the table 2.

Issac, I will try your solution.
 

idxyz

New member
Local time
Today, 11:32
Joined
May 18, 2020
Messages
24
1. changed the 2nd row in table 2 like changed its field1 a bit. Then created a separate query to change Yes flag in table1 to No.
like Update table1 inner join qry2_able1_UnmatchedinTable2 on table.id = qry2_able1_UnmatchedinTable2.Table1ID set table1.flag = No;
Did not update the flag field in table1. I did not have success with changing the qry2 to undo the flag in table1.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:32
Joined
May 7, 2009
Messages
19,169
access modified my SQL:

Code:
UPDATE Table1 SET Table1.Flag = 0
WHERE (((Exists (SELECT * FROM Table2 WHERE
Field1 = Table1.Field1 And Field2 = Table1.Field2 And Field3 = Table1.Field3 And Field4 =  Table1.Field4 And Field5 = Table1.Field5 And Field6 = Table1.Field6))=False));
 

Isaac

Lifelong Learner
Local time
Today, 11:32
Joined
Mar 14, 2017
Messages
8,738
Did not update the flag field in table1. I did not have success with changing the qry2 to undo the flag in table1
Hmm. Not sure what to tell you. The queries I gave you were correct, and they successfully identified the record(s) that needed to be updated.
All you had to do was take the 2nd query, and change it from Select to Update, and then choose the field to update and click Run..
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:32
Joined
Feb 28, 2001
Messages
26,999
i need to set the flag in table1 for a given record that its field1 ..... field6 are not found in the table 2.

I'm looking into this thread. You said this isn't your design, but do you have the ability to do anything at all to the files?

First, your complaint was poor speed. If you can add single-column indexes to the tables, you can get incredible speed boosts.

Second, are you looking at exact matches for all six fields simultaneously?

If the answer to the 2nd question is YES, and if you can index the six fields, you should get good performance from something like this (but to save typing, I'm going to use f1-f6 instead of spelling out field1-field6, and T1, T2 instead of Table1, Table2.)

Code:
UPDATE T1 SET T1.Flag =1 
  WHERE T1.ID NOT IN
    ( SELECT T1.ID 
      FROM T1 INNER JOIN T2
        ON ( T1.F1 = T2.F1 ) AND
          ( T1.F2 = T.F2 ) AND
          ( T1.F3 = T.F3 ) AND
          ( T1.F4 = T2.F4 ) AND
          ( T1.F5 = T2.F5 ) AND
          ( T1.F6 = T2.F6 ) ) ;

The logic is that the SELECT subquery makes a list of all IDs from the first table where the records match an entry in the second table, but then the outer UPDATE query marks everything NOT in that list. So ... inner SELECT is every match, outer UPDATE is anything that didn't match.
 

idxyz

New member
Local time
Today, 11:32
Joined
May 18, 2020
Messages
24
all these are good and useful solutions. The problem is with design of an old database that needs to be replaced.
 

Users who are viewing this thread

Top Bottom