Cross Checking two tables

[eric.kung]

Dear All

I am new to MS Access and I need to develop a program.
This program will cross check two tables.

Table A and Table B have same fields, they are the


File_name and Transmission_time

Table A is a log file sent from my FTP server.
Table B is a log file sent from receiver to acknowledge me the files they have received.

Now I need to compare these two files and I would like to use MS Access.

Can anyone please teach me how I can do this?
Is there a website or something which I can read to learn how to do this??
Can the super specialists in this forum kindly teach me how I can do this?

Thank you very much!

[maximus]

Tried to replicate your problem. I have two tables Table3 and Table4. Table3 will represent my FTP Server Log info and Table4 is the receiver Log info.

Both Tables have two Fields
1) File_Name
2) Transmission_time

I have used DAO.Recordsets to Compare the table. File_Name and Transmission_time from Table3 is used to open a specific RecordSet if the Recordset is empty it is tagged as a not received. A log file is created in note pad change the directory and the log file name as per your requirement. This code can be further modified to show only the files not received.

Private Sub Command8_Click()
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strSQL As String
Dim MyString As String

Set rst = CurrentDb.OpenRecordset("Table3")
Do While Not rst.EOF
strSQL = "Select * from Table4 Where File_name='" & rst!File_name & "' And Transmission_time=#" & rst!Transmission_Time & "#"
Set rst1 = CurrentDb.OpenRecordset(strSQL)
If rst1.BOF And rst1.EOF Then
    If Len(MyString) > 0 Then
        MyString = MyString & vbCrLf & rst!File_name & " Tramission time " & rst!Transmission_Time & " not received"
    Else
       MyString = rst!File_name & " Tramission time " & rst!Transmission_Time & " not received"
    End If
Else
    If Len(MyString) > 0 Then
        MyString = MyString & vbCrLf & rst!File_name & " Tramission time " & rst!Transmission_Time & " received"
    Else
        MyString = rst!File_name & " Tramission time " & rst!Transmission_Time & " received"
    End If
End If

rst1.Close
Set rst1 = Nothing

rst.MoveNext
Loop
rst.Close
Set rst = Nothing

'MsgBox MyString

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("d:\comparelog.txt", True)
a.WriteLine (MyString)
a.Close

End Sub

[eric.kung]

Dear Maximus
I have a few questions regarding the code.

[code]"rst"/code] has appeared several times in the code.
At the top it shows

"Dim rst As DAO.Recordset"

What does this part means?

What does

"Do While Not rst.EOF"

mean?

The same, what is rst1?
Is this self determine or is this per visual basic code?
Can rsf be called something else if I would like it to be?

Set rst = CurrentDb.OpenRecordset("Table3")

Does this mean
Open record, current database = Table 3?

Noticed a piece of code:

strSQL = "Select * from Table4 Where File_name='" & rst!File_name & "' And Transmission_time=#" & rst!Transmission_Time & "#"
Set rst1 = CurrentDb.OpenRecordset(strSQL)

If what I expected is correct about rst, then why can't the same rule happen for rst1? I am just curious with this one.


One more piece of the code requiring your advice on.

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("d:\comparelog.txt", True)
a.WriteLine (MyString)
a.Close

Does this code represent the create a text log file that you mentioned just now?
Is it possible for this to be in a different format? Let's say XLS?

[rpeare]

I think I would do this a little bit differently than maximus is suggesting. I assume you're looking for exceptions (files that appear on one list or the other, but not both)

If that's the case what I would likely do is create a union query that only showed mismatching files. I am not familiar with your process, but if you have a one way door, i.e. the customer can not possibly list a file that you have not sent them, then it's a much easier problem and you can do it with a regular select query.

Here's an example of what I'm talking about.

[eric.kung]

I think I would do this a little bit differently than maximus is suggesting. I assume you're looking for exceptions (files that appear on one list or the other, but not both)

If that's the case what I would likely do is create a union query that only showed mismatching files. I am not familiar with your process, but if you have a one way door, i.e. the customer can not possibly list a file that you have not sent them, then it's a much easier problem and you can do it with a regular select query.

Here's an example of what I'm talking about.

Thank you very much!
It works great! Now I just have to put this in a form and see how I can make this displayed in a form (Or produce report).

Edit:
Question.
Rpeare
Can this code cross check two fields?
The code you provided helps to check the file name, if there is a missing or not the same then it will bring up an alert in the query.
I have tested this with my tables and it works brilliantly.

However, my database is a little more complex than that...
There is also a date field where I have to cross check this as well.
Meaning that: when the filename AND the date matches with two tables then it will be OK. While if there is a miss on that, it will then give me an alert by putting the file onto the query.
Can this do that? If so can you please teach me how may I do this?

