Rabu, 30 Juli 2014

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

.

__,_._,___

Tidak ada komentar:

Posting Komentar