Solved Get the different values between two tables (1 Viewer)

[zelarra821]

Buenas tardes.

I have two tables, in which I save the names of files that I have in a folder on my computer.

In one of the tables (Table1), I save the name that I get when traversing the folders using VBA.

And in the other table (Table2), I keep a manual record because each file I record it in a series of categories that would be difficult to do using VBA.

So, what I need is to obtain the names that are in Table2 but that no longer exist in the computer, that is, they would not appear, therefore, in Table1. And, on the other hand, the names of Table1 that are not yet registered in Table2.

Both names are not related by ID.

Nor have I been able to do it by query: first a union query of both tables, and then a select distinct, because obviously it shows me a relationship of unique records, and that's not what I want.

Let's see, right now I'm doing it through VBA, going through the files in the folder, but since there are many, it takes me a long time to perform the verification, and I wanted to know if it was possible to do it through the tables, or queries, with something else simple and fast, because the information is already in the tables.

Thanks!

 

[bastanu]

Have a look at the "unmatched" query wizard under Create\Query.
Basically you need to have a left outer join from Table2.FileName---->Table1.FileName and add Table1.FileName with Is Null in the criteria row.
Cheers,

 

[Isaac]

Let's see, right now I'm doing it through VBA, going through the files in the folder, but since there are many, it takes me a long time to perform the verification

Can't you just loop through the table and use Dir() to check for the existence of the file? That seems quite fast to me.
Don't start from the folder files, start from the table.

Of course you can write a VBA function and then use it in a query, or just write a recordset loop, or a tabledef based thing - same difference either way they will all be RBAR

 

[zelarra821]

Can't you just loop through the table and use Dir() to check for the existence of the file? That seems quite fast to me.
Don't start from the folder files, start from the table.

Sure, I'm doing it with two procedures, one for each direction: one with a dir, and the other with an each ... for ... next, but since there are many files, it takes me a long time, and I wanted to know only if there is another way to do it.

 

[zelarra821]

Have a look at the "unmatched" query wizard under Create\Query.
Basically you need to have a left outer join from Table2.FileName---->Table1.FileName and add Table1.FileName with Is Null in the criteria row.
Cheers,

Hi, I just tested what you say and it returns that the data types in the criteria expression do not match. Also, I don't really know what it has to show as results in case there are no records.

SELECT TVideos.*
FROM TVideos LEFT JOIN CFiles ON TVideos.[Nombre] = CFiles.[FolderFileName1]
WHERE (((CFiles.FolderFileName1) Is Null));

 

[Isaac]

Sure, I'm doing it with two procedures, one for each direction: one with a dir, and the other with an each ... for ... next, but since there are many files, it takes me a long time, and I wanted to know only if there is another way to do it.

I thought of a better way.

1. Using Dir() (and perhaps recursive), obtain a list of all files (with full paths), that actually are in the folders. Place that information in a table. This should only take a few seconds to run.
2) Run the query now ... and that should only take a few seconds to run, too.

 

[zelarra821]

I have not been able to explain myself.

The list of updated files that are in the folders I already have in a table, I don't need to pass any dir. I only need to compare with what I am putting by hand that there is no more or no lack.

 

[Isaac]

Oh.
Then why are you doing it in VBA, and why does it take a long time? Use Bastanu's suggestion with properly structured data

 

[bastanu]

Please have a look at the attached sample, should put you on the right track.
Cheers,
Vlad

 

[MajP]

If you do a left join

 FROM TVideos LEFT JOIN CFiles

You can see what is in Tvideos and not in CFILES

if you do it the other direction

FROM CFiles LEFT JOIN TVideos

You can see what is in CFiles and not in TVideos

 

[zelarra821]

I have managed to do it in one sense. In this:



But if I try it the other way around, it tells me "Datatypes in criteria expression do not match":



I do not care if I put Is Null or not, in both it gives me the same error.

 

[MajP]

Can you upload a small database that just has those two tables in it?

 

[zelarra821]

Here it is

 

[MajP]

The issue is the folderFileName1 is a calculated field that uses string functions. If that field is null the calculated value is not null it is #error. Do the null check in another field on that table that is not calculated.

SELECT CFilesVideos.folderfilename1 AS VideoName
FROM CFilesTreeview RIGHT JOIN CFilesVideos ON CFilesTreeview.FolderFileName1 = CFilesVideos.FolderFileName1
WHERE (((CFilesTreeview.DefaultFolderNumber) Is Null));

 

[zelarra821]

You are amazing, thank you very much.