Re: [MS_AccessPros] Re: Question RE my FY query

 

Sheryl-

If you want to show totals by Board, you need to include that field in the SELECT and GROUP BY clauses of the Crosstab query.

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 

http://www.viescas.com/ 

(Paris, France)

On Jul 30, 2014, at 3:11 PM, Sheryl.Jones@state.mn.us [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ok, I know y'all are probably getting tired of my questions, but now that I have both the source query working and the crosstab query working, the data I was looking for does not separate as I was hoping so I'm fore confused than ever.  When I go back to the source query and run it,shows everything separated not combining the count in that query

sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab

CalendarMonth	CalendarYear	FY	CaseID	Date_Closed	Board	ClassRefSource
3	2008	2008	4449	3/6/2008	Nursing	Third Party
7	2010	2011	5681	7/7/2010	Marriage & Family Therapy	Board - Eligibility for Monitoring
10	2007	2008	3899	10/17/2007	Nursing	Self-Report
10	2010	2011	5851	10/20/2010	Nursing	Self-Report
2	2014	2014	7493	2/20/2014	Nursing	Third Party
2	2012	2012	6175	2/7/2012	Nursing	Board Action

and when I run the Cross tab query, it doesn't show the combined data either?

THOUGHTS?

sj_qry_to_cal_ref_by_brd_sourc_by FY_for_Crs_tab_Crosstab

FY	Total Of CaseID	Board - Eligibility for Monitoring	Board - Follow-Up to Diagnosis/Treatment	Board Action	Self-Report	Third Party
2008	449	60	78	53	207	51
2009	462	78	70	45	183	86
2010	489	87	64	62	201	75
2011	524	103	68	57	189	107
2012	455	91	37	56	193	78
2013	522	107	37	79	227	72
2014	527	120	55	83	189	80

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

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 

http://www.viescas.com/ 

(Paris, France)

On Jul 30, 2014, at 2:24 PM, Sheryl.Jones@... [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

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.

 

 

__._,_.___

---

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 (17)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___