Help! Complex Query w/ Junction Table?

[eyal8r]

ok- I'm REALLY stuck on a complex Query using a Junction Table. Here's what I have-

A flat file being imported (tbImport), with a unique property ID (MLS), information on 3 different agents, all in different fields. I am trying to normallize the DB by assigning every Agent to a separate table (away from all the other data). Every Agent has their own Employee ID (AgentCode), as well as Email Address, OfficeCode, Name and Office Name. There are 3 positions available to fill on every property- Listing Agent, CoListing Agent, and Buyer's Agent. It is possible for 1 Agent to fill 2 spots on a property (record). It is also possible for an Agent to fill DIFFERENT spots on different properties or records. So For example- on one property, Bob Smith can be both the Listing Agent, and the Buyer's Agent. Bob can also be the Co-Listing Agent on a different property. Bob can also be the Listing Agent on many other properties as well.

So- what I have is a Junction Table for the Property ID (called MLS). It also Contains the AgentCode and Position as Primary Keys. Basically the position is figured by which field in the import flat file their AgentCode shows up in. So- if CR123 shows up in the Agent1 Field- I know that is Bob Smith's Code, and that he is the Listing Agent on this property.

So I have 4 tables- tbImport- this is the flat file that is not normallized yet, and I will be updating the other tables with this info; tbListing, with a Primary Key of MLS (Number, not AutoNumber).
tbAgent, with a PK of AgentCode (again, pre-assigned not AutoNumber). This table holds all the agent's Contact/Office info. And the 3rd Table is the Junction Table- with a 1-to-Many Relationship between it and the tbListing Table, with MLS as the Foreign Key, and another 1-to-many relationship setup with the tbAgent Table, and a FK of the AgentCode.

Please see the attached image of my Relationship Diagram.

I am trying to import the records from tbImport into tbAgent & tbListings_Agents 'Position' field. I already have a query updating the tbListing table with all the other info for the Property- I now need to update/append the info into the tbAgents and Junction table.

I have been trying everything- and only ending up with Key Errors. I think I'll need to make 3 separate passes at updating the tbAgents & tbListings_Agents tables, as there are 3 different positions available. I'm a newbie to all this, so this is REALLY over my head! I am also up against a VERY tough deadline (already over) and need help ASAP. Can someone show me how to do this here?
THANK YOU!!!

ok- how do I post up a picture of my Relationships? I don't see a way to post it here.

 

[eyal8r]

ok- let's try this link and see if it will show the picture...

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=18665&d=1189474533

OR THIS-

http://tinyurl.com/2hkquf

 

[lespaul]

In your previous posting: thread181-1406003 I asked about the "meaning" of the three types of realtors...if you had responded with this information:

There are 3 positions available to fill on every property- Listing Agent, CoListing Agent, and Buyer's Agent.

I would have responded with:

Since each position is a different attribute of the property listing, you probably should NOT have a separate Junction table for the realtors assigned to a property!

Have you read the fundamentals document linked below? It has some basic techniques for helping to determine what should be an entity and what information is an attribute of that entity.

I would have to say that your entity "Property" has three attributes of:
ListingAgent
CoListAgent (may be null)
BuyersAgent

no need for a junction table.

Just my 2¢



Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[eyal8r]

Leslie-
I apologize for not responding previously- I must've missed it somehow... Huh- you raise an interesting point. Yes, I've read those documents- but I was under the impression that this is part of normalizing the database. I figure that since I will be repeating the realtor info over and over again- it will need to be normalized and split out to it's own table. Likewise if I include all the office contact info (which, for now I have excluded from this to make it easier for ME to understand). Is this not the case? Again, thanks for the reply.

 

[lespaul]

It depends on the situation. Let's say you have a property and you want to record all the agents that want to show the property. This would be a proper use of a junction table. You have properties, you have agents and you have agents showing a property:

Property
PropertyID
Address
City
etc. (all information about the PROPERTY)

Agents
AgentID
AgentName

ShowingAgents
PropertyID
AgentID

Your situtation is: You have a property listing. Each property has an Agent. It may have a CoAgent and it has a BuyerAgent. Those are all unique attributes of the PROPERTY. Now, can you have a junction table? Sure, you can...does it break the rules of normalization NOT to have one, NOT in this case.



Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[eyal8r]

huh. So- if I ran a query on that property table- and one agent has 20 properties, a buyer's agent has 45 properties, and a specific office has 230+ Agents all represented in the table... won't you see the Agent's contact info repeated x amount of times, over and over- as well as the Office info repeated over and over. Doesn't that mean it needs to be normalized? Won't it slow down the DB NOT normalizing it? I'm just trying to understand...
Thanks!

 

[eyal8r]

Well, now you have me thinking about it... I guess I COULD break out the Agent's contact info (office, phone, email, etc) into a separate table- then leave their AgentCode in the specific position in the Property Table... So, no need to a junction table at all doing it this way. Right? Man, this would've saved me 2 days if I would've just K.I.S.S.! Am I on the right track?