Solved Append (unique) records to table without creating duplicates (1 Viewer)

abette

Member
Local time
Today, 10:17
Joined
Feb 27, 2021
Messages
85
Hi - This is the last piece of this project I have been working on. I have a Form that allows user to attest to reviewing records by marking each checkbox control and then clicking an Attest button. This will open an Attestation form which allows the user to "attest" to completing the review. This button runs an embedded macro that is attached to the button's On Click event property. So it basically append the records being attested from a Temp table to the 'Real" table and then deletes the records from the Temp table.

1625155980045.png
\
Macro
1625156078454.png

Here's my issue: How do I prevent records that were previously attested from being appended to the table again, thus creating duplicates? I know normally the user will be processing a week's worth of data based on date range but say they attest this week's data and then do it again for some reason. How can I prevent the append from running if the records are already on the "real" table? My Temp table is a duplicate design of the "real" table. It's just used to process records imported that will be reviewed and then once attested, moved to the "real" table.

Here's the SQL view of my query qryAppendAttestedRecsFromTEMP:

INSERT INTO tblClaimsWorkingTable ( ClaimNumber, AuthNumber, DateOfServiceFrom, DateOfServiceTo, ProviderCIMID, PayeeName, FEIN_SSN, DateReceived, DateProcessed, MemberID, YouthName, DxCode, DxCode2, DxCode3, DxCode4, ServiceCode, LineNumber, UnitsRequested, AmountBilled, ClaimStatus, DenialReasonComments, ProcessedBy, ClaimsFundYear, ClaimsFundYear2, ClaimsFundType, AttestationCheck, AttestationName, AttestationDate )
SELECT tblClaimsWorkingTableTEMP.ClaimNumber, tblClaimsWorkingTableTEMP.AuthNumber, tblClaimsWorkingTableTEMP.DateOfServiceFrom, tblClaimsWorkingTableTEMP.DateOfServiceTo, tblClaimsWorkingTableTEMP.ProviderCIMID, tblClaimsWorkingTableTEMP.PayeeName, tblClaimsWorkingTableTEMP.FEIN_SSN, tblClaimsWorkingTableTEMP.DateReceived, tblClaimsWorkingTableTEMP.DateProcessed, tblClaimsWorkingTableTEMP.MemberID, tblClaimsWorkingTableTEMP.YouthName, tblClaimsWorkingTableTEMP.DxCode, tblClaimsWorkingTableTEMP.DxCode2, tblClaimsWorkingTableTEMP.DxCode3, tblClaimsWorkingTableTEMP.DxCode4, tblClaimsWorkingTableTEMP.ServiceCode, tblClaimsWorkingTableTEMP.LineNumber, tblClaimsWorkingTableTEMP.UnitsRequested, tblClaimsWorkingTableTEMP.AmountBilled, tblClaimsWorkingTableTEMP.ClaimStatus, tblClaimsWorkingTableTEMP.DenialReasonComments, tblClaimsWorkingTableTEMP.ProcessedBy, tblClaimsWorkingTableTEMP.ClaimsFundYear, tblClaimsWorkingTableTEMP.ClaimsFundYear2, tblClaimsWorkingTableTEMP.ClaimsFundType, tblClaimsWorkingTableTEMP.AttestationCheck, tblClaimsWorkingTableTEMP.AttestationName, tblClaimsWorkingTableTEMP.AttestationDate
FROM tblClaimsWorkingTableTEMP
WHERE (((tblClaimsWorkingTableTEMP.AttestationCheck)=True));

I tried using criteria for most of the fields based on the value of the field on the "real" table but then when it executed is displayed the information as prompts.
Thank you for your assistance and patience with me.
Ann Marie
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:17
Joined
Oct 29, 2018
Messages
21,358
Here's my issue: How do I prevent records that were previously attested from being appended to the table again, thus creating duplicates?
Hi. Why not prevent those already attested records from being added to the temp table in the first place, so that the user doesn't have to attest to something that someone already attested to? Just curious...
 

abette

Member
Local time
Today, 10:17
Joined
Feb 27, 2021
Messages
85
Hi - So you are saying in the Import process when I am bringing the recs in from the SQL table check then? Yes, for sure that would make sense.

Here's the SQL version of my query that imports recs from the SQL table:
INSERT INTO tblClaimsWorkingTableTEMP ( ClaimNumber, AuthNumber, DateOfServiceFrom, DateOfServiceTo, ProviderCIMID, PayeeName, FEIN_SSN, DateReceived, MemberID, YouthName, DxCode, DxCode2, DxCode3, DxCode4, ServiceCode, ServiceDescription, LineNumber, UnitsRequested, AmountBilled, ClaimStatus, DenialReasonComments, ProcessedBy, DateProcessed, ClaimsFundYear2, ClaimsFundYear, ClaimsFundType )
SELECT dbo_tblClaimsAttestation.claimsId, dbo_tblClaimsAttestation.AuthNumber, dbo_tblClaimsAttestation.ServiceDateFrom, dbo_tblClaimsAttestation.ServiceDateTo, dbo_tblClaimsAttestation.CIMID, dbo_tblClaimsAttestation.ProviderName, dbo_tblClaimsAttestation.FEIN, dbo_tblClaimsAttestation.DateReceived, dbo_tblClaimsAttestation.MemberID, dbo_tblClaimsAttestation.YouthName, dbo_tblClaimsAttestation.DxCode, dbo_tblClaimsAttestation.DxCode2, dbo_tblClaimsAttestation.DxCode3, dbo_tblClaimsAttestation.DxCode4, dbo_tblClaimsAttestation.ServiceCode, dbo_tblClaimsAttestation.ServiceDesciption, dbo_tblClaimsAttestation.Line, dbo_tblClaimsAttestation.UnitsReq, dbo_tblClaimsAttestation.AmountBilled, dbo_tblClaimsAttestation.ClaimStatus, dbo_tblClaimsAttestation.ClaimStatusReason, dbo_tblClaimsAttestation.Prosessedby, dbo_tblClaimsAttestation.UpdateDate, dbo_tblClaimsAttestation.ClaimsfundYear, "FY"+Left([ClaimsFundYear2],2) AS ClaimsFundYearNew, dbo_tblClaimsAttestation.ClaimsFundType
FROM dbo_tblClaimsAttestation
WHERE (((dbo_tblClaimsAttestation.ClaimStatus)="Approved" Or (dbo_tblClaimsAttestation.ClaimStatus)="Returned" Or (dbo_tblClaimsAttestation.ClaimStatus)="Denied") AND ((dbo_tblClaimsAttestation.UpdateDate) Between [Forms]![frmImportClaimsFromCYBER]![StartDateRange] And [Forms]![frmImportClaimsFromCYBER]![EndDateRange]));

I have a form that requires the user to enter a date range and click a button. When the button is clicked, an event procedure (see vba code below) runs that executes a delete query first and then the append query.
1625158129697.png
 

abette

Member
Local time
Today, 10:17
Joined
Feb 27, 2021
Messages
85
BTW, I do not take credit for the VBA code. A nice gentleman on this forum helped me this morning with it!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:17
Joined
Jul 9, 2003
Messages
16,245
1) - Put an index on the receiving table to not allow duplicates.

(Problem - the user is not informed of the error)

2) - Query the receiving table on the data you want to add. Count the number of records returned, if greater than zero, inform the user of the error. If zero, add the record and advise the user.
 

abette

Member
Local time
Today, 10:17
Joined
Feb 27, 2021
Messages
85
Hi Uncle Gizmo - Would you please break down the English into VBA for me?
And you mean to set the 'Indexed' property on the receiving table for each field that should be unique?
1625158672835.png
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:17
Joined
Jul 9, 2003
Messages
16,245
Which method do you want to use?
 

abette

Member
Local time
Today, 10:17
Joined
Feb 27, 2021
Messages
85
Do you mean VBA vs. Macro? I don't understand? I need assistance with VBA coding of this. Thank you!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:17
Joined
Jul 9, 2003
Messages
16,245
Do you mean VBA vs. Macro? I don't understand? I need assistance with VBA coding of this. Thank you!

The two choices I suggested are:-

1) - Put an index on the receiving table to not allow duplicates.

(Problem - the user is not informed of the error)

2) - Query the receiving table on the data you want to add. Count the number of records returned, if greater than zero, inform the user of the error. If zero, add the record and advise the user.
 

Users who are viewing this thread

Top Bottom