Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Compare two tables in MS Access
Hi,
I have 3 tables in my project:
tblBatches
tblReferenceData
tblJobData
A Batch is entered into the tblBatches table and then individual jobs associated with the Batch is entered into tblReferenceData. They are both joined accordingly. When the job gets completed all the data associated with the job gets automatically added to the tblJobData table (via an external source)
To ensure the Batch has been completed, I need to check the data in the tblJobData table against the data in tblReferenceData to check that there is a corresponding entry in the tblJobData table for each entry in tblReferenceData for the Batch reference.
If there is a record in tblReferenceData but there isn't a corresponding entry (with the same REF ID) in tblJobData the user needs to be informed of what Job is missing.
Hope I have explained this ok.
I have 3 tables in my project:
tblBatches
tblReferenceData
tblJobData
A Batch is entered into the tblBatches table and then individual jobs associated with the Batch is entered into tblReferenceData. They are both joined accordingly. When the job gets completed all the data associated with the job gets automatically added to the tblJobData table (via an external source)
To ensure the Batch has been completed, I need to check the data in the tblJobData table against the data in tblReferenceData to check that there is a corresponding entry in the tblJobData table for each entry in tblReferenceData for the Batch reference.
If there is a record in tblReferenceData but there isn't a corresponding entry (with the same REF ID) in tblJobData the user needs to be informed of what Job is missing.
Hope I have explained this ok.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Not sure of your design there...(without seeing the associated fields...)
For example, ...it is not clear how tblJobData is related to the other two tables.
Most techniques to do this revolve around simply creating a "Completed" (yes/no) calculated field in a query.
This avoids all the complex "compare" code needed to validate across tables.
It also eliminates the need for the second table altogether.
In this design, if the number of start an end dates are the same, the task is "Complete"
For example:
tblJobs
jID (PK)
jCustID
jNotes
jComplete (calculated in a query)
tblJobDetails
jdID (PK)
jd_jID (FK)
jdName
jdCost
jdStartDate
jEndDate
A very basic sample is attached...
JeffCoachman
Database108.mdb
For example, ...it is not clear how tblJobData is related to the other two tables.
Most techniques to do this revolve around simply creating a "Completed" (yes/no) calculated field in a query.
This avoids all the complex "compare" code needed to validate across tables.
It also eliminates the need for the second table altogether.
In this design, if the number of start an end dates are the same, the task is "Complete"
For example:
tblJobs
jID (PK)
jCustID
jNotes
jComplete (calculated in a query)
tblJobDetails
jdID (PK)
jd_jID (FK)
jdName
jdCost
jdStartDate
jEndDate
A very basic sample is attached...
JeffCoachman
Database108.mdb
Hi Jeff,
Thanks for the attached examples. I'll show my code which I'm currently working on when I get into the office tomorrow. I have to use a separate table to store the JobData (tblJobData) as this data is supplied to the database from an external source which is automated (in simple terms, it dumps the data into the said table). By using the data which it dumps I can then see if a certain job has been completed (if the tblJobData has JobID = 10 then Job ID = 10 which was created in tblReferenceData must have been completed.
I started to write a simple 'show unmatched data' query but I think I have confused the WHERE clause somewhat. I'll post tomorrow with an update.
Thank you for your time - very much appreciated.
Thanks for the attached examples. I'll show my code which I'm currently working on when I get into the office tomorrow. I have to use a separate table to store the JobData (tblJobData) as this data is supplied to the database from an external source which is automated (in simple terms, it dumps the data into the said table). By using the data which it dumps I can then see if a certain job has been completed (if the tblJobData has JobID = 10 then Job ID = 10 which was created in tblReferenceData must have been completed.
I started to write a simple 'show unmatched data' query but I think I have confused the WHERE clause somewhat. I'll post tomorrow with an update.
Thank you for your time - very much appreciated.
Hi,
As promised here is the code I am using:
I think the:
WHERE (((tbl_scan_data.[Referenc
is incorrect.
I need to add the filter on BatchID as well. The ID is on the form where the button is pressed (me.id).
Anthony
As promised here is the code I am using:
Private Sub JobComplete_Click()
Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
'Define SQL Query
strSQL = "SELECT qryReferenceData.tbl_batches.id, qryReferenceData.customer_id, qryReferenceData.batch_number, qryReferenceData.tbl_batches.batch_reference, qryReferenceData.batch_output_location, qryReferenceData.create_date, qryReferenceData.tbl_reference_data.id, qryReferenceData.tbl_reference_data.batch_reference, qryReferenceData.reference_1, qryReferenceData.reference_2, qryReferenceData.reference_3, qryReferenceData.reference_4, qryReferenceData.reference_5 FROM qryReferenceData LEFT JOIN tbl_scan_data ON qryReferenceData.[tbl_reference_data].[id] = tbl_scan_data.[Reference ID] WHERE (((tbl_scan_data.[Reference ID]) Is Null)) AND tbl_scan_data.[Batch ID] = Me.id;"
Set rs = db.OpenRecordset(strSQL)
'Return Result in Loop until end of results
Do While Not rs.EOF
'Insert code here to list elements
'We will loop through and popup a message box
'for this example
MsgBox "Job: " & rs.Fields(0) & " " & rs.Fields(1) & ": " & rs.Fields(2)
'This would pop a message box for each loop
'and display each Job called from the
'SQL query
'move to the next record for the next loop
rs.MoveNext
Loop
'Close the Database
db.Close
Set db = Nothing
Set rs = Nothing
End Sub
I think the:
WHERE (((tbl_scan_data.[Referenc
is incorrect.
I need to add the filter on BatchID as well. The ID is on the form where the button is pressed (me.id).
Anthony
Just thinking over this again.
Would it be better to have the code look for unmatched records and then notify the user if there are any and then allow the user to print out a report of the unmatched records instead of having them displayed in a MsgBox?
The MsgBox could read:
"There are X number of unmatched items. Do you wish to print a report of these Jobs?"
Would it be better to have the code look for unmatched records and then notify the user if there are any and then allow the user to print out a report of the unmatched records instead of having them displayed in a MsgBox?
The MsgBox could read:
"There are X number of unmatched items. Do you wish to print a report of these Jobs?"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial