How to use SQL to filter columns in a spreadsheet.
I have a query that is listed below. I need to develop a statement that would filter the month and the year as well as the Phase informaton in the access table. __________________________________________________________________
Select INMSP300.M3OE# as "OENo", INMSP300.M3OEC as "ComprsOE", INMSP300.M3PRD as "PN", INMSP300.M3CLS as "PrdLine", INMSP100.MSAPP, INMSP100.MSPRD, [strong]INMSP100.MSRDTM as "rtd-Month", INMSP100.MSRDTD as "rdc-Day", INMSP100.MSRDTY as "rtc-Year", INMSP100.[/strong]MSMPC as "Phase" From rdb.CARF1.INMSP300 INMSP300 join rdb.CARF1.INMSP100 INMSP100 on M3PRD = MSPRD Where INMSP300.M3CLS ='79'
________________________________________________________________
I want to filter the spreadsheet so the [strong]INMSP100.MSRDTMas "rtd-Month" =11[/strong] and the [strong]rtc-Year=12[/strong], and then I need the [strong]INMSP100.MSMPC as "Phanse" grouped[/strong] as for lowest number to highest. Then numbers are few 1,2,3, and 4. So would it be a group or a "What"? What would I need to do to the this statement above to be able to accomplish this?
Software/Hardware used:
MS Access 2000, MSXP200,
Select INMSP300.M3OE# as "OENo", INMSP300.M3OEC as "ComprsOE", INMSP300.M3PRD as "PN", INMSP300.M3CLS as "PrdLine", INMSP100.MSAPP, INMSP100.MSPRD, [strong]INMSP100.MSRDTM as "rtd-Month", INMSP100.MSRDTD as "rdc-Day", INMSP100.MSRDTY as "rtc-Year", INMSP100.[/strong]MSMPC as "Phase" From rdb.CARF1.INMSP300 INMSP300 join rdb.CARF1.INMSP100 INMSP100 on M3PRD = MSPRD Where INMSP300.M3CLS ='79'
________________________________________________________________
I want to filter the spreadsheet so the [strong]INMSP100.MSRDTMas "rtd-Month" =11[/strong] and the [strong]rtc-Year=12[/strong], and then I need the [strong]INMSP100.MSMPC as "Phanse" grouped[/strong] as for lowest number to highest. Then numbers are few 1,2,3, and 4. So would it be a group or a "What"? What would I need to do to the this statement above to be able to accomplish this?
Software/Hardware used:
MS Access 2000, MSXP200,
ASKED:
October 23, 2009 12:43 PM
UPDATED:
October 23, 2009 5:41 PM
Answer Wiki:
I'm not sure if I understood correctly, but, how about this ?
<pre>Select INMSP300.M3OE# as "OENo", INMSP300.M3OEC as "ComprsOE", INMSP300.M3PRD as "PN",
INMSP300.M3CLS as "PrdLine", INMSP100.MSAPP, INMSP100.MSPRD, INMSP100.MSRDTM as "rtd-Month",
INMSP100.MSRDTD as "rdc-Day", INMSP100.MSRDTY as "rtc-Year", INMSP100.MSMPC as "Phase"
From rdb.CARF1.INMSP300 INMSP300
join rdb.CARF1.INMSP100 INMSP100
on M3PRD = MSPRD
Where INMSP300.M3CLS ='79'
and INMSP100.MSRDTY = '09'
and INMSP100.MSRDTM = '11'
order by INMSP100.MSMPC</pre>
Please let us know if that is what you meant.
To see all answers submitted to the Answer Wiki: View Answer History.
I would recommend to post some example data, and show us what the result would look like.
I was able to paste only the last four columns to the site. These are the columns that need to be filtered by access but I what the query to be able to give me the report without me having to filter the results… The year and the month are the only columns that would need the filtering action. The month would be filter using 11 and the Year by 09 and the phase would need to be group so that the phase are either increasing or decreasing not random.
rtd-Month rdc-Day rtc-Year Phase
2 26 09 4
9 26 11 3
11 26 01 4
12 26 03 2
11 26 01 1
12 26 09 3
11 26 9 3
12 26 9 3
10 26 9 3
rtd-Month——rdc-Day——– rtc-Year——— Phase
2 —————— 26 —————— 09 ————–4
9 —————– 26 —————–11 —————3
11 —————- 26 —————— 01 ————–4
12 —————-26 —————— 03 ————–2
11 —————–26 ——————01 ————–1
12 —————–26 ——————09 —————3
11 —————–26 —————— 09 ————– 3
12 —————–26 —————— 09 ————– 3
10 —————– 26 —————— 09 ————-3