What Type of Relationship?

[eyal8r]

ok- just re-read your response. So, 3 Queries- 1 for each of the positions. If Position 2 isn't null, then append the AgentCode into the Positions table, and mark the position field as '2' (or whatever). Same thing on the other 2 queries. Right?

So- do I include the position number in the Junction Table ALSO as a Primary Key, or no? Man, this is confusing for a newbie!

 

[eyal8r]

OR... would you just throw a Position field in the junction table and be done with it? That almost seems like the way to go- not a separate position table- but just a position field in the Junction table. Right?

 

[pbaldy]

It is confusing, but you've bitten off a fairly complicated application to start with. I wouldn't bother making the position field a key in the junction table, but I wouldn't argue against it either. Making it part of the key would eliminate the same person having 2 positions. Whether you have a table for positions and join it to the junction table is optional. I probably would if I thought the positions would change over time, wouldn't if they would stay the same. Since I assume each of the 3 fields in the import table represents a specific position, you can hard code that position into your 3 append queries.

Oops, wife is here with dinner. Gotta go!

 

[eyal8r]

Thanks a ton!!!!

 

[eyal8r]

ok- So I just realized that a Realtor CAN hold 2 positions at the same time in one record. So- if that's the case, I assume I need to take away the compound key, right? So what would the key be in the Junction Table?

 

[eyal8r]

ok- here is what I have now. I am getting really confused with how to write the update query for the Realtor's Information & Position- since it's happening in 3 different queries, and spawning across 4 tables.

Here's my guess- I update the tbAgentOffice info, tbAgent info, and the tbListings_Agents.Position field (this is the junction table). Look at my relationships diagram attached- when I am writing these 3 update queries to do all this- do I need to include the MLS, AgentCode, or AgentOfficeCode in them?

Basically- you'll have the Agent's Code come in in the flat file, in a specific position field. I need it to go out, look at the office information, the agent's contact information, and the position they are sitting in on this property- and update/add any information as needed in all the tables. Do I need to include the keys for each table in that process? I'm confused...
Thanks for all your help!

 

[pbaldy]

The person being able to hold 2 positions means you could add position as a third element of the compound key. As I mentioned, others would argue for a new autonumber field as the key.

I don't think your append queries span 4 tables; you're only appending to the junction table (though you certainly may have other processes that add to the agents table, the office table, etc). Your flat file has the MLS number and it has the agents code, so the only missing element is position. I assume that you know the position based on which of the 3 fields you're looking at. Since each of the 3 queries is designed to look at a specific field, you can hard code the position into each query.

It would really help to have a sample db including the raw import data.

 

[eyal8r]

ok- working on a sample db for you- my raw data is being a major PITA right now. So, I'm trying to figure out how to get it in here...

Well- the other data I am bringing in with each file is hte Agent's phone numbers, fax number, email, office code, office numbers, office fax, office name, etc etc. So- I want to update the corresponding tables/fields with all that data that comes in. That's where I'm getting confused.

 

[eyal8r]

ok- so here's what I have...

A flat file coming in with 3 fields in it- Agent1, Agent2, and Agent3. IF there is an agent occupying any of those positions, their employee code occupies that specific field. I have a junction table that holds the PropertyID (MLS) and AgentCode, as well as the position they represent on this specific property.

I am trying to update the Agent, AgentOffice tables, as well as the Position Field- but am completely stuck on how to write this query. I get that there will be 3 separate queries all together (1 for each position)- but how do I write the query? Please see the previous attached Relationships Image...
Please help!
Thanks!

 

[calvinshardy09]

Employee benefits including such as insurance, pension plans paid vacation, sick leave and disability insurance matters. Type of relationship, the connection object priority data resources.