SQL Update Query Based On Values

[mrmmickle1]

I have two tables with the same fields. One is named PendingErrors and the other is named PendingImportBilling.

The fields are as follows:

Excel 2012

	A
1	Accession ID
2	Payor Name
3	Error Date
4	Error Code
5	Error Code Description
6	Patient First Name
7	Patient Last Name
8	Patient DOB
9	Patient Home Phone
10	Insured Home Phone
11	Client Billing Phone
12	Note
13	Client ID
14	Client Name
15	Subscriber ID
16	Insured SSN
17	Patient SSN
18	Patient Full Address
19	Responsible
20	Category
21	Type

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I want to update the PendingErrors Table with only records that do not already exist in it.

BEFORE EXAMPLES

ie. Pending Errors:
Excel 2012

	A	B
1	Accession ID	Payor Name
2	1111	AAA
3	22222	AAA
4	33333	AAA

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

PendingImportBilling:

Excel 2012

	A	B
8	Accession ID	Payor Name
9	1111	BBB
10	22222	BBB
11	33333	BBB
12	44444	BBB
13	55555	BBB
14	66666	BBB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Pendingerrors After SQL Query:

Excel 2012

	A	B
1	Accession ID	Payor Name
2	1111	AAA
3	22222	AAA
4	33333	AAA
5	44444	BBB
6	55555	BBB
7	66666	BBB

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I would like to insert only the records that do not currently have an Accession ID in Pending Errors. I would like the entire record (With ALL Fields) inserted into PendingErrors. Not just the Payor Name likein the simplified example.

If someone could help me out it would be much appreciated!

Thanks for looking at my issue.

 

[sheetspread]

How about write/create with the wizard a query that filters out the Accession ID, then update from that? You can compare the SQL and merge them into one.

 

[mrmmickle1]

I'm open to suggestions. Right now I have all of this written into an Excel File.

1. A User Picks Reference File 1
2. A User Picks Reference File 2
3. The Program Cleans/Filters/Manipulates the data in Excel to my database format
4. The Program Imports the Data into PendingImportBilling
5. Query 1 is run : Query to Delete Records from PendingErrors that are not in PendingImportBilling
6. Query 2 is run : Query that updates data that may have changed in Records that are in both Tables
7. This is where I am now.

 

[sheetspread]

INSERT INTO PendingErrors ( ID, AccessionID, PayorName )
SELECT PendingImportBilling.ID, PendingImportBilling.AccessionID, PendingImportBilling.PayorName
FROM PendingImportBilling;

worked on your sample (even though it gave me error messages), just an append query with the wizard and design view. It's not an update query, you're adding new records rather than changing what exists.

 

[mrmmickle1]

Sheetspread,

Thank you for your effort. I was able to build on your example to finish the query. I appreciate the guidance.

INSERT INTO PendingErrors
SELECT MYSELECT.*
FROM (SELECT [Accession ID], [Payor Name], [Error Date], [Error Code], [Error Code Description], [Patient First Name], [Patient Last Name], [Patient DOB], [Patient Home Phone], [Insured Home Phone], [Client Billing Phone], [Note], [Client ID], [Client Name], [Subscriber ID], [Insured SSN], [Patient SSN], [Patient Full Address], [Responsible], [Category], [Type], [Pending_Days] FROM PendingImportBilling WHERE [Pending_Days] <> 0)  AS MYSELECT LEFT JOIN PendingErrors ON MYSELECT.[Accession ID] = PendingErrors.[Accession ID]
WHERE (((PendingErrors.[Accession ID]) IS NULL));