Return Latest Quarterly and Monthly info.

[spartanmba]

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

 

[[MVP] S.Clark]

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
(e-mail address removed)
www.fmsinc.com/consulting

 

[spartanmba]

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