Can you please tell me how I can cross check using your query code against multiple fields within a table?
Thank you.

Edit:
Also
I am now making a cross check procedure with query.
Is it possible to:
Extract my query result to a table? Also creating the execution date of the query?

The thing is:
If the receiver hasn't receive our file for more than 2 days we will then resend the file to them.
Therefore we need to know if it is possible to create a table when a query is run. (Or I pre-create)
Then everytime query is run, the query data will automatically go into this table?

[rpeare]

Can this code cross check two fields?

Yes, in the queries link the two relevant fields rather than just one, in your case file date/name, assuming you can have the same file name on multiple dates.

Edit:
Also
I am now making a cross check procedure with query.
Is it possible to:
Extract my query result to a table? Also creating the execution date of the query?

The thing is:
If the receiver hasn't receive our file for more than 2 days we will then resend the file to them.
Therefore we need to know if it is possible to create a table when a query is run. (Or I pre-create)
Then everytime query is run, the query data will automatically go into this table?

What?

I don't understand. If you are trying to send your customer all the files that are listed in your database that are no in their list (moving existing files around?) Or are you looking to generate new files based on data that is in your database (create new files and send them to your customer either via email or ftp etc) or are you strictly just updating a list for them?

[maximus]

Answers:
You need to understand how the code is suppose to work.
Assuming there are two tables:
1) Table3 FTP LOG
2) Table4 User LOG

Now I need to find which all files have been actually received by the user and which has not been received. Following is the explanation of the code:

I open Table3 as a DAO.Recordset rst . Then I use a Do while loop to loop through all the records it. This process ifs continued till it reaches the end of the recordset (EOF means end of File). Code used for this purpose:

Set rst = CurrentDb.OpenRecordset("Table3")
  Do While Not rst.EOF

I use the File_name and Transmission_time of a particular record in Table3 as the code loops through the recordset rst (Table3) as the criteria of to open the identical record in table4. Code used for this purpose

strSQL = "Select * from Table4 Where File_name='" & rat!File_name & "' And Transmission_time=#" & rat!Transmission_Time & "#"
  Set rst1 = CurrentDb.OpenRecordset(strSQL)

rst1 is the second recordset containing the record as per strSQL

Here there can be two conditions:
1) Code finds the Identical Record in Table4.
2) Code Does not find the Identical Record in Table4.

The following condition is checked by this piece of code:

If rst1.BOF And rst1.EOF Then

if this condition is true then there are no identical record in Table4 for a record inTable3. Then this record in Table3 is tagged as not received. If an identical record is found in Table4 for a record in Table3 then it is tagged as received the following piece of code is used to do it:

If rst1.BOF And rst1.EOF Then
      If Len(MyString) > 0 Then
          MyString = MyString & vbCrLf & rst!File_name & " Tramission time " & rst!Transmission_Time & " not received"
      Else
         MyString = rst!File_name & " Tramission time " & rst!Transmission_Time & " not received"
      End If
  Else
      If Len(MyString) > 0 Then
          MyString = MyString & vbCrLf & rst!File_name & " Tramission time " & rst!Transmission_Time & " received"
      Else
          MyString = rst!File_name & " Tramission time " & rst!Transmission_Time & " received"
      End If
  End If

Then both the Recordsets are closed. Code used for the purpose:

rst1.Close
  Set rst1 = Nothing
  rst.MoveNext
  Loop  
  rst.Close
  Set rst = Nothing

My String is a variable in which the Result is stored which is the written to a txt file to create a log file.

Set fs = CreateObject("Scripting.FileSystemObject")
  Set a = fs.CreateTextFile("d:\testfile.txt", True)
  a.WriteLine (MyString)
  a.Close

rst and rst1 are DAO.Recordsets. To leran more about it use the link below.
http://msdn.microsoft.com/en-us/libr...=vs.71%29.aspx


EOF means end of File. Do while not rst.EOF is indicative of the fact that the Do While loop will continue to run till it reaches the last record of the recordset rst.


Yes you can use anything in place of rst. I have used rat instead of rst in the example below. Make sure the name of the recordset used in your code matches the one you used in your declaration. e.g.

Dim rat As DAO.Recordset
  Dim rst1 As DAO.Recordset
  Dim strSQL As String
  Dim MyString As String
   
  Set rst = CurrentDb.OpenRecordset("Table3")
  Do While Not rat.EOF
  strSQL = "Select * from Table4 Where File_name='" & rat!File_name & "' And Transmission_time=#" & rat!Transmission_Time & "#"

This code can be modified in various ways:
1) Create a Log Table which will contain the comparison result.
2) Create the comparison Log on an Excel Sheet.

