How do i input fields in another table that is a template?

[AccessUser30]

Help! Help! I'm creating a report to track $ sum monthly and my boss wants
the information formatted in a way that the report will capture the next
month's figures without manually adjusting the report. However, the user will
have to update the query with new information monthly. I started with
setting up a table with the 12months and attached that table to a cross tab
query, which as you can see didn't work as planned. Any input would be
welcome, as I’m a new Access user.

 

[Larry Linson]

Help! Help! I'm creating a report to track $ sum monthly
and my boss wants the information formatted in a way
that the report will capture the next month's figures without
manually adjusting the report. However, the user will have
to update the query with new information monthly. I started
with setting up a table with the 12months and attached that
table to a cross tab query, which as you can see didn't work
as planned. Any input would be welcome, as I'm a new
Access user.

Actually, no, we canNOT see whether or not it worked as planned (but,
_please_, do not attah the database, or screen captures, to posts in this
newsgroup).

If you'd like to get some useful help, you will have to describe "precisely
and concisely" what you have (e.g., your Tables, Queries, and pertinent
Fields), what you are trying to accomplish, what, exactly, you have done,
and what, exactly, happened that "didn't work".

It certainly should be possible to design Tables, Queries, and Reports to
track information on a Monthly basis. Whether it will be possible without
re-design of your database will depend on what you have (from your comment
about "setting up a table with 12 months", I'm suspicious that a re-design
will be required).

Larry Linson
Microsoft Access MVP

 

[AccessUser30]

Objective: I need to create a report in Access that tracks monthly
expenditures for one fiscal year. The dilemma comes in with the report; my
boss wants me to format the report in a manner that the information would be
updated in the query only not the report.

As such, I created a cross tab query to filter the information from my
source table, which provided precisely what I needed and as I update the
source table the query will be updated as well. Then, I created another
table with one field called month with each month as the data to be reference
in my report. I expected the months (Oct, Nov, Dec and etc.) to appear as
fields in my report query, which is not the case as only the months with data
appear not the months to come. How can I make the report look like a
template with the months across the header, which can be done by creating
labels for each month, but how can i reference future fields to appear in the
appropriate column?

 

[AccessUser30]

how can i solve for this message?

 

[AccessUser30]

how can i solve for this message? (Cannot use the crosstab of a non-fixed column as a subquery)

 

[John Spencer]

You must be using a crosstab as a subquery. What the message says is
that you must specify the columns being returned in by the crosstab query.

Transform
SELECT
FROM
PIVOT SomeField IN ("Column1Value","Column2Value","Column3Value")

The values are what you would expect to see as column headers if you ran
the crosstab by itself.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[AccessUser30]

yes, but how can i correct this error?

TRANSFORM Sum(qry1DecReport.Amount) AS SumOfAmount SELECT qry1DecReport.[CSA
Account], FIND_CSA.from_fac, FIND_CSA.from_site, Sum(qry1DecReport.Amount) AS
Total FROM (tblDate LEFT JOIN qry1DecReport ON
tblDate.Month=qry1DecReport.Month_Yr) LEFT JOIN FIND_CSA ON
qry1DecReport.[CSA Account]=FIND_CSA.csa_account GROUP BY qry1DecReport.[CSA
Account], FIND_CSA.from_fac, FIND_CSA.from_site PIVOT tblDate.Month;

 

[John Spencer]

As I said previously, the query must specify the names of the columns it is
returning An example of specifying the column names is shown below

CHECK the PIVOT line of the query.

TRANSFORM Sum(qry1DecReport.Amount) AS SumOfAmount
SELECT qry1DecReport.[CSA Account]
, FIND_CSA.from_fac
, FIND_CSA.from_site
, Sum(qry1DecReport.Amount) AS Total
FROM (tblDate LEFT JOIN qry1DecReport
ON tblDate.Month=qry1DecReport.Month_Yr)
LEFT JOIN FIND_CSA
ON qry1DecReport.[CSA Account]=FIND_CSA.csa_account
GROUP BY qry1DecReport.[CSA Account], FIND_CSA.from_fac, FIND_CSA.from_site
PIVOT tblDate.Month IN (1,2,3,4,5,6,7,8,9,10,11,12)

If tblDate.Month is a text field with the abbreviated names of the months,
then the in clause would have to have the month names
PIVOT tblDate.Month IN ("Jan","Feb",...,"Dec")

Try the above modification and see if that solves your problem.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

yes, but how can i correct this error?

TRANSFORM Sum(qry1DecReport.Amount) AS SumOfAmount SELECT
qry1DecReport.[CSA
Account], FIND_CSA.from_fac, FIND_CSA.from_site, Sum(qry1DecReport.Amount)
AS
Total FROM (tblDate LEFT JOIN qry1DecReport ON
tblDate.Month=qry1DecReport.Month_Yr) LEFT JOIN FIND_CSA ON
qry1DecReport.[CSA Account]=FIND_CSA.csa_account GROUP BY
qry1DecReport.[CSA
Account], FIND_CSA.from_fac, FIND_CSA.from_site PIVOT tblDate.Month;

You must be using a crosstab as a subquery. What the message says is
that you must specify the columns being returned in by the crosstab
query.

Transform
SELECT
FROM
PIVOT SomeField IN ("Column1Value","Column2Value","Column3Value")

The values are what you would expect to see as column headers if you ran
the crosstab by itself.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[AccessUser30]

Thanks Spencer!

As I said previously, the query must specify the names of the columns it is
returning An example of specifying the column names is shown below

CHECK the PIVOT line of the query.

TRANSFORM Sum(qry1DecReport.Amount) AS SumOfAmount
SELECT qry1DecReport.[CSA Account]
, FIND_CSA.from_fac
, FIND_CSA.from_site
, Sum(qry1DecReport.Amount) AS Total
FROM (tblDate LEFT JOIN qry1DecReport
ON tblDate.Month=qry1DecReport.Month_Yr)
LEFT JOIN FIND_CSA
ON qry1DecReport.[CSA Account]=FIND_CSA.csa_account
GROUP BY qry1DecReport.[CSA Account], FIND_CSA.from_fac, FIND_CSA.from_site
PIVOT tblDate.Month IN (1,2,3,4,5,6,7,8,9,10,11,12)

If tblDate.Month is a text field with the abbreviated names of the months,
then the in clause would have to have the month names
PIVOT tblDate.Month IN ("Jan","Feb",...,"Dec")

Try the above modification and see if that solves your problem.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

yes, but how can i correct this error?

TRANSFORM Sum(qry1DecReport.Amount) AS SumOfAmount SELECT
qry1DecReport.[CSA
Account], FIND_CSA.from_fac, FIND_CSA.from_site, Sum(qry1DecReport.Amount)
AS
Total FROM (tblDate LEFT JOIN qry1DecReport ON
tblDate.Month=qry1DecReport.Month_Yr) LEFT JOIN FIND_CSA ON
qry1DecReport.[CSA Account]=FIND_CSA.csa_account GROUP BY
qry1DecReport.[CSA
Account], FIND_CSA.from_fac, FIND_CSA.from_site PIVOT tblDate.Month;

You must be using a crosstab as a subquery. What the message says is
that you must specify the columns being returned in by the crosstab
query.

Transform
SELECT
FROM
PIVOT SomeField IN ("Column1Value","Column2Value","Column3Value")

The values are what you would expect to see as column headers if you ran
the crosstab by itself.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


AccessUser30 wrote:

:

how can i solve for this message? (Cannot use the crosstab of a
non-fixed column as a subquery)