By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

P: 8
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_AddAgentTraining) 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_AgentTraining).

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_AgentTraining)
-LineBusiness
-TeamLead
-Email
etc, about 50 fields on this one

tblCE_AgentTraining (Middle Man table if you will, gets data from Hierarchy and CEDetails)
-AGENT_ID
-Course_Number
-ClassID
-Registered
-Trainer
-Training_Location
-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_AgentTraining)
-Course_Name
-Trainer
-StartDate
-EndDate
-LineBusinessSpecific
-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_AddAgentTraining)
-DateSelected (from my frmCE_AddAgentTraining)
-ClassID (from my frmCE_AddAgentTraining)
-Trainer (from my frmCE_AddAgentTraining)
-TrainingLocation (frmCE_AddAgentTraining)


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_AgentTraining I specify the class ID and course number /name from my frmCE_AddAgentTraining 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_AgentTraining.

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_AgentTraining. 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.
Apr 18 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Apr 19 '10 #2

P: 8
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!
Apr 19 '10 #3

NeoPa
Expert Mod 15k+
P: 31,307
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!
Apr 20 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.