I am happy rpeare came up with a much simpler solution for you which suits your purpose. Keep up the good work. Please let me know if you have any more queries.

[eric.kung]

Yes, in the queries link the two relevant fields rather than just one, in your case file date/name, assuming you can have the same file name on multiple dates.

What?

I don't understand. If you are trying to send your customer all the files that are listed in your database that are no in their list (moving existing files around?) Or are you looking to generate new files based on data that is in your database (create new files and send them to your customer either via email or ftp etc) or are you strictly just updating a list for them?

I have modified the code a bit to suit my tables.

SELECT tbl_AckLog.File_Name_ACKLog, "ACKLog Only" as Notice
FROM tbl_AckLog LEFT JOIN tbl_POLog ON tbl_AckLog.File_Name_ACKLog = tbl_POLog.FileName_POLog
WHERE (((tbl_POLog.FileName_POLog) Is Null));
GROUP BY Notice

UNION ALL SELECT tbl_POLog.FileName_POLog, "POLog Only" as ItemLabel
FROM tbl_POLog RIGHT JOIN tbl_AckLog ON tbl_POLog.FileName_POLog = tbl_AckLog.File_Name_ACKLog
WHERE (((tbl_AckLog.File_Name_ACKLog) Is Null));

The code you provided works excellently but atm it only checks for 1 field.

From my knowledge, it first creates a dummmy field called "Notice".
Notice will display a message "ACKLog Only" when this file is ONLY in ACKLog (Customer Table) and not POLog (Local Table).

However, now I need to cross check TWO fields in both tables.
tbl_AckLog.File_Name_ACKLog and tbl_ACKLog.Transmission_Date
I have tried to make a code to check both codes but it didn't work.

This is what I tried.

SELECT tbl_AckLog.File_Name_ACKLog & Transmission_Date, "ACKLog Only" as Notice
FROM tbl_AckLog LEFT JOIN tbl_POLog ON tbl_AckLog.File_Name_ACKLog = tbl_POLog.FileName_POLog
WHERE (((tbl_POLog.FileName_POLog & tbl_POLog.Transmissiondate_POLog) Is Null));
GROUP BY Notice

I have no idea what the & sign works in query so I just try... I have searched for a few solution but all I can find is

select *

and that is not quite what I am looking for...
Can you please help? How do I make this code to go by multiple fields?


2nd request:

I am now doing the comparison of two tables. The code you provided successfully creates a dummy data but can this dummy data be stored in another table too?
What I would love to have is:
When I run this query, the query will automatically cross check all the files I have imported to my database.
The query will then create a table, a new table which stores the compared record and the QUERY RUN TIME (In date).
I will then need to use this brand new table to compare again as the data we sent to the customer may have not yet arrived and with a little more waiting, it may have arrived.
Which is why I need the query run time if this is possible.



@Maximus

I am currently testing on the code.
Or more like, learning how to read VB! Haha
Thank you very much for explaining, it means a lot.

[rpeare]

SELECT tbl_AckLog.File_Name_ACKLog, tbl_AckLog.Transmission_Date, "ACKLog Only" as Notice
FROM tbl_AckLog LEFT JOIN tbl_POLog ON tbl_AckLog.File_Name_ACKLog = tbl_POLog.FileName_POLog
WHERE (((tbl_POLog.FileName_POLog & tbl_POLog.Transmissiondate_POLog) Is Null));
GROUP BY Notice

I haven't ever tried to make a union query without having an equal number of columns in both, so if your secondary table does not have a transmission date field or something equivalent you may need to create a 'dummy' field to make it come out.

Just like any other query you have to separate values by a comma (,) in your SQL code.


2nd request:

I am now doing the comparison of two tables. The code you provided successfully creates a dummy data but can this dummy data be stored in another table too?
What I would love to have is:
When I run this query, the query will automatically cross check all the files I have imported to my database.
The query will then create a table, a new table which stores the compared record and the QUERY RUN TIME (In date).
I will then need to use this brand new table to compare again as the data we sent to the customer may have not yet arrived and with a little more waiting, it may have arrived.
Which is why I need the query run time if this is possible.

When I run this query, the query will automatically cross check all the files I have imported to my database.

The queries do this right now, correct? They are showing you files that do not exist on both tables.

The query will then create a table, a new table which stores the compared record and the QUERY RUN TIME (In date).

There is no reason to create a table for this. In your query you can have a field called CurrentDate: date() and it will populate with the current date when you run the query, you can even use now() if you need a time stamp as well as a date stamp

I will then need to use this brand new table to compare again as the data we sent to the customer may have not yet arrived and with a little more waiting, it may have arrived.

Don't compare the data again until the CUSTOMER updates their list, comparing items when they haven't had a chance to update is just asking for trouble.

