Crosstab Worries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a crosstab query to sum up numbers in a table by month; it works
fine. I am using this query as the recordsource to a form. This also works
fine because I have set the form to be continous, so I see all twenty rows
with their totals in each of the twelve columns. When you view the form it
becomes obvious that the monthly columns need to be totaled. This is the
problem. I can sum each row in the query, but how do I get the form to total
the twenty numbers in each monthly column. I have set the text box in the
footer to reference the text box in the detail section and all I get is the
first number. I need some sort of running sum option that you can do in
reports so I can get a column total on the form. Any suggestions will be most
appreciated.
Thanks
Michael
 
You should be able to use a text box (or 12) in your form footer section with
a control source like:
=Sum([Your Field Name])
 
In reading over my question I realize I must have been tired Friday night
because I left out an important part. I am already using the =Sum([Jan]) and
=Sum([Feb]), etc. for all twelve columns and it is giving me the value in
record one (or wherever I place the cursor) but not totaling the full list of
twenty items. I tested the sum formula in another database and it worked
fine, the column was totaled. I think the query is causing the problem.
In the crosstab to get a column for each month, I am using the formula Exp1:
Format([Date],"mmm") and changing the column headings to "Jan", "Feb", etc.
on the column heading option so all twelve show up every time. The Exceptions
column (the one I want to total) is set to sum and value. When I execute the
query everything looks fine. I suspect that the text nature of the month
headings is changing the exceptions value to text. This would explain why the
current record will display but the column is not totaled. With this in mind
I have tried =Int([Jan]) in the form, but I suspect the underlying
recordsource is still text because the column is still not totaling.
Duane Hookom said:
You should be able to use a text box (or 12) in your form footer section with
a control source like:
=Sum([Your Field Name])

--
Duane Hookom
Microsoft Access MVP


apollo8359 said:
I created a crosstab query to sum up numbers in a table by month; it works
fine. I am using this query as the recordsource to a form. This also works
fine because I have set the form to be continous, so I see all twenty rows
with their totals in each of the twelve columns. When you view the form it
becomes obvious that the monthly columns need to be totaled. This is the
problem. I can sum each row in the query, but how do I get the form to total
the twenty numbers in each monthly column. I have set the text box in the
footer to reference the text box in the detail section and all I get is the
first number. I need some sort of running sum option that you can do in
reports so I can get a column total on the form. Any suggestions will be most
appreciated.
Thanks
Michael
Click to expand...
Click to expand...
 
Post the SQL view of your crosstab.
--
Duane Hookom
Microsoft Access MVP


apollo8359 said:
In reading over my question I realize I must have been tired Friday night
because I left out an important part. I am already using the =Sum([Jan]) and
=Sum([Feb]), etc. for all twelve columns and it is giving me the value in
record one (or wherever I place the cursor) but not totaling the full list of
twenty items. I tested the sum formula in another database and it worked
fine, the column was totaled. I think the query is causing the problem.
In the crosstab to get a column for each month, I am using the formula Exp1:
Format([Date],"mmm") and changing the column headings to "Jan", "Feb", etc.
on the column heading option so all twelve show up every time. The Exceptions
column (the one I want to total) is set to sum and value. When I execute the
query everything looks fine. I suspect that the text nature of the month
headings is changing the exceptions value to text. This would explain why the
current record will display but the column is not totaled. With this in mind
I have tried =Int([Jan]) in the form, but I suspect the underlying
recordsource is still text because the column is still not totaling.
Duane Hookom said:
You should be able to use a text box (or 12) in your form footer section with
a control source like:
=Sum([Your Field Name])

--
Duane Hookom
Microsoft Access MVP


apollo8359 said:
I created a crosstab query to sum up numbers in a table by month; it works
fine. I am using this query as the recordsource to a form. This also works
fine because I have set the form to be continous, so I see all twenty rows
with their totals in each of the twelve columns. When you view the form it
becomes obvious that the monthly columns need to be totaled. This is the
problem. I can sum each row in the query, but how do I get the form to total
the twenty numbers in each monthly column. I have set the text box in the
footer to reference the text box in the detail section and all I get is the
first number. I need some sort of running sum option that you can do in
reports so I can get a column total on the form. Any suggestions will be most
appreciated.
Thanks
Michael
Click to expand...
Click to expand...
Click to expand...
 
TRANSFORM Sum([tbl Exceptions].Exceptions) AS SumOfExceptions
SELECT [tbl Exceptions].SourceID, [qry Source].SourceShort,
Format([Date],"yyyy") AS [Year], Sum([tbl Exceptions].Exceptions) AS
SourceTotal
FROM [tbl Exceptions] LEFT JOIN [qry Source] ON [tbl Exceptions].SourceID =
[qry Source].SourceID
GROUP BY [tbl Exceptions].SourceID, [qry Source].SourceShort,
Format([Date],"yyyy"), [qry Source].Order
ORDER BY [qry Source].Order
PIVOT Format([Date],"mmm");

This is it, let me know if you need anything else, Thanks.

Duane Hookom said:
Post the SQL view of your crosstab.
--
Duane Hookom
Microsoft Access MVP


apollo8359 said:
In reading over my question I realize I must have been tired Friday night
because I left out an important part. I am already using the =Sum([Jan]) and
=Sum([Feb]), etc. for all twelve columns and it is giving me the value in
record one (or wherever I place the cursor) but not totaling the full list of
twenty items. I tested the sum formula in another database and it worked
fine, the column was totaled. I think the query is causing the problem.
In the crosstab to get a column for each month, I am using the formula Exp1:
Format([Date],"mmm") and changing the column headings to "Jan", "Feb", etc.
on the column heading option so all twelve show up every time. The Exceptions
column (the one I want to total) is set to sum and value. When I execute the
query everything looks fine. I suspect that the text nature of the month
headings is changing the exceptions value to text. This would explain why the
current record will display but the column is not totaled. With this in mind
I have tried =Int([Jan]) in the form, but I suspect the underlying
recordsource is still text because the column is still not totaling.
Duane Hookom said:
You should be able to use a text box (or 12) in your form footer section with
a control source like:
=Sum([Your Field Name])

--
Duane Hookom
Microsoft Access MVP


:

I created a crosstab query to sum up numbers in a table by month; it works
fine. I am using this query as the recordsource to a form. This also works
fine because I have set the form to be continous, so I see all twenty rows
with their totals in each of the twelve columns. When you view the form it
becomes obvious that the monthly columns need to be totaled. This is the
problem. I can sum each row in the query, but how do I get the form to total
the twenty numbers in each monthly column. I have set the text box in the
footer to reference the text box in the detail section and all I get is the
first number. I need some sort of running sum option that you can do in
reports so I can get a column total on the form. Any suggestions will be most
appreciated.
Thanks
Michael
Click to expand...
Click to expand...
Click to expand...
 
Your first post stated "I have set the text box in the footer to reference
the text box in the detail section". Are you really attempting to sum a text
box? This would not work. You can sum a field from the record source but not
a control.

I just created a form in Northwind based on the crosstab:
TRANSFORM Sum(Orders.Freight) AS SumOfFreight
SELECT Year([OrderDate]) AS Yr
FROM Orders
GROUP BY Year([OrderDate])
PIVOT Format([OrderDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I added text boxes in the form footer with control sources like:
=Sum([Jan])
This displayed the expected results.

--
Duane Hookom
Microsoft Access MVP


apollo8359 said:
TRANSFORM Sum([tbl Exceptions].Exceptions) AS SumOfExceptions
SELECT [tbl Exceptions].SourceID, [qry Source].SourceShort,
Format([Date],"yyyy") AS [Year], Sum([tbl Exceptions].Exceptions) AS
SourceTotal
FROM [tbl Exceptions] LEFT JOIN [qry Source] ON [tbl Exceptions].SourceID =
[qry Source].SourceID
GROUP BY [tbl Exceptions].SourceID, [qry Source].SourceShort,
Format([Date],"yyyy"), [qry Source].Order
ORDER BY [qry Source].Order
PIVOT Format([Date],"mmm");

This is it, let me know if you need anything else, Thanks.

Duane Hookom said:
Post the SQL view of your crosstab.
--
Duane Hookom
Microsoft Access MVP


apollo8359 said:
In reading over my question I realize I must have been tired Friday night
because I left out an important part. I am already using the =Sum([Jan]) and
=Sum([Feb]), etc. for all twelve columns and it is giving me the value in
record one (or wherever I place the cursor) but not totaling the full list of
twenty items. I tested the sum formula in another database and it worked
fine, the column was totaled. I think the query is causing the problem.
In the crosstab to get a column for each month, I am using the formula Exp1:
Format([Date],"mmm") and changing the column headings to "Jan", "Feb", etc.
on the column heading option so all twelve show up every time. The Exceptions
column (the one I want to total) is set to sum and value. When I execute the
query everything looks fine. I suspect that the text nature of the month
headings is changing the exceptions value to text. This would explain why the
current record will display but the column is not totaled. With this in mind
I have tried =Int([Jan]) in the form, but I suspect the underlying
recordsource is still text because the column is still not totaling.
:

You should be able to use a text box (or 12) in your form footer section with
a control source like:
=Sum([Your Field Name])

--
Duane Hookom
Microsoft Access MVP


:

I created a crosstab query to sum up numbers in a table by month; it works
fine. I am using this query as the recordsource to a form. This also works
fine because I have set the form to be continous, so I see all twenty rows
with their totals in each of the twelve columns. When you view the form it
becomes obvious that the monthly columns need to be totaled. This is the
problem. I can sum each row in the query, but how do I get the form to total
the twenty numbers in each monthly column. I have set the text box in the
footer to reference the text box in the detail section and all I get is the
first number. I need some sort of running sum option that you can do in
reports so I can get a column total on the form. Any suggestions will be most
appreciated.
Thanks
Michael
Click to expand...
Click to expand...
Click to expand...
 
I copied your sql into my Northwind and I created a form off that query and I
got the monthly column to total as desired. As far as the referencing is
concerned, the recordsource is three letters, Jan, Feb, but the text box is
labeled January, February, etc. In the footer I am referencing the
recordsource, =Sum([Jan]) so I don't see that as a cause. The boxes in the
footer still display error so there must be something goofy with the box or
form. I may rebuild the form from scratch, it is not that involved and there
is no code behind it. I don't want to take anymore of your time on something
that clearly is wierd on my end so I thank you for your efforts and your
help. If I figure out the cause later, I'll let you know.
Regards,
Michael

Duane Hookom said:
Your first post stated "I have set the text box in the footer to reference
the text box in the detail section". Are you really attempting to sum a text
box? This would not work. You can sum a field from the record source but not
a control.

I just created a form in Northwind based on the crosstab:
TRANSFORM Sum(Orders.Freight) AS SumOfFreight
SELECT Year([OrderDate]) AS Yr
FROM Orders
GROUP BY Year([OrderDate])
PIVOT Format([OrderDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I added text boxes in the form footer with control sources like:
=Sum([Jan])
This displayed the expected results.

--
Duane Hookom
Microsoft Access MVP


apollo8359 said:
TRANSFORM Sum([tbl Exceptions].Exceptions) AS SumOfExceptions
SELECT [tbl Exceptions].SourceID, [qry Source].SourceShort,
Format([Date],"yyyy") AS [Year], Sum([tbl Exceptions].Exceptions) AS
SourceTotal
FROM [tbl Exceptions] LEFT JOIN [qry Source] ON [tbl Exceptions].SourceID =
[qry Source].SourceID
GROUP BY [tbl Exceptions].SourceID, [qry Source].SourceShort,
Format([Date],"yyyy"), [qry Source].Order
ORDER BY [qry Source].Order
PIVOT Format([Date],"mmm");

This is it, let me know if you need anything else, Thanks.

Duane Hookom said:
Post the SQL view of your crosstab.
--
Duane Hookom
Microsoft Access MVP


:

In reading over my question I realize I must have been tired Friday night
because I left out an important part. I am already using the =Sum([Jan]) and
=Sum([Feb]), etc. for all twelve columns and it is giving me the value in
record one (or wherever I place the cursor) but not totaling the full list of
twenty items. I tested the sum formula in another database and it worked
fine, the column was totaled. I think the query is causing the problem.
In the crosstab to get a column for each month, I am using the formula Exp1:
Format([Date],"mmm") and changing the column headings to "Jan", "Feb", etc.
on the column heading option so all twelve show up every time. The Exceptions
column (the one I want to total) is set to sum and value. When I execute the
query everything looks fine. I suspect that the text nature of the month
headings is changing the exceptions value to text. This would explain why the
current record will display but the column is not totaled. With this in mind
I have tried =Int([Jan]) in the form, but I suspect the underlying
recordsource is still text because the column is still not totaling.
:

You should be able to use a text box (or 12) in your form footer section with
a control source like:
=Sum([Your Field Name])

--
Duane Hookom
Microsoft Access MVP


:

I created a crosstab query to sum up numbers in a table by month; it works
fine. I am using this query as the recordsource to a form. This also works
fine because I have set the form to be continous, so I see all twenty rows
with their totals in each of the twelve columns. When you view the form it
becomes obvious that the monthly columns need to be totaled. This is the
problem. I can sum each row in the query, but how do I get the form to total
the twenty numbers in each monthly column. I have set the text box in the
footer to reference the text box in the detail section and all I get is the
first number. I need some sort of running sum option that you can do in
reports so I can get a column total on the form. Any suggestions will be most
appreciated.
Thanks
Michael
Click to expand...
Click to expand...
Click to expand...
 
Back
Top