Hi all:
I have the following table structure:
Address
City
State
Zip
Close Date
Close Price
LID
LA First Name
LA Last Name
SID
SA First Name
SA Last Name
County
The above table reflects the transactions conducted by our agents. Every transaction has two sides, the listing side represented by the LID and the buying side represented by the SID. The field LID is unique to each agent and refers to the Listing Agent and the field SID refers to the buying agent.
Here is what I am trying to get. I am trying to get a result that sums up the total transaction value for all agents. However, here are some rules:
1.) The totals need to be grouped by county and state.
2.) If a house address appears more than once, only the first occurence needs to be taken into consideration.
3.) For every transaction that has two agents i.e. the LID and SID are different for that address, the transaction needs to be counted once for each of those agents.
4.) For every transaction that has the same agent representing both sides of the transaction i.e. the LID and SID are the same, the transaction needs to be counted only once for that agent and not twice.
5.) Lastly, though it is not necessary, it would be nice to have a count. E.g. if LAID 100 had 10 transactions for $2,000,000 then it would be good to know the breakdown for the 10 transactions i.e. 3 listing, 3 buying and 4 double ended transactions.
I hope the query itself is clear. Please let me know if you need any more information. If this cannot be done by one query, it would really help if you can point in the direction I need to proceed.
Thanks for any help.
Ram.
I have the following table structure:
Address
City
State
Zip
Close Date
Close Price
LID
LA First Name
LA Last Name
SID
SA First Name
SA Last Name
County
The above table reflects the transactions conducted by our agents. Every transaction has two sides, the listing side represented by the LID and the buying side represented by the SID. The field LID is unique to each agent and refers to the Listing Agent and the field SID refers to the buying agent.
Here is what I am trying to get. I am trying to get a result that sums up the total transaction value for all agents. However, here are some rules:
1.) The totals need to be grouped by county and state.
2.) If a house address appears more than once, only the first occurence needs to be taken into consideration.
3.) For every transaction that has two agents i.e. the LID and SID are different for that address, the transaction needs to be counted once for each of those agents.
4.) For every transaction that has the same agent representing both sides of the transaction i.e. the LID and SID are the same, the transaction needs to be counted only once for that agent and not twice.
5.) Lastly, though it is not necessary, it would be nice to have a count. E.g. if LAID 100 had 10 transactions for $2,000,000 then it would be good to know the breakdown for the 10 transactions i.e. 3 listing, 3 buying and 4 double ended transactions.
I hope the query itself is clear. Please let me know if you need any more information. If this cannot be done by one query, it would really help if you can point in the direction I need to proceed.
Thanks for any help.
Ram.