OK. I added your query as written, but now I am assuming that the crosstab query needs to be redone- because it doesn't work. It says Teh MA dataabase engine does not reconize '[sj_query_to_cal_ref_by_brd_sourc_byFY_for_Crs_tab].Classification_Referral_Source' as a valid field name or expression
TRANSFORM Count([sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].CaseID) AS CountOfCaseID
SELECT [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].FY, Count([sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].CaseID) AS [Total Of CaseID]
FROM [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab]
GROUP BY [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].FY
PIVOT [sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab].Classification_Referral_Source;
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Sheryl,
You can modify the SQL of a query so have any of the suggestions worked such as:
SELECT Month([Date_Closed]) AS CalendarMonth,
Year([Date_Closed]) AS CalendarYear,
IIf([CalendarMonth]>6,[CalendarYear]+1,[CalendarYear]) AS FY,
sj_qry_close_by_referral_by_fy.CaseID,
sj_qry_close_by_referral_by_fy.Date_Closed,
sj_qry_close_by_referral_by_fy.Board,
IIf(Classification_Referral_Source IN ("Board-FUDT","Board-Eligibility"), "Combined Name", Classification_Referral_Source) as ClassRefSource
FROM sj_qry_close_by_referral_by_fy
WHERE (((sj_qry_close_by_referral_by_fy.Date_Closed) Between #7/1/2007# And #6/30/2014#));
Also, can you set your emails to include the previous email?
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 30 Jul 2014 11:01:05 -0700
Subject: [MS_AccessPros] Re: Question RE my FY query
Unfortunately, I do not have the ability to adapt the tables in this case so I am trying to come up with a work around.
You can modify the SQL of a query so have any of the suggestions worked such as:
SELECT Month([Date_Closed]) AS CalendarMonth,
Year([Date_Closed]) AS CalendarYear,
IIf([CalendarMonth]>6,[CalendarYear]+1,[CalendarYear]) AS FY,
sj_qry_close_by_referral_by_fy.CaseID,
sj_qry_close_by_referral_by_fy.Date_Closed,
sj_qry_close_by_referral_by_fy.Board,
IIf(Classification_Referral_Source IN ("Board-FUDT","Board-Eligibility"), "Combined Name", Classification_Referral_Source) as ClassRefSource
FROM sj_qry_close_by_referral_by_fy
WHERE (((sj_qry_close_by_referral_by_fy.Date_Closed) Between #7/1/2007# And #6/30/2014#));
Also, can you set your emails to include the previous email?
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 30 Jul 2014 11:01:05 -0700
Subject: [MS_AccessPros] Re: Question RE my FY query
Unfortunately, I do not have the ability to adapt the tables in this case so I am trying to come up with a work around.
__._,_.___
Posted by: Sheryl.Jones@state.mn.us
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (14) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar