Design Help! 1

[alwayshouston]

Hi All!
I need help in design of a database table. I have a Sales with the sales information coming from different sources. I need to priortize the output in my query to what I define in the table. Let me explain this via table structure. Lets say I have a source Sales Table as below:

Sales_ID Sales Commission Source
NEWYORK 10000 100 Adam
NEWYORK 10001 Chris
NEWYORK 107 Debbie
HOUSTON 20000 Delay
HOUSTON 100 Morrison
HOUSTON 20000 132 Perry

I have a source priority table which tells me the priority of source given to any id.
Source_ID Sales_ID Priority
ADAM NEWYORK 3
CHRIS NEWYORK 1
DEBBIE NEWYORK 2
DELAY HOUSTON 1
MORRISON HOUSTON 3
PERRY HOUSTON 2

I need to show the data in the priority as it listed in the table above. If some ask for NEWYORK data, it should retrieve data in priority (CHRIS first, Debbie 2 and ADAM 3). If the data is missing for any fields in the first priority, it should look at the second priority and give me the data. The ideal output of NEWYORK and Houston data is as follow:

Sales_ID Sales Sales_Source Comm Comm_Source
NEWYORK 10001 Chris 107 Debbie
HOUSTON 20000 Delay 132 Perry

I would like to design a table to capture above scenario and write query to show the data in sync with the prirority. How would I do that? Feel free to send me a link to a article that will help me.
Any help in this regard is appreciated!

Thanks in Advance!

 

[lespaul]

Can't you just join your two tables together on the Source_ID and sort by Priority?

Leslie

 

[alwayshouston]

Yes I can join the tables together on the Source_ID and sort by Priority, but this will give me muliple records for each sales ID. For example, It will give me three records in the query instead of one as mention below:

Sales_ID[ ][ ]Sales[ ][ ]Sales_Source[ ][ ]Comm[ ][ ]Comm_Source
NEWYORK[ ][ ]10001[ ][ ]Chris
NEWYORK[ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ][ ]107[ ][ ] Debbie
NEWYORK[ ][ ]10000[ ][ ]Adam[ ][ ]100[ ][ ]Adam

How can I have my results in one row based on the priority as below:

Sales_ID Sales Sales_Source Comm Comm_Source
NEWYORK 10001 Chris 107 Debbie

Thank you very much for your prompt reply!

 

[lespaul]

You need to look at a pivot table. It does what you are looking for.

Leslie

 

[alwayshouston]

Thanks I will try using cross tab query and see whether I can solve the problem.

Thanks!

 

[alwayshouston]

Leslie
I tried the cross tab query and I could not get the results as I desired. Can you help me in this regard?

Thanks!

 

[marsss]

Sales_ID Sales Sales_Source Comm Comm_Source
NEWYORK 10001 Chris
NEWYORK 107 Debbie
NEWYORK 10000 Adam 100 Adam

How can I have my results in one row based on the priority as below:

Sales_ID Sales Sales_Source Comm Comm_Source
NEWYORK 10001 Chris 107 Debbie

You tryed something like :

SELECT [...], Max( Comm )
[...]
GROUP BY Sales_ID;

 

[marsss]

oups,press to fast on reply


SELECT [...]
FROM table AS mainTable
WHERE (mainTable.Comm =
(SELECT Max( Comm )
FROM table AS SubTable
WHERE SubTable.Sales_ID = mainTable.Sales_ID))

 

[alwayshouston]

Thanks Marsss for your solution. But your solution does not incorporate the priority feature of data source for both sales and comm. Further, my output should not be dependent on the max value. It is dependend on the source priority as I defined it in the source table. Any thoughts/feedback? )

 

[lespaul]

Sales_ID  Sales  Commission Source
NEWYORK   10000  100        Adam
NEWYORK   10001             Chris
NEWYORK          107        Debbie
HOUSTON   20000             Delay
HOUSTON          100        Morrison
HOUSTON   20000  132        Perry

Sales_ID   Sales  Sales_Source  Comm  Comm_Source
NEWYORK    10001  Chris         107   Debbie

From your example records above, how do you know that Debbie is the CommSource information that goes with the NEWYORK 10001 record? How come Debbie doesn't go with the other NEWYORK record?

Leslie

 

[marsss]

I think it s because "NEWYORK 10001 " doesnt have any "Comission"