[eric.kung]

Hey rpeare

I have been trying on the code but have been unsuccessful.

SELECT tbl_AckLog.File_Name_ACKLog, tbl_AckLog.Transmission_Date, "ACKLog Only" as Notice
FROM tbl_AckLog LEFT JOIN tbl_POLog ON tbl_AckLog.File_Name_ACKLog = tbl_POLog.FileName_POLog
WHERE (((tbl_POLog.FileName_POLog, tbl_POLog.Transmissiondate_POLog) Is Null));
GROUP BY Notice



UNION ALL SELECT tbl_POLog.FileName_POLog, tbl_POLog.Transmissiondate_POLog, "POLog Only" as ItemLabel
FROM tbl_POLog RIGHT JOIN tbl_AckLog ON tbl_POLog.FileName_POLog = tbl_AckLog.File_Name_ACKLog
WHERE (((tbl_AckLog.File_Name_ACKLog, tbl_AckLog.Transmission_Date) Is Null));

I receive the error:
Syntax error (comma) in query expression '(((tbl_POLog.FileName_POLog, tbl_POLog.Transmissiondate_POLog) Is Null))'.

How do I check per two fields?


-------------------------------------------------

2nd request

I have been unsuccessfully using "CURDATE".
I want the check to display the current date of validation.

I came up with this but I receive error message.

SELECT NOW() as Now ,tbl_AckLog.File_Name_ACKLog, "ACKLog Only" as Notice
FROM tbl_AckLog LEFT JOIN tbl_POLog ON tbl_AckLog.File_Name_ACKLog = tbl_POLog.FileName_POLog
WHERE (((tbl_POLog.FileName_POLog) Is Null));


UNION ALL SELECT NOW() as Now,tbl_POLog.FileName_POLog, "POLog Only" as ItemLabel
FROM tbl_POLog RIGHT JOIN tbl_AckLog ON tbl_POLog.FileName_POLog = tbl_AckLog.File_Name_ACKLog
WHERE (((tbl_AckLog.File_Name_ACKLog) Is Null));

But with NOW it comes with the time too. I do not need the time, I only need the current time.
Can you please teach me, how may I do that?


-------------------------------------------------

3rd request

The code seem to only inform me when AckLog table has missing files but it does not inform me about POLog table.

SELECT tbl_AckLog.File_Name_ACKLog, "ACKLog Only" as Notice, NOW() as Now 
FROM tbl_AckLog LEFT JOIN tbl_POLog ON tbl_AckLog.File_Name_ACKLog = tbl_POLog.FileName_POLog
WHERE (((tbl_POLog.FileName_POLog) Is Null));



UNION ALL SELECT NOW() as Now,tbl_POLog.FileName_POLog, "POLog Only" as ItemLabel
FROM tbl_POLog RIGHT JOIN tbl_AckLog ON tbl_POLog.FileName_POLog = tbl_AckLog.File_Name_ACKLog
WHERE (((tbl_AckLog.File_Name_ACKLog) Is Null));

Is there something I have done wrong? I have tried swapping them around (Logically that won't work but anyway...) but it doesn't work, it only checks and shows "AckLog Only".
How can I make it to show "POLog Only" too?

[rpeare]

When you're working with union queries each portion of your query that is separated by a UNION statement is a separate query. To make the query behave as you want work on ONE section of it. Cut and paste the SQL statement from the first part of the Union query I gave you, put it into the design view of a new query and look at how it's built. Then modify it by adding the link to the secondary field you want to check. Once you have that working look at the second one (in the example I gave you it's just a left join/right join reversal so it's fairly simple) then paste the SQL code back into the appropriate section of your union query and it should work.

[LukeChung-FMS]

You may want to consider our Total Access Detective program that lets you find differences between any two Access databases or objects. That includes data between two tables.

If the tables have identical primary keys, it will find records in one table and not the other, and records that were modified. If the tables are not keyed, it'll identify the first record that differs.

More details about table design and data differences here: http://www.fmsinc.com/MicrosoftAcces...lds/tables.asp

Hope this helps.

[eric.kung]

When you're working with union queries each portion of your query that is separated by a UNION statement is a separate query. To make the query behave as you want work on ONE section of it. Cut and paste the SQL statement from the first part of the Union query I gave you, put it into the design view of a new query and look at how it's built. Then modify it by adding the link to the secondary field you want to check. Once you have that working look at the second one (in the example I gave you it's just a left join/right join reversal so it's fairly simple) then paste the SQL code back into the appropriate section of your union query and it should work.

Thank you thank you thank you thank you!
I got it working now, thank you so very much!!!

Finally can go live! This is a solved case, thank you very much!