Sheryl-
The name of the field in your source query is ClassRefSource, not Classification_Referral_Source.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jul 30, 2014, at 2:24 PM, Sheryl.Jones@state.mn.us [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (15) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar