Clicky

I have two tables and want to create a query/subquery to determine client status at time of purchase. Table 1 is clientstatus and has fields clientid,  status, and status date. So a record from clientstatus might read:

78 Active 12/20/2010
78 credithold 12/25/2010
78 Active 1/5/2011

Table 2 is clientorders and has fields clientid, orderdate, item, price. So a record from clientorder might read:

78, 12/26/2010, widget1, 20.00
78, 1/7/2011, widget3, 30.00
78, 1/7/2011, widget1, 20.00

I want the query to show the client status and the orders placed only if orders are placed and the total orders summed for each status.

Thank you.

asked 06/15/2011 03:48

hossupport's gravatar image

hossupport ♦♦


10 Answers:
You may try following query. I am just not sure if Access can process it:

SELECT clientid, status, SUM(price) FROM (
    SELECT
    t2.clientid,
    (SELECT TOP 1 status FROM Table1 t1
         WHERE t1.clientid = t2.clientid AND t1.status_date <= order_date
         ORDER BY status_date DESC) status,
    price
    FROM Table2 t2) allorders
GROUP BY clientid, status


I'll try it later.
link

answered

pcelba's gravatar image

pcelba

The order total for each status is not so clear...

Could you show the result sample if we add one more client status:

78 Active 12/20/2010
78 credithold 12/25/2010
78 Active 1/5/2011
79 Active 1/5/2011
79 credithold 1/8/2011

(79 is new client without orders)
link

answered 2011-06-15 at 12:09:53

pcelba's gravatar image

pcelba

Thanks for the reply. I couldn't get it to work and, after spending time experimenting I couldn't get close enough to find a solution myself. Any further suggestions? Thanks again.
link

answered 2011-06-15 at 12:37:27

hossupport's gravatar image

hossupport

OK, similar approach...  pretty much an inline subquery to get status

create a new query, use SQL view

1:
2:
SELECT O.clientid,O.orderdate,O.item,O.price,(select top 1 status from ClientStatus O2 where O2.clientid = O.clientid and O2.[status date] <= O.orderdate order by O2.[status date] desc) as status
FROM ClientOrders O;


should give you all the detail rows with the appropriate status setting (might want to wrap that in a NZ( (select.....) , 'Active' ) to default to active if no status set)

then once you are happy with detail rows, then we can aggregate that query as if it were a subquery...

1:
2:
3:
4:
5:
6:
7:
SELECT clientid, status, count(*) as Num_Orders, sum(price) as Order_Value
FROM 

        ( SELECT O.clientid,O.orderdate,O.item,O.price,(select top 1 status from ClientStatus O2 where O2.clientid = O.clientid and O2.[status date] <= O.orderdate order by O2.[status date] desc) as status
           FROM ClientOrders O ) SQ

GROUP BY clientid, status


See how you go with that....

If not, can you show the exact output you want from the example you posted in the question header ?
link

answered 2011-06-16 at 14:32:02

mark_wills's gravatar image

mark_wills

I've requested that this question be closed as follows:

Accepted answer: 500 points for mark_wills's comment http:/Q_27100761.html#36247018

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

link

answered 2011-07-25 at 01:35:11

leonstryker's gravatar image

leonstryker

Two almost same solutions were provided.

IMHO, hossupport should confirm the solution is correct. If not then the question deletion better follows EE rules.
link

answered 2011-12-07 at 11:11:56

pcelba's gravatar image

pcelba

No worries pcelba, and if I fix up your syntax (which the asker couldnt get to work) and use the naming conventions supplied (for tables and columns) and doing the test (why you were going to try later) we can even split the points :)

1:
2:
3:
4:
5:
6:
7:
8:
9:
SELECT clientid, status, SUM(price) FROM (
    SELECT 
    t2.clientid, 
    (SELECT TOP 1 status FROM ClientStatus t1 
         WHERE t1.clientid = t2.clientid AND t1.[status date] <= orderdate
         ORDER BY [status date] DESC) as status,
    price
    FROM ClientOrders t2) allorders
GROUP BY clientid, status
link

answered 2011-12-07 at 13:04:42

mark_wills's gravatar image

mark_wills

Points split isn't the issue here. I would rather hear the word from hossupport. If he is unable to substitute correct column or table names into the query provided then he should tell it. If the solution does not give correct results then I would like to know it...

link

answered 2011-12-07 at 18:15:25

pcelba's gravatar image

pcelba

*laughing* the Asker did say something...

And that is the reason why I posted :)
link

answered 2011-12-07 at 22:38:26

mark_wills's gravatar image

mark_wills

Starting the auto-close procedure on behalf of the question asker. Please see the referenced question for more details.

SouthMod
Community Support Moderator

link

answered 2011-12-08 at 08:33:53

SouthMod's gravatar image

SouthMod

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1
×132

Asked: 06/15/2011 03:48

Seen: 286 times

Last updated: 12/14/2011 05:16