So he fill "107 Debbie" FROM first table where in the second table the priority is highter for the SALE_ID

 

[alwayshouston]

That is right Marsss. There is no comm data for Chris and debbie does have the data (and also second in the priority).

Thanks!

 

[lespaul]

could you provide some more sample records and how you want all those records to look in the output? For instance, above you show this for the HOUSTON record

Sales_ID   Sales  Sales_Source  Comm  Comm_Source
HOUSTON    20000  Delay         132   Perry

Why isn't it:

Sales_ID   Sales  Sales_Source  Comm  Comm_Source
NEWYORK    10001  Chris         107   Debbie
HOUSTON    20000  Delay         100   Morrison

The more details you provide, the easier it is to help you!







Leslie

 

[alwayshouston]

This reason the Houston record is showing Comm:132 and Comm_Sourceerry is because it is the second priority. Morrison and comm:100 is the third priority of data. Below is the table of source priority.

Source_ID Sales_ID Priority
ADAM NEWYORK 3
CHRIS NEWYORK 1
DEBBIE NEWYORK 2
DELAY HOUSTON 1
MORRISON HOUSTON 3
PERRY HOUSTON 2

Thanks!

 

[lespaul]

(again the more details you provided the more likely you are to get help. At this point I'm ready to give up because getting any information from you is like pulling teeth!!!)

so again with the records you provided:

Sales_ID  Sales  Commission Source
NEWYORK   10000  100        Adam
NEWYORK   10001             Chris
NEWYORK          107        Debbie
HOUSTON   20000             Delay
HOUSTON          100        Morrison
HOUSTON   20000  132        Perry

How do you know that Debbie goes with the 10001 sale? Just because it's the next record?

And you don't want Morrison on the 20000 sale even though it's the next record because Perry has SALES record that matches and has a higher priority than Morrison?

And what about the ADAM record? Does it not show up at all?

At this point I can't see any way to get this information in the way you want, because there is no tie between the Debbie Record (Other than it's the same location) and the Sales record. I've never seen such a weird structure.






Leslie

 

[alwayshouston]

I apologize for not being clear and articulate in my prior postings. Let me try to explain the situation. Lets say I am getting sales data from different sources. Each source has the priority assign to it. If the data is missing, it should look at the second priority and find the missing data. If all the data is found in first priority, it should ignore rest of the data for the output for same sales region. Therefore, in above case NEWYORK data has first preference on Chris. Since Chris does have sales data, but does not have Comm data it looks at the next priority (Debbie) and retrieves the comm data from Debbie. In Houston sales data, Delay has first priority. It retrieves the sales information from Delay. Since Delay does not have the Comm data it looks at the second priority (Perry) and gets Perry data for comm. The data retrieval is based on priority not on the amount of sales or comm.

I hope above situation makes sense. Recently, I have been assigned a project in which I am getting data for single entity from different sources based on the priority. If the first priority is missing data, the database query should display the missing data from second priority.

Again, thank you very much for helping. I am going nuts here to solve this problem via query or any other way.

 

[PHV]

Create a saved query, say SalesMinPriority :
SELECT F.Sales_ID, Min(F.Priority) As Priority2
FROM Source F INNER JOIN (
SELECT Sales_ID, Min(Priority) AS MinPriority FROM Source GROUP BY Sales_ID
) G ON F.Sales_ID=G.Sales_ID AND F.Priority>G.MinPriority
GROUP BY F.Sales_ID;

Then you may try this:
SELECT A.Sales_ID, A.Sales, A.Source AS Sales_Source,
Nz(A.Commission,B.Commission) AS Comm, IIf(IsNull(A.Commission),B.Source,A.Source) AS Comm_Source
FROM (Sales A INNER JOIN (Source S INNER JOIN SalesMinPriority M
ON S.Sales_ID=M.Sales_ID AND S.Priority<M.Priority2)
ON UCase(A.Source)=UCase(S.Source_ID) AND A.Sales_ID=S.Sales_ID)
INNER JOIN (Sales B INNER JOIN (Source T INNER JOIN SalesMinPriority N
ON T.Sales_ID=N.Sales_ID AND T.Priority=N.Priority2)
ON UCase(B.Source)=UCase(T.Source_ID) AND B.Sales_ID=T.Sales_ID)
ON A.Sales_ID=B.Sales_ID;

If the data is missing
I assume that missing commission is a Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244