Importing Multiple Fields into 1 Across tables? What am I not seeing?

[eyal8r]

Hey guys-
I am importing data on a daily basis- some of it contains existing records, some of those existing records have updated information, and then the rest will be new records. I am importing into a table, say, tbImport.

I have 3 tables I am splitting this info out to- tbListings, tbAgent, and tbAgentOffice. Each record can have up to 3 Sales Agents on the file- Agent 1, Agent 2, and Agent 3. Each Agent has their own Employee ID, as well as Office ID, Mobile, Home, Pager numbers. Each Agent can belong to only ONE Office, but every office can have multiple agents. I am using the Agent's Employee ID (it's assigned by another SQL db- and I will never be assigning this freely to anyone else, or new agents- they come into the DB already assigned a unique code).

Agent 1, 2 and 3 can freely change positions (Agent 1 can be Agent 3 at times, etc etc) from record to record. However, I want to store all their contact info into a separate table, going for 3rd Level Normalization...

So what I have is this-

tbImport
_____________________
fldInfoID
fldWhatever
fldAgent1Code
fldAgent1Name
fldAgent1Mobile
fldAgent1Pager
fldAgent1Home
fldAgent1OfficeName
fldAgent1OfficeCode
fldAgent1OfficeFax
fldAgent1OfficeNumber
fldAgent2Code
fldAgent2Name
fldAgent2Mobile
fldAgent2Pager
fldAgent2Home
fldAgent2OfficeName
fldAgent2OfficeCode
fldAgent2OfficeFax
fldAgent2OfficeNumber
fldAgent3Code
fldAgent3Name
fldAgent3Mobile
fldAgent3Pager
fldAgent3Home
fldAgent3OfficeName
fldAgent3OfficeCode
fldAgent3OfficeFax
fldAgent3OfficeNumber
**plus a ton of other data- but you get the idea here


AND I want it to go into tables like these 2-

tbAgent
________________
fldAgentCode (Primary Key)
fldAgentName
fldAgentMobile
fldAgentPager
fldAgentHome
fldAgentOfficeName
fldAgentOfficeCode
fldAgentOfficeFax
fldAgentOfficeNumber


tbAgentOffice
_________________
fldAgentOfficeCode (Primary Key)
fldAgentOfficeName
fldAgentOfficeNumber
fldAgentOfficeFax



So- the tbAgent table will store all the contact info for every agent, and tbAgentOffice stores info for their offices. I know I'll have relationships from tbListings.fldAgent1Code (as well as Agent 2 and 3 at the same time) to the tbAgent.fldAgentCode. I'll also have a relationship from tbAgent.fldAgentOfficeCode to tbAgentOffice.fldAgentOfficeCode. So, when I do daily imports- I will need to run an update query from the tbImport table into the other 3 tables at the same time. My question is this-
How do I cram the individual fields for all 3 agents, into one universal field in the tbAgent table at the same time? I can't get my mind around the concept here. How will it take all the fields for each agent in the tbImport table- and cram them into 1 record set (while checking for existing/update records) in the tbAgent table? How do I write that in the sql code to tell it to link Agent2Mobile into the same Record as Agent2Code, and not into the Agent1Code or Agent3Code? Right now it seems to me that there's no way to distinguish between the Mobile Number for Agent 1 2 and 3 compared to their own Codes to identify the records with. Does that make sense?

Any help you can provide is EXTREMELY appreciated!

 

[eyal8r]

ok- been thinking about this problem here. What I figure is that I'll have to do 3 separate update queries- 1 query for each Agent all going into the tbAgent table (as well as into the tbAgentOffice table concurrently). Is that right?