Help! Complex Query w/ Junction Table (1 Viewer)

[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!!!

 

[eyal8r]

ok- just so that I am clear...
I need to take the Agent's Info from the tbIMport table- and Update it into the tbAgent table, as well as their position they hold on each property (which is held in the tbListings_Agents table).
Thanks!

 

[neileg]

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.

Seems fine so far!

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.

Looking good.

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!!!

You'll need three queries to extract the three positions. You could union these together but there's unlikely to be any benefit. It would be useful to know what and where the key errors occur. The compound key you've applied to tblListings_Agents should prevent any duplicates.

 

[eyal8r]

ok- what I'm confused with is how exactly to write the queries? Do I update the junction table itself as well as the Agent table? In the junction table, how do I tell it to write the Agent Code specific to the position it's in? I can write the update query into the Agent table based off of ListingAgent's information- but how do I write the code to tell the Junction Table what position the agent is in on which property?

I wrote one query to update the Agent's table with the Listing Agent's info- and that went fine. When I wrote the next query to update the Agent's Table with the Co-Listing Agent's info- I got tons of key errors. So, I'm completely lost on this part and not sure how to go about doing it. Can you help?

 

[eyal8r]

Or, does this REALLY need to be split up like this at all? Couldn't I just keep the Agent's Code in the position field it's in now- and then split out all their contact info into a different table, and still be fairly well normalized? What's the potential problems I could incur if I did it this way?
Thanks!!!

 

[neileg]

Don't let one small problem stand in the way of normalisation!

I assume there's 3 fields in tblImport that hold the agent reference for the 3 different positions. So your first query needs to select the MLS, the agent code for position 1, and a constant field that holds whatever you want to indicate the position. The SQL would look something like this:
INSERT INTO ...
SELECT tblImport.LN2 as MLS, Position1AgentCode as AgentCode, "1" as [Position]

Then the second one would look like:
INSERT INTO ...
SELECT tblImport.LN2 as MLS, Position2AgentCode as AgentCode, "2" as [Position]

Does this make sense?

 

[eyal8r]

Yeah, I'll give that a shot and post back!
Thanks!

 

[eyal8r]

Well wait- does that insert into the Junction table? What about the Agent table? Does that have a separate update query other than this one? I assume so... but does that mean I am inserting the MLS, Agent Code & Position into the Junction Table, AS WELL AS the AgentCode into the Agent Table (along with all the agent's contact info?) So I'm actually inserting the information into the Junction table, and I'm inserting the AgentCode specifically, twice (Junction and Agent tables)?

Also- won't I be needing an UPDATE Query instead of an insert? The same records will be getting imported over and over- so I need an update to add in the new ones, or make changes on the old ones... Right? So how does that look using an update query?

Just trying to understand...
Thanks!

 

[eyal8r]

ok- here's what I have...
UPDATE tbImport LEFT JOIN tbListings_Agents ON (tbImport.LN2 = tbListings_Agents.MLS) AND (tbImport.LA1 = tbListings_Agents.AgentCode) SET tbListings_Agents.MLS = tbImport.LN2, tbListings_Agents.AgentCode = tbImport.LA1, tbListings_Agents.[Position] = "ListingAgent";

And I get a Key Violation for every single record (28,000+ of them). tbListings_Agents is my Junction Table, where MLS, AgentCode and Position are all 3 keys.
Any ideas?

 

[eyal8r]

ok- Since the Agent Table and the Junction Table are both empty- I figured I need to populate the Agent Table FIRST, before the Junction Table (since, there has to be an Agent in the Agent Table to link to the Junction Table). Right? So- I did this-

UPDATE tbImport LEFT JOIN tbAgent ON tbImport.LA1 = tbAgent.AgentCode SET tbAgent.AgentCode = tbImport.LA1, tbAgent.AgentName = tbImport.T_FULLNAME, tbAgent.AgentEmail = tbImport.GENT1EMAIL, tbAgent.AgentOfficeCode = tbImport.LO, tbAgent.AgentOfficeName = tbImport.OFFICENAME;

I get key violation errors on 19,000 of the 28,000 records. I am guessing that the 19,000 are unique records, where 9,000 of them are repeats (or the other way around). What's going on here? Is this problem related to my OTHER Key Violation Errors above?

 

[neileg]

Hang on a minute! This is the first you mentioned of the same data being imported! Let's take a step or two back.

An update query changes records that already exist. An append query adds new records. They are two different queries and you can't do both at the same time.

Now bear in mind that you are familiar with the data and I only know what you tell me! All of your table need to be populated and you need to do them in turn. Which order you do them in depends on what is most appropriate but I can't know that.

tblAgents needs to hold all of the information on all of the agents but only once. As AgentCode is your PK, trying to append records with the same AgentCode will generate a key violation. You have a compound key on tblListings_Agents so any records that have the same combination of these 3 fields will cause a key violation.

Now tell me some more about what really exists in tblImport and what you are trying to do.

 

[neileg]

ok- Since the Agent Table and the Junction Table are both empty- I figured I need to populate the Agent Table FIRST, before the Junction Table (since, there has to be an Agent in the Agent Table to link to the Junction Table). Right? So- I did this-

UPDATE tbImport LEFT JOIN tbAgent ON tbImport.LA1 = tbAgent.AgentCode SET tbAgent.AgentCode = tbImport.LA1, tbAgent.AgentName = tbImport.T_FULLNAME, tbAgent.AgentEmail = tbImport.GENT1EMAIL, tbAgent.AgentOfficeCode = tbImport.LO, tbAgent.AgentOfficeName = tbImport.OFFICENAME;

I get key violation errors on 19,000 of the 28,000 records. I am guessing that the 19,000 are unique records, where 9,000 of them are repeats (or the other way around). What's going on here? Is this problem related to my OTHER Key Violation Errors above?

You can't update a field that you are using to join the two tables.

If tblAgent is empty, you can't update, only append.

If you have duplicates then there must be duplicates in LA1.

 

[eyal8r]

Neil-
You're right, and wrong I believe. According to this document here-
http://support.microsoft.com/default.aspx?scid=kb;en-us;127977

And the posting about it here-
http://www.access-programmers.co.uk/forums/showthread.php?t=91911

It states you CAN update and append in one query. But nonetheless-

I don't have a 'Master List' of all the agents and their info. I am gathering it from the import file I have. Even then, I only am collecting their info as it comes in on the import files every day. So- the import file contains all of yesterday's records (with any changes to those included) as well as new records for today. So- yes, there will be repeats in there, which is why I'm looking at an UPDATE query instead of append. From my understanding, the UPDATE query will recognize if the record already exists off of the key field (so say AgentCode in the tbAgents table). It will then update any changes to that record (say, email address). If the record DOESN'T exist yet, it will add it as a new record all together. This is what I'm understanding from those links above.

So- here's what I've figured out thus far- since my tbAgents table was empty- it was giving me a key error on every record. I went ahead and ignored the key errors the first time- it populated about 8,000 records in the Agents table. I ran the query a 2nd time- no key errors at all. Same experience on the CoListing Agent's query. I also tried it in the Junction Table- and it seems to be the same thing- first time populating, gives errors on every record. Once populated with at least 1 record in there, it runs perfectly fine. I don't know why it does htis, but...

I'm going to run some simple queries to see if it DID get them all, and if there's any repeats... brb.

 

[eyal8r]

Yeah, this just isn't working right. Any ideas on what I'm doing wrong? I'm about to throw in the towel after 4 days of attempting normalization- and just keep it non-normalized... Heeeeelllllllllpppppp....

 

[neileg]

As far as the update and append goes, it just shows I have plenty to learn!

Now tell me some more about what really exists in tblImport and what you are trying to do.

You haven't answered this question.

I'm beginning to wonder if, since you are importing the data each day, are you changing anything in your Access db or are you just using it for reporting? There's an argument for leaving the data in the flat file, or alternatively, achieving normalisation by using queries instead of storing the data in tables

 

[eyal8r]

Yeah- all the data comes in daily in a flat file. I import that into a temp table, then move it over to the other tables for normalization (well, trying to anyways). As said before- the records contain previous records, changed records, and new records, all in the same file. I won't be doing much with data, except queries, calculations, matching/searching. I may add a few columns for notes and my own operational uses- but the core of the data will remain untouched for the most part. I could never get this junction table to work right. The append/update feature isn't working quite either- everytime I run new info- I get key violations on the NEW records only. I ignore them, and they import right on in the way they're supposed to. I don't get it. I tried doing separate append/update queries- had key violation errors. So, at this point, I'm just going to keep moving forward, since nothing I try is working out right.