update cross tab query in a report

[Kelly]

Does anyone know how I can resovle this problem. I created a crosstab query
to provide totals to each mark number. The CableCalcQry query calculates the
MarkCalc expression based on number of conductors multiplied by the length of
cable. The MarkListing is that sum of based on each mark number. The report
needs to be based on which revision totals. The report is fine if I use
current data to build the report. If I add an additional revision it does not
show. How can I programmatically add additional revisions to the report and
only have the revision I need for the totals. Below is the cross tab query.

TRANSFORM Sum(CableCalcQry.MarkCalc) AS MarkListing
SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
PIVOT CableCalcQry.CircRevisionNum;

I also tried adding a parameter to the cross tab query that did not work in
the report but worked when I ran the query. Confused....
PARAMETERS [Which Revision] IEEEDouble, [CableCalcQry].[CircRevisionNum]
IEEEDouble;

Is there a better way to get the results.

Kelly

 

[Duane Hookom]

There is a sample of creating reports from dynamic crosstabs at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11.

 

[Kelly]

Duane, thanks for the reply. I tried using the format and could create the
column headings that solved part of the problem, but my situation is instead
of letting the function create the column heading I am selecting a field
"CircRevisonNum" to supply the seperated columns. I need to specify just one
column from this like if I only want revision 2 to be displayed in a colum.
The cross tab query is great but if you try to set a criteria it doesn't like
that, even if you set the parameter in the query to select a specific revison
number. do you have any more suggestions..

Kelly

There is a sample of creating reports from dynamic crosstabs at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11.

--
Duane Hookom
Microsoft Access MVP

Does anyone know how I can resovle this problem. I created a crosstab query
to provide totals to each mark number. The CableCalcQry query calculates the
MarkCalc expression based on number of conductors multiplied by the length of
cable. The MarkListing is that sum of based on each mark number. The report
needs to be based on which revision totals. The report is fine if I use
current data to build the report. If I add an additional revision it does not
show. How can I programmatically add additional revisions to the report and
only have the revision I need for the totals. Below is the cross tab query.

TRANSFORM Sum(CableCalcQry.MarkCalc) AS MarkListing
SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
PIVOT CableCalcQry.CircRevisionNum;

I also tried adding a parameter to the cross tab query that did not work in
the report but worked when I ran the query. Confused....
PARAMETERS [Which Revision] IEEEDouble, [CableCalcQry].[CircRevisionNum]
IEEEDouble;

Is there a better way to get the results.

Kelly

 

[Duane Hookom]

If you only want to use a single CircRevisionNum then you need a simple
totals query, not a crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane, thanks for the reply. I tried using the format and could create the
column headings that solved part of the problem, but my situation is instead
of letting the function create the column heading I am selecting a field
"CircRevisonNum" to supply the seperated columns. I need to specify just one
column from this like if I only want revision 2 to be displayed in a colum.
The cross tab query is great but if you try to set a criteria it doesn't like
that, even if you set the parameter in the query to select a specific revison
number. do you have any more suggestions..

Kelly

There is a sample of creating reports from dynamic crosstabs at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11.

--
Duane Hookom
Microsoft Access MVP

Does anyone know how I can resovle this problem. I created a crosstab query
to provide totals to each mark number. The CableCalcQry query calculates the
MarkCalc expression based on number of conductors multiplied by the length of
cable. The MarkListing is that sum of based on each mark number. The report
needs to be based on which revision totals. The report is fine if I use
current data to build the report. If I add an additional revision it does not
show. How can I programmatically add additional revisions to the report and
only have the revision I need for the totals. Below is the cross tab query.

TRANSFORM Sum(CableCalcQry.MarkCalc) AS MarkListing
SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
PIVOT CableCalcQry.CircRevisionNum;

I also tried adding a parameter to the cross tab query that did not work in
the report but worked when I ran the query. Confused....
PARAMETERS [Which Revision] IEEEDouble, [CableCalcQry].[CircRevisionNum]
IEEEDouble;

Is there a better way to get the results.

Kelly

 

[Kelly]

It's not that simple. I have a query that is calculating number of conductors
multiplied by the length of conductors field expression "MarkCalc". The
conductors have 21 different mark numbers assigned to them. So Then I have a
query with a field expression "MarkListing" that sums the mark numbers based
on revision number. This is why the cross tab worked well to show the mark
numbers as row headings and the revision number as column headings and the
"MarkListing" as the value. I just need it to allow me to select just one of
the revisions for the report.
Kelly

