AnneYourPointIs
asked on
Compare Tables MS Access 2007
Good Afternoon All,
I have two tables in an access database. The first table is tblCAD and the second table is tblPCR. I am trying to compare the field in tblCAD.IncNum to tblPCR eIncNum. The data is coming from a website. TblCAD represents CAD dispatches from our local 9-1-1 center. TblPCR is coming from a second website for patient care reports. Each is stored in a seperate table in access. The common field is IncNum. The CAD data comes down with an incident number attached. As the EMT completes the report he also enters the incident number as eIncNum in the tblPCR table. I want to compare the two incident numbers. If the incident number is in tblPCR matches the incident number in tblCAD then I know the report was completed by the EMS provider.
I do not know if it is possible but I would also like to see the Incident Numbers in tblCAD that have no match in tblPCR. I want to see both completed reports and uncompleted reports.
the CAD incident number comes down as 2012-85. The employee may or may not use the 2012. There report may show an incident number of 85 or 2012-85. So I am looking for this to pick up either match.
Any help would be greatly appreciated,
Lisa
I have two tables in an access database. The first table is tblCAD and the second table is tblPCR. I am trying to compare the field in tblCAD.IncNum to tblPCR eIncNum. The data is coming from a website. TblCAD represents CAD dispatches from our local 9-1-1 center. TblPCR is coming from a second website for patient care reports. Each is stored in a seperate table in access. The common field is IncNum. The CAD data comes down with an incident number attached. As the EMT completes the report he also enters the incident number as eIncNum in the tblPCR table. I want to compare the two incident numbers. If the incident number is in tblPCR matches the incident number in tblCAD then I know the report was completed by the EMS provider.
I do not know if it is possible but I would also like to see the Incident Numbers in tblCAD that have no match in tblPCR. I want to see both completed reports and uncompleted reports.
the CAD incident number comes down as 2012-85. The employee may or may not use the 2012. There report may show an incident number of 85 or 2012-85. So I am looking for this to pick up either match.
Any help would be greatly appreciated,
Lisa
ASKER
I have tried this and it is not working for me. It produces a query full of bad data. The information in tblCAD does not match the information in tblPCR.
AnneYourPointIs,
<see the Incident Numbers in tblCAD that have no match in tblPCR.>
Try the "unmatched" query wizard in Access:
Create-->Query Wizard-->Find Unmatched....
<see the Incident Numbers in tblCAD that have no match in tblPCR.>
Try the "unmatched" query wizard in Access:
Create-->Query Wizard-->Find Unmatched....
I obtained a query similar to the following from the EE site in the past.
I modified it to match your table names (and included an ID field):
Data in tblCAD that have no records in tblPCR:
SELECT tblCAD.ID, tblCAD.incnum
FROM tblCAD
LEFT JOIN tblPCR ON tblCAD.incnum = tblPCR.eIncNum
WHERE (tblPCR.eIncNum) Is Null;
========================== ======
adding the NOT keyword shows data in tblCAD with matching records in tblPCR
SELECT tblCAD.ID, tblCAD.incnum
FROM tblCAD
LEFT JOIN tblPCR ON tblCAD.incnum = tblPCR.eIncNum
WHERE (tblPCR.eIncNum) Is Not Null;
========================== =========
To remove the year from your Inc number, you may have to build a query to Trim that value away. Here's an example:
WithoutYear: Trim(Right([incNum],Len([I ncNum])-In Str([IncNu m],"-")))
I modified it to match your table names (and included an ID field):
Data in tblCAD that have no records in tblPCR:
SELECT tblCAD.ID, tblCAD.incnum
FROM tblCAD
LEFT JOIN tblPCR ON tblCAD.incnum = tblPCR.eIncNum
WHERE (tblPCR.eIncNum) Is Null;
==========================
adding the NOT keyword shows data in tblCAD with matching records in tblPCR
SELECT tblCAD.ID, tblCAD.incnum
FROM tblCAD
LEFT JOIN tblPCR ON tblCAD.incnum = tblPCR.eIncNum
WHERE (tblPCR.eIncNum) Is Not Null;
==========================
To remove the year from your Inc number, you may have to build a query to Trim that value away. Here's an example:
WithoutYear: Trim(Right([incNum],Len([I
ASKER
I created two queries and applied your suggestion of Trim(right(. I applied the two new queries to your suggestion above replacing the queries for the tables. I get a data type mismatch. It works fine for the tables but trimming the incident numbers down so they match on both sides and then running the query is where I get the error. Here is what I have:
SELECT qryCADTrimIncNum.cadID, qryCADTrimIncNum.NewCADInc Num, qryCADTrimIncNum.Unit, qryPCRTrimIncNum.eCompany, qryCADTrimIncNum.IncDate
FROM qryCADTrimIncNum LEFT JOIN qryPCRTrimIncNum ON qryCADTrimIncNum.NewCADInc Num = qryPCRTrimIncNum.NewPCRinc Num
WHERE (((qryPCRTrimIncNum.NewPCR incNum) Is Null));
SELECT qryCADTrimIncNum.cadID, qryCADTrimIncNum.NewCADInc
FROM qryCADTrimIncNum LEFT JOIN qryPCRTrimIncNum ON qryCADTrimIncNum.NewCADInc
WHERE (((qryPCRTrimIncNum.NewPCR
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An additional point .... I'm assuming your 'mismatch' error message is due to data existing in one table and not in the other, yet that's what you hoping to accomplish. So using different syntax seems to be the way to go.
Someone else here on EE with a greater understanding may be able to clarify, but that's the only reason I can see for the error.
Someone else here on EE with a greater understanding may be able to clarify, but that's the only reason I can see for the error.
^
correct.
AnneYourPointIs,
Perhaps if you posted a sample of the database that exhibits this issue, it would be easier...
correct.
AnneYourPointIs,
Perhaps if you posted a sample of the database that exhibits this issue, it would be easier...
ASKER
Ok Dove,
You have put me on the right track, and it appears that the below query is offering the data that I was looking for. Thanks for your help!
SELECT tblCAD1.CADID, tblCAD1.IncDate, tblCAD1.CADIncNum, tblPCR1.ePCRNum, tblCAD1.IncAddress, tblCAD1.Unit, tblPCR1.eCompany, tblCAD1.Response, tblCAD1.PSAP, tblCAD1.Disp, tblCAD1.Enr, tblCAD1.Arr, tblCAD1.EnrHosp, tblCAD1.ArrHosp, tblCAD1.Clear
FROM tblCAD1 LEFT JOIN tblPCR1 ON tblCAD1.CADIncNum = tblPCR1.PCRIncNum
WHERE (((tblCAD1.IncDate)>=#2/1/ 2012#) AND (Not (tblCAD1.ArrHosp) Is Null));
You have put me on the right track, and it appears that the below query is offering the data that I was looking for. Thanks for your help!
SELECT tblCAD1.CADID, tblCAD1.IncDate, tblCAD1.CADIncNum, tblPCR1.ePCRNum, tblCAD1.IncAddress, tblCAD1.Unit, tblPCR1.eCompany, tblCAD1.Response, tblCAD1.PSAP, tblCAD1.Disp, tblCAD1.Enr, tblCAD1.Arr, tblCAD1.EnrHosp, tblCAD1.ArrHosp, tblCAD1.Clear
FROM tblCAD1 LEFT JOIN tblPCR1 ON tblCAD1.CADIncNum = tblPCR1.PCRIncNum
WHERE (((tblCAD1.IncDate)>=#2/1/
Excellent,
Cheers
Cheers
http://office.microsoft.com/en-us/access-help/compare-two-tables-and-find-records-without-matches-HA010205132.aspx