Return Latest Quarterly and Monthly info.

Hello,

I have a table with the follow data:

Store Quarter Month Sales Percent ID
245 2 5 3.5% 110
245 2 6 3% 111
245 2 7 5% 243
245 2 12 8% 249
245 3 7 4.5% 302
245 3 8 4% 303
245 3 12 6% 310

I would like the result set to return this:

Store Quarter Month Sales Percent ID
245 2 5 3.5% 110
245 2 6 3% 111
245 3 7 4.5% 302
245 3 8 4% 303
245 3 12 6% 310

I think I know what the query needs to do, but I have no idea how to
approach it. I am thinking that the query would take an individual record
(id 243 in this case) and see if it is the highest quarterly information in
the table while keeping the store number and month the same. To give a
little more insight, the data in the table is actual and forecasted percents.
For example, record 310 is a forecasted amount during the third quarter for
month 12 which is in the fourth quarter. I would want 310 to appear in my
result set until sometime in the fourth quarter when a data point for month
twelve was added.

Thanks for your help,
John

What is it about Q2M7 & Q2M12 that should cause it to be excluded from the
output?
(I've crashed a lot of motorcycles, so forgive my cranial density.)

Maybe this website can help: http://www.mvps.org/access/queries/qry0020.htm


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234

www.fmsinc.com/consulting


"spartanmba" wrote in message
...
Hello,

I have a table with the follow data:

Store Quarter Month Sales Percent ID
245 2 5 3.5% 110
245 2 6 3% 111
245 2 7 5% 243
245 2 12 8% 249
245 3 7 4.5% 302
245 3 8 4% 303
245 3 12 6% 310

I would like the result set to return this:

Store Quarter Month Sales Percent ID
245 2 5 3.5% 110
245 2 6 3% 111
245 3 7 4.5% 302
245 3 8 4% 303
245 3 12 6% 310

I think I know what the query needs to do, but I have no idea how to
approach it. I am thinking that the query would take an individual record
(id 243 in this case) and see if it is the highest quarterly information
in
the table while keeping the store number and month the same. To give a
little more insight, the data in the table is actual and forecasted
percents.
For example, record 310 is a forecasted amount during the third quarter
for
month 12 which is in the fourth quarter. I would want 310 to appear in my
result set until sometime in the fourth quarter when a data point for
month
twelve was added.

Thanks for your help,
John

No problem and good question. Q2M7 and Q2M12 would be excluded from the list
if I have information from a quarter greater than 2. So if I have q3m7 then
I would return the information for it and not Q2. Same with Q2M12. If I
have Q3 then I would want it unless I have Q4. The largest quarterly number
for a month is what I would like returned.

Thanks,
John

"[MVP] S.Clark" wrote:

What is it about Q2M7 & Q2M12 that should cause it to be excluded from the
output?
(I've crashed a lot of motorcycles, so forgive my cranial density.)

Maybe this website can help: http://www.mvps.org/access/queries/qry0020.htm


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234

www.fmsinc.com/consulting


"spartanmba" wrote in message
...
Hello,

I have a table with the follow data:

Store Quarter Month Sales Percent ID
245 2 5 3.5% 110
245 2 6 3% 111
245 2 7 5% 243
245 2 12 8% 249
245 3 7 4.5% 302
245 3 8 4% 303
245 3 12 6% 310

I would like the result set to return this:

Store Quarter Month Sales Percent ID
245 2 5 3.5% 110
245 2 6 3% 111
245 3 7 4.5% 302
245 3 8 4% 303
245 3 12 6% 310

I think I know what the query needs to do, but I have no idea how to
approach it. I am thinking that the query would take an individual record
(id 243 in this case) and see if it is the highest quarterly information
in
the table while keeping the store number and month the same. To give a
little more insight, the data in the table is actual and forecasted
percents.
For example, record 310 is a forecasted amount during the third quarter
for
month 12 which is in the fourth quarter. I would want 310 to appear in my
result set until sometime in the fourth quarter when a data point for
month
twelve was added.

Thanks for your help,
John