If you only want to use a single CircRevisionNum then you need a simple
totals query, not a crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane, thanks for the reply. I tried using the format and could create the
column headings that solved part of the problem, but my situation is instead
of letting the function create the column heading I am selecting a field
"CircRevisonNum" to supply the seperated columns. I need to specify just one
column from this like if I only want revision 2 to be displayed in a colum.
The cross tab query is great but if you try to set a criteria it doesn't like
that, even if you set the parameter in the query to select a specific revison
number. do you have any more suggestions..

Kelly

There is a sample of creating reports from dynamic crosstabs at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11.

--
Duane Hookom
Microsoft Access MVP


:

Does anyone know how I can resovle this problem. I created a crosstab query
to provide totals to each mark number. The CableCalcQry query calculates the
MarkCalc expression based on number of conductors multiplied by the length of
cable. The MarkListing is that sum of based on each mark number. The report
needs to be based on which revision totals. The report is fine if I use
current data to build the report. If I add an additional revision it does not
show. How can I programmatically add additional revisions to the report and
only have the revision I need for the totals. Below is the cross tab query.

TRANSFORM Sum(CableCalcQry.MarkCalc) AS MarkListing
SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
PIVOT CableCalcQry.CircRevisionNum;

I also tried adding a parameter to the cross tab query that did not work in
the report but worked when I ran the query. Confused....
PARAMETERS [Which Revision] IEEEDouble, [CableCalcQry].[CircRevisionNum]
IEEEDouble;

Is there a better way to get the results.

Kelly

 

[Duane Hookom]

I'm still not convinced you couldn't use a query with SQL of:

SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum,
Sum(CableCalcQry.MarkCalc) AS MarkListing
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum;

Just add a criteria after the FROM clause to select a specific
CircRevisionNum.

--
Duane Hookom
Microsoft Access MVP

It's not that simple. I have a query that is calculating number of conductors
multiplied by the length of conductors field expression "MarkCalc". The
conductors have 21 different mark numbers assigned to them. So Then I have a
query with a field expression "MarkListing" that sums the mark numbers based
on revision number. This is why the cross tab worked well to show the mark
numbers as row headings and the revision number as column headings and the
"MarkListing" as the value. I just need it to allow me to select just one of
the revisions for the report.
Kelly

If you only want to use a single CircRevisionNum then you need a simple
totals query, not a crosstab.

--
Duane Hookom
Microsoft Access MVP

Duane, thanks for the reply. I tried using the format and could create the
column headings that solved part of the problem, but my situation is instead
of letting the function create the column heading I am selecting a field
"CircRevisonNum" to supply the seperated columns. I need to specify just one
column from this like if I only want revision 2 to be displayed in a colum.
The cross tab query is great but if you try to set a criteria it doesn't like
that, even if you set the parameter in the query to select a specific revison
number. do you have any more suggestions..

Kelly


:

There is a sample of creating reports from dynamic crosstabs at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11.

--
Duane Hookom
Microsoft Access MVP


:

Does anyone know how I can resovle this problem. I created a crosstab query
to provide totals to each mark number. The CableCalcQry query calculates the
MarkCalc expression based on number of conductors multiplied by the length of
cable. The MarkListing is that sum of based on each mark number. The report
needs to be based on which revision totals. The report is fine if I use
current data to build the report. If I add an additional revision it does not
show. How can I programmatically add additional revisions to the report and
only have the revision I need for the totals. Below is the cross tab query.

TRANSFORM Sum(CableCalcQry.MarkCalc) AS MarkListing
SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
PIVOT CableCalcQry.CircRevisionNum;

I also tried adding a parameter to the cross tab query that did not work in
the report but worked when I ran the query. Confused....
PARAMETERS [Which Revision] IEEEDouble, [CableCalcQry].[CircRevisionNum]
IEEEDouble;

Is there a better way to get the results.

Kelly

 

[Kelly]

It pays to ask the experts. Thanks Duane. I was trying to group on the
MarkCalc field instead of sum. And add an additional query to get my results
because of the mark numbers. Also I thought I needed the CircuitID
information that's where I ended up with a mess.This works great. I really
apprecaite your help.
Kelly

I'm still not convinced you couldn't use a query with SQL of:

SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum,
Sum(CableCalcQry.MarkCalc) AS MarkListing
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum;

Just add a criteria after the FROM clause to select a specific
CircRevisionNum.

--
Duane Hookom
Microsoft Access MVP

It's not that simple. I have a query that is calculating number of conductors
multiplied by the length of conductors field expression "MarkCalc". The
conductors have 21 different mark numbers assigned to them. So Then I have a
query with a field expression "MarkListing" that sums the mark numbers based
on revision number. This is why the cross tab worked well to show the mark
numbers as row headings and the revision number as column headings and the
"MarkListing" as the value. I just need it to allow me to select just one of
the revisions for the report.
Kelly

If you only want to use a single CircRevisionNum then you need a simple
totals query, not a crosstab.

--
Duane Hookom
Microsoft Access MVP


:

Duane, thanks for the reply. I tried using the format and could create the
column headings that solved part of the problem, but my situation is instead
of letting the function create the column heading I am selecting a field
"CircRevisonNum" to supply the seperated columns. I need to specify just one
column from this like if I only want revision 2 to be displayed in a colum.
The cross tab query is great but if you try to set a criteria it doesn't like
that, even if you set the parameter in the query to select a specific revison
number. do you have any more suggestions..

Kelly


:

There is a sample of creating reports from dynamic crosstabs at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11.

--
Duane Hookom
Microsoft Access MVP


:

Does anyone know how I can resovle this problem. I created a crosstab query
to provide totals to each mark number. The CableCalcQry query calculates the
MarkCalc expression based on number of conductors multiplied by the length of
cable. The MarkListing is that sum of based on each mark number. The report
needs to be based on which revision totals. The report is fine if I use
current data to build the report. If I add an additional revision it does not
show. How can I programmatically add additional revisions to the report and
only have the revision I need for the totals. Below is the cross tab query.

TRANSFORM Sum(CableCalcQry.MarkCalc) AS MarkListing
SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
PIVOT CableCalcQry.CircRevisionNum;

I also tried adding a parameter to the cross tab query that did not work in
the report but worked when I ran the query. Confused....
PARAMETERS [Which Revision] IEEEDouble, [CableCalcQry].[CircRevisionNum]
IEEEDouble;

Is there a better way to get the results.

Kelly

 

[Duane Hookom]

Glad to hear you got this sorted out.

--
Duane Hookom
Microsoft Access MVP

It pays to ask the experts. Thanks Duane. I was trying to group on the
MarkCalc field instead of sum. And add an additional query to get my results
because of the mark numbers. Also I thought I needed the CircuitID
information that's where I ended up with a mess.This works great. I really
apprecaite your help.
Kelly

I'm still not convinced you couldn't use a query with SQL of:

SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum,
Sum(CableCalcQry.MarkCalc) AS MarkListing
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum;

Just add a criteria after the FROM clause to select a specific
CircRevisionNum.

--
Duane Hookom
Microsoft Access MVP

It's not that simple. I have a query that is calculating number of conductors
multiplied by the length of conductors field expression "MarkCalc". The
conductors have 21 different mark numbers assigned to them. So Then I have a
query with a field expression "MarkListing" that sums the mark numbers based
on revision number. This is why the cross tab worked well to show the mark
numbers as row headings and the revision number as column headings and the
"MarkListing" as the value. I just need it to allow me to select just one of
the revisions for the report.
Kelly


:

If you only want to use a single CircRevisionNum then you need a simple
totals query, not a crosstab.

--
Duane Hookom
Microsoft Access MVP


:

Duane, thanks for the reply. I tried using the format and could create the
column headings that solved part of the problem, but my situation is instead
of letting the function create the column heading I am selecting a field
"CircRevisonNum" to supply the seperated columns. I need to specify just one
column from this like if I only want revision 2 to be displayed in a colum.
The cross tab query is great but if you try to set a criteria it doesn't like
that, even if you set the parameter in the query to select a specific revison
number. do you have any more suggestions..

Kelly


:

There is a sample of creating reports from dynamic crosstabs at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=11.

--
Duane Hookom
Microsoft Access MVP


:

Does anyone know how I can resovle this problem. I created a crosstab query
to provide totals to each mark number. The CableCalcQry query calculates the
MarkCalc expression based on number of conductors multiplied by the length of
cable. The MarkListing is that sum of based on each mark number. The report
needs to be based on which revision totals. The report is fine if I use
current data to build the report. If I add an additional revision it does not
show. How can I programmatically add additional revisions to the report and
only have the revision I need for the totals. Below is the cross tab query.

TRANSFORM Sum(CableCalcQry.MarkCalc) AS MarkListing
SELECT Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
FROM (Cables INNER JOIN CircuitsData ON Cables.CableID =
CircuitsData.DescripID) INNER JOIN CableCalcQry ON CircuitsData.CircID =
CableCalcQry.CircID
GROUP BY Cables.MarkNum, Cables.CableDescrip, Cables.CommodNum
PIVOT CableCalcQry.CircRevisionNum;

I also tried adding a parameter to the cross tab query that did not work in
the report but worked when I ran the query. Confused....
PARAMETERS [Which Revision] IEEEDouble, [CableCalcQry].[CircRevisionNum]
IEEEDouble;

Is there a better way to get the results.

Kelly