Rabu, 30 Juli 2014

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

 

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: Sheryl.Jones@state.mn.us
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (16)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

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

 

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

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

.

__,_._,___
Diposting oleh di Tidak ada komentar:

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

 

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: Sheryl.Jones@state.mn.us
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (14)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

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

 

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: Duane Hookom <duanehookom@hotmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (13)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] corrupted database

 

I had a similar issue on a multi-table database, where I need to verify
before/after maintenance or backup work.

I built a form where I have a field for each table I want to review.

Inside that field, I built an event using this code:

=DCount("[indexfield]","[tablename]")

An index field is used as there is always a value in every row. I believe
that
it only counts rows where that field is occupied.

I monitor about 40 different tables this way, and print out the form before
and
after. I added a field that displays the current date/time, which is
included in
the printout.

Hope this helps,
Gary

Original email:
-----------------
From: Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals]
MS_Access_Professionals@yahoogroups.com
Date: Wed, 30 Jul 2014 12:31:56 -0500
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] corrupted database

Every once in a while I have a problem with a database and it compacts and
repairs.

It then tells me to check to see if I have lost any records. Usually I know
what table to look at but I should check to see if there are any missing
records.

Is there any way to see how many records are in each table without opening
each table? It would really save me a lot of time.

Thanks for the help.

Bill

MN

----------------------------------------------------------
mail2web LIVE Free email based on Microsoft Exchange technology -
http://link.mail2web.com/LIVE

__._,_.___
Posted by: "gary.schwartz@pobox.com" <gary.schwartz@pobox.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[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 (12)
Yahoo Groups
Did you Know?
Learn about attachment settings in your groups.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] corrupted database

 

Every once in a while I have a problem with a database and it compacts and repairs.

 

It then tells me to check to see if I have lost any records.  Usually I know what table to look at but I should check to see if there are any missing records.

 

Is there any way to see how many records are in each table without opening each table?  It would really save me a lot of time.

 

Thanks for the help.

Bill

 

MN

 

__._,_.___
Posted by: "Bill Singer" <Bill.Singer@at-group.net>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)
Yahoo Groups
Did you Know?
Learn about attachment settings in your groups.

.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)