update cross tab query in a report

  • Thread starter Thread starter Kelly
  • Start date Start date
K

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, 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


Duane Hookom said:
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


Kelly said:
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
Click to expand...
Click to expand...
 
If you only want to use a single CircRevisionNum then you need a simple
totals query, not a crosstab.

--
Duane Hookom
Microsoft Access MVP


Kelly said:
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


Duane Hookom said:
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


Kelly said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


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

--
Duane Hookom
Microsoft Access MVP


Kelly said:
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


Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Kelly said:
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


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

--
Duane Hookom
Microsoft Access MVP


Kelly said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Duane Hookom said:
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


Kelly said:
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


Duane Hookom said:
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
Click to expand...
Click to expand...
Click to expand...
 
Glad to hear you got this sorted out.

--
Duane Hookom
Microsoft Access MVP


Kelly said:
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


Duane Hookom said:
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


Kelly said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top