If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Tricky query grouping
Okay I have a table that lists products and all their prices. In addition
there is a field that lists the "status" of the item (sale, full price). I want to have a query that shows me just every place the status changes and on what date. The problem is that an item could have 4 different sale prices and then be placed back at full price (seasonal items) and then returned to sale. So with a simple grouping my output would look like: Item 1 $20 1/1/05 Full Price Item 1 $15 3/1/05 Sale Item 1 $12 4/1/05 Sale Item 1 $20 8/1/05 Full Price Item 1 $18 11/1/05 Sale I want an output like this: Item 1 1/1/05 Full Price Item 1 3/1/05 Sale Item 1 8/1/05 Full Price Item 1 11/1/05 Sale Anyone have any ideas or suggestions? Thanks! |
#2
|
|||
|
|||
Tricky query grouping
Dear JLamb:
The thing you want done can be done using a correlated subquery. This subquery would return the status of the immediately previous row for the same item. You could then exclude the current row if the previous one is for the same status. There will be a potential problem here. There must never be two rows for the same item and the same date. Obviously, this makes the job of determining whether the previous row is for the same status, since there would be two rows equally qualified to be the previous row. I recommend there be a constraint on the table in the form of a unique index by Item and Date. Otherwise, the subquery will fail by virtue of returning both rows. Since the table makes the job ambiguous, this is as it should be. If you wish, I can provide the query you need. You must first post the query that produces the first set you show, the raw data. Do not use a wildcare (*) for the column names, but show them individually so I can use them to produce the remainder of the query. Tom Ellison "JLamb" wrote in message ... Okay I have a table that lists products and all their prices. In addition there is a field that lists the "status" of the item (sale, full price). I want to have a query that shows me just every place the status changes and on what date. The problem is that an item could have 4 different sale prices and then be placed back at full price (seasonal items) and then returned to sale. So with a simple grouping my output would look like: Item 1 $20 1/1/05 Full Price Item 1 $15 3/1/05 Sale Item 1 $12 4/1/05 Sale Item 1 $20 8/1/05 Full Price Item 1 $18 11/1/05 Sale I want an output like this: Item 1 1/1/05 Full Price Item 1 3/1/05 Sale Item 1 8/1/05 Full Price Item 1 11/1/05 Sale Anyone have any ideas or suggestions? Thanks! |
#3
|
|||
|
|||
Tricky query grouping
I know how to write a correlated subquery, but it would have to have only one
SELECT item, in this case that would be the status. But you would need to pull the max date the current row date. How would you accomplish this without putting max([stat dt]) in the SELECT clause? A subquery within the correlated subquery? "Tom Ellison" wrote: Dear JLamb: The thing you want done can be done using a correlated subquery. This subquery would return the status of the immediately previous row for the same item. You could then exclude the current row if the previous one is for the same status. There will be a potential problem here. There must never be two rows for the same item and the same date. Obviously, this makes the job of determining whether the previous row is for the same status, since there would be two rows equally qualified to be the previous row. I recommend there be a constraint on the table in the form of a unique index by Item and Date. Otherwise, the subquery will fail by virtue of returning both rows. Since the table makes the job ambiguous, this is as it should be. If you wish, I can provide the query you need. You must first post the query that produces the first set you show, the raw data. Do not use a wildcare (*) for the column names, but show them individually so I can use them to produce the remainder of the query. Tom Ellison "JLamb" wrote in message ... Okay I have a table that lists products and all their prices. In addition there is a field that lists the "status" of the item (sale, full price). I want to have a query that shows me just every place the status changes and on what date. The problem is that an item could have 4 different sale prices and then be placed back at full price (seasonal items) and then returned to sale. So with a simple grouping my output would look like: Item 1 $20 1/1/05 Full Price Item 1 $15 3/1/05 Sale Item 1 $12 4/1/05 Sale Item 1 $20 8/1/05 Full Price Item 1 $18 11/1/05 Sale I want an output like this: Item 1 1/1/05 Full Price Item 1 3/1/05 Sale Item 1 8/1/05 Full Price Item 1 11/1/05 Sale Anyone have any ideas or suggestions? Thanks! |
#4
|
|||
|
|||
Tricky query grouping
Dear JLamb:
Why do you believe you must NOT put MAX([stat dt]) in the SELECT clause? How about: SELECT Item, Amount, [stat dt], Status FROM YourTable T WHERE Status (SELECT Status FROM YourTable T1 WHERE T1.Item = T.Item AND T1.[stat dt] = (SELECT MAX([stat dt] FROM YourTable T2 WHERE T2.Item = T.Item AND T2.[stat dt] T.[stat dt])) Again, this will not work if there are multiple rows for an Item on the same date. I have seen this structure work in Access Jet, and I have seen it fail. I'm not sure what determines whether it will work or not. This query would have no problem in MSDE. I don't know which you use, but for really serious query work, I recommend MSDE strongly. However, this CAN be done in Jet even if the above fails for you. Let me know if you need details of this. You must, of course, ammend the above to use your actual column and table names. Tom Ellison "JLamb" wrote in message ... I know how to write a correlated subquery, but it would have to have only one SELECT item, in this case that would be the status. But you would need to pull the max date the current row date. How would you accomplish this without putting max([stat dt]) in the SELECT clause? A subquery within the correlated subquery? "Tom Ellison" wrote: Dear JLamb: The thing you want done can be done using a correlated subquery. This subquery would return the status of the immediately previous row for the same item. You could then exclude the current row if the previous one is for the same status. There will be a potential problem here. There must never be two rows for the same item and the same date. Obviously, this makes the job of determining whether the previous row is for the same status, since there would be two rows equally qualified to be the previous row. I recommend there be a constraint on the table in the form of a unique index by Item and Date. Otherwise, the subquery will fail by virtue of returning both rows. Since the table makes the job ambiguous, this is as it should be. If you wish, I can provide the query you need. You must first post the query that produces the first set you show, the raw data. Do not use a wildcare (*) for the column names, but show them individually so I can use them to produce the remainder of the query. Tom Ellison "JLamb" wrote in message ... Okay I have a table that lists products and all their prices. In addition there is a field that lists the "status" of the item (sale, full price). I want to have a query that shows me just every place the status changes and on what date. The problem is that an item could have 4 different sale prices and then be placed back at full price (seasonal items) and then returned to sale. So with a simple grouping my output would look like: Item 1 $20 1/1/05 Full Price Item 1 $15 3/1/05 Sale Item 1 $12 4/1/05 Sale Item 1 $20 8/1/05 Full Price Item 1 $18 11/1/05 Sale I want an output like this: Item 1 1/1/05 Full Price Item 1 3/1/05 Sale Item 1 8/1/05 Full Price Item 1 11/1/05 Sale Anyone have any ideas or suggestions? Thanks! |
#5
|
|||
|
|||
Tricky query grouping
As I said with a correlated subquery you can only return one value which in
this case would have to be [status]. This code does use a second nested correlated subquery within the subquery as I suspected was the only way to accomplish your original suggestion. I believe this may work, but I was hoping (probably unrealistically) for a much simpler solution. Thanks! "Tom Ellison" wrote: Dear JLamb: Why do you believe you must NOT put MAX([stat dt]) in the SELECT clause? How about: SELECT Item, Amount, [stat dt], Status FROM YourTable T WHERE Status (SELECT Status FROM YourTable T1 WHERE T1.Item = T.Item AND T1.[stat dt] = (SELECT MAX([stat dt] FROM YourTable T2 WHERE T2.Item = T.Item AND T2.[stat dt] T.[stat dt])) Again, this will not work if there are multiple rows for an Item on the same date. I have seen this structure work in Access Jet, and I have seen it fail. I'm not sure what determines whether it will work or not. This query would have no problem in MSDE. I don't know which you use, but for really serious query work, I recommend MSDE strongly. However, this CAN be done in Jet even if the above fails for you. Let me know if you need details of this. You must, of course, ammend the above to use your actual column and table names. Tom Ellison "JLamb" wrote in message ... I know how to write a correlated subquery, but it would have to have only one SELECT item, in this case that would be the status. But you would need to pull the max date the current row date. How would you accomplish this without putting max([stat dt]) in the SELECT clause? A subquery within the correlated subquery? "Tom Ellison" wrote: Dear JLamb: The thing you want done can be done using a correlated subquery. This subquery would return the status of the immediately previous row for the same item. You could then exclude the current row if the previous one is for the same status. There will be a potential problem here. There must never be two rows for the same item and the same date. Obviously, this makes the job of determining whether the previous row is for the same status, since there would be two rows equally qualified to be the previous row. I recommend there be a constraint on the table in the form of a unique index by Item and Date. Otherwise, the subquery will fail by virtue of returning both rows. Since the table makes the job ambiguous, this is as it should be. If you wish, I can provide the query you need. You must first post the query that produces the first set you show, the raw data. Do not use a wildcare (*) for the column names, but show them individually so I can use them to produce the remainder of the query. Tom Ellison "JLamb" wrote in message ... Okay I have a table that lists products and all their prices. In addition there is a field that lists the "status" of the item (sale, full price). I want to have a query that shows me just every place the status changes and on what date. The problem is that an item could have 4 different sale prices and then be placed back at full price (seasonal items) and then returned to sale. So with a simple grouping my output would look like: Item 1 $20 1/1/05 Full Price Item 1 $15 3/1/05 Sale Item 1 $12 4/1/05 Sale Item 1 $20 8/1/05 Full Price Item 1 $18 11/1/05 Sale I want an output like this: Item 1 1/1/05 Full Price Item 1 3/1/05 Sale Item 1 8/1/05 Full Price Item 1 11/1/05 Sale Anyone have any ideas or suggestions? Thanks! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Grouping and Sums issue (do it w/ Query or Report?) | rgrantz | Setting Up & Running Reports | 1 | December 13th, 2004 02:48 AM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |