Duplicate Record issue with Training Database with Multiple tables & temporary tables

[JMANTN]

Hello, I'm hoping someone could help me out with a problem I've ran into while trying to create a training database. I'm just a beginner with Access and VBA so please take that into consideration.

The problem I'm having is the possibility of record duplication in my current database. I'm hoping maybe there's a way to set up a relationship or query/vba to prevent this.

I currently have a form (frmCE_AddAgent Training) which I use to select which line of business I want and then select a Team of agents that will be taking a new training class. This will then pull up a temporary table (tblAddTempB) which has a yes/no field so I can check those that will be able to attend. I can then close out of the pop-up temporary table and append those agents to my main table (tblCE_AgentTra ining).

Problem is I won't be the only person using this so there is a chance someone will add an agent that's already been added and then I'll have a duplicate record in my database.

I have three main tables:

tblHierarchy (this is from an ODBC and is updated daily so I don't add anything to this table)
-Agent_Name
-Agent_ID (Primary Key & the only field being copied to tblCE_AgentTrai ning)
-LineBusiness
-TeamLead
-Email
etc, about 50 fields on this one

tblCE_AgentTrai ning (Middle Man table if you will, gets data from Hierarchy and CEDetails)
-AGENT_ID
-Course_Number
-ClassID
-Registered
-Trainer
-Training_Locati on
-Completion_Date
-Date_Missed
-Date_Missed2
-Date_Missed3

tblCE_Details (list each training class details)
-Course_Number (Primary Key & the only field being copied to tblCE_AgentTrai ning)
-Course_Name
-Trainer
-StartDate
-EndDate
-LineBusinessSpe cific
-Duration
-Self_Paced

And my Temporary table which pulls in info from tblHierarchy and my form:

tblCE_AddTempB
-LeaveBlank (yes/no setup if someone is on vacation when during the time the class is offered)
-Missed (yes/no, I use this on a different part of form for after the fact and not used with append query)
-Agent_Name (So I'll know who I'm adding/skipping)
-TeamLead
-Course_Num (from my frmCE_AddAgentT raining)
-DateSelected (from my frmCE_AddAgentT raining)
-ClassID (from my frmCE_AddAgentT raining)
-Trainer (from my frmCE_AddAgentT raining)
-TrainingLocatio n (frmCE_AddAgent Training)


So each agent (AGENT_ID) can have multiple training (different Course_Number) and each Course_Number will have multiple agents. To add a team of agents into tblCE_AgentTrai ning I specify the class ID and course number /name from my frmCE_AddAgentT raining form and use a temporary table (tblCE_AddTempB ) to select the Agents that can attend and then append those AGENT_ID 's to the tblCE_AgentTrai ning.

So I'm thinking either my tables need work or I need better relationships or maybe some VBA that prior to appending when I close out the popup temporary table (tblCE_AddTempB ) and prior to saving will go through each record and make sure that AGENT_ID AND Course_Number do not match any existing AGENT_ID AND Course_Number on my final tblCE_AgentTrai ning. To me this seems like the best course at this point.

If I need to provide any additional info please let me know. I'd post a copy of my db but I'm getting ready to leave work and don't have time to wipe agents personal info right now.

[TheSmileyCoder]

There a couple of ways to deal with this. The easiest is to make the primary key (or a seperate index) consisting of both Agent_ID and Class_ID. Since that must now be unique, you will not be able to add another record with the same combination of Agent and Class. If I recall correctly you will get an error message saying something like "Could not append records, would create duplicate fields in index"

2nd solution: In code, step through each record, and check whether it exists allready, and then append if it doesnt.

[JMANTN]

Thank you very much TheSmileyOne as I had my AGENT_ID and Course_Number's indexed but not correctly and that fixed my issue! I've been stuck on that for weeks.

Since I'm still learning I think I'll try and find out how to use VBA to step through each record to check against two or more fields as well sounds like a challenge for a beginner :)

Thanks again!

[NeoPa]

Since I'm still learning I think I'll try and find out how to use VBA to step through each record to check against two or more fields as well sounds like a challenge for a beginner :)

Sounds like a very good idea :)

Welcome to Bytes!