Find out if data one one table exist on another. (1 Viewer)

Mackbear · Sep 17, 2019

Hi, good day! hope you can help, I have a field on one table and I would like to know if the data exist on another table. Can't figure out how the query would be pls help.

Table 1: tbl_noticesinfo - table where the field I am looking for is. The field is entryid_info

Table 2: tbl_noticesinfo - table where I am looking for the field. Field where to look for is entryid_reso

Would like to find out if the entryid_info exist in entryid_reso

Thank you in advance!

isladogs · Sep 17, 2019

Perhaps you made a mistake but you listed the same table in both cases.
If you meant two different tables, just create a query and join by the fields you listed.
If the query result contains any records, the same data is in both fields

plog · Sep 17, 2019

A few things about what you've provided:

1. Both tables have the same name (tbl_noticesinfo). Is that correct? Are they in different databases?

2. Is their any way to narrow down the matching between the 2 tables? I understand you are looking for some data within a field, but do the 2 tables share another field that you can link them on? Otherwise this query can take a very long while to run.

Lastly, can you clarify what you need? Perhaps provide sample data. Provide 2 sets of data:

A. Starting data from both 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 return when you feed it the data from A.

Mark_ · Sep 17, 2019

Just to see if there are NONE, you can do an

Code:

IF IsNull([URL="https://support.office.com/en-us/article/dcount-function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3"]DCount[/URL]("*","tbl_noticesinfo", "entryid_reso = " & entryid_info)) THEN 
   Your code here for if it isn't found
END IF

Though it if you can explain more about what you are trying to do we may be able to give you a much better solution.

isladogs · Sep 17, 2019

Mark_ said:

Just to see if there are NONE, you can do an

Code:

IF IsNull([URL="https://support.office.com/en-us/article/dcount-function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3"]DCount[/URL]("*","tbl_noticesinfo", "entryid_reso = " & entryid_info)) THEN 
   Your code here for if it isn't found
END IF

If there are no matches, DCount will be zero.
Using IsNull would appear to be inappropriate here

Mackbear · Sep 17, 2019

Hello everyone I am sorry the 2nd table should be tbl_noticesreso..

Mackbear · Sep 17, 2019

plog said:
A few things about what you've provided:

1. Both tables have the same name (tbl_noticesinfo). Is that correct? Are they in different databases?
I'm sorry the 2nd table is tbl_noticereso

2. Is their any way to narrow down the matching between the 2 tables? I understand you are looking for some data within a field, but do the 2 tables share another field that you can link them on? Otherwise this query can take a very long while to run.

Yes the fields that links them is entryid_noticesinfo and entryid_noticereso. Those are the primary keys on tbl_noticesinfo and tbl_noticereso respectively.

Lastly, can you clarify what you need? Perhaps provide sample data. Provide 2 sets of data:

I need to find out if the entryid_noticesinfo value exist on entryid_noticereso.

A. Starting data from both 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 return when you feed it the data from A.

Need to find out the entryid_noticesinfo that doesn't have a pair in entryid_noticereso. :banghead::banghead::banghead:

Mackbear · Sep 17, 2019

isladogs said:
If there are no matches, DCount will be zero.
Using IsNull would appear to be inappropriate here

Hmm... I need to find out the entryid_info that does not have a pair in entryid_reso. Like this list of them that does not have a pair in entryid_reso which is in the tbl_notice reso… I hope it's not confusinh...:banghead::banghead:

plog · Sep 17, 2019

Sample data as prescribed please.

Mark_ · Sep 17, 2019

isladogs said:
If there are no matches, DCount will be zero.
Using IsNull would appear to be inappropriate here

Had a brain fart on that one... Was thinking first of just a DLookup, but realized he may have need for the number of matching.

deletedT · Sep 17, 2019

I always use a SELECT sql with Exits. Because it's the fastest possible way.
My tables have more than several thousands record on each side.
Going through all records and do a DCount would take a long time.

The following sql gives me a list of all the records from table1 where the same ID doesn't exits in table2 in less than a second:

SELECT ID, Field1, field2 FROM table1 WHERE NOT Exists
(SELECT * FROM table2 WHERE table1.ID=table2.ID)

isladogs · Sep 17, 2019

@Mackbear
You've changed your description of what you want several times so I'm not at all certain what you want.
If you want a list of all items in one table that do not exist in the other, you cn create a suitable query using the unmatched query wizard.
However if you want to know whether a specific record exists, there are at least four approaches you can use. The best (fastest) method depends on whether or not the field is indexed. See Http://www.mendipdatasystems.co.uk/speed-comparison-tests-7/4594524997Check Record Exists

arnelgp · Sep 17, 2019

use query:

Code:

select entryid_info, (select count("1") from tbl_noticesreso as T 
         where T.entryid_reso = tbl_noticesinfo.entryid_info), as [no found in tbl_noticesreso] 
from tbl_noticesinfo;

Find out if data one one table exist on another. (1 Viewer)

Mackbear

Registered User.

isladogs

MVP / VIP

plog

Banishment Pending

Mark_

Longboard on the internet

isladogs

MVP / VIP

Mackbear

Registered User.

Mackbear

Registered User.

Mackbear

Registered User.

plog

Banishment Pending

Mark_

Longboard on the internet

deletedT

Guest

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread