Report based on filtered crosstab

[Shaun]

Hi All,

I have a form acting as a menu for sites (continuous form so it looks like a
list). On this form I have a command button that opens 2 reports for that
site, Site Summary (simple query based, works fine) and Cell Summary
(crosstab based).

The crosstab query has a Where clause [Forms]![Site List menu]![Field_no] as
well as that in the Parameters dialog. The crosstab runs fine by itself, but
as soon as I try and open the report based on it using the button on the
form, I get "MS Jet db engine does not recognize " as a valid field name or
expression." Help please!

Thanks

 

[Duane Hookom]

We can't see how your crosstab query or report have been created. Did you set
the column headings property of the crosstab? Are you some how accomodating
dynamic columns in your crosstab?

 

[Shaun]

The column headings property is set to the results of a Union query. No I'm
not handling the dynamic columns in the report because I don't know how (that
was going to be another post probably). Guessing the lack of dynamic columns
is part of the problem? Below is the SQL for the crosstab (sorry its so
ugly).

PARAMETERS [Forms]![Site List menu]![Field_no] Text ( 255 );
TRANSFORM Sum(qry_ArtifactUnion.Count) AS SumOfCount
SELECT TRU.Northing, TRU.Easting, Concatenate("SELECT Field_Number FROM
Features WHERE TRU_Northing =" & [Northing] & " And TRU_Easting =" &
[Easting]) AS Features, First(TRU.FCR_Count) AS FCR, First(Comment.Comment)
AS Comment
FROM ((Site_boundary RIGHT JOIN ((TRU LEFT JOIN qry_ArtifactUnion ON
(TRU.Easting = qry_ArtifactUnion.TRU_Easting) AND (TRU.Northing =
qry_ArtifactUnion.TRU_Northing)) LEFT JOIN lnk_TruSite ON (TRU.Easting =
lnk_TruSite.TRU_Easting) AND (TRU.Northing = lnk_TruSite.TRU_Northing)) ON
Site_boundary.Id = lnk_TruSite.SiteID) LEFT JOIN Comment ON (TRU.Date =
Comment.Date) AND (TRU.Initials = Comment.Initials) AND (TRU.Easting =
Comment.TRU_Easting) AND (TRU.Northing = Comment.TRU_Northing)) LEFT JOIN
Features ON (TRU.Date = Features.Date) AND (TRU.Initials = Features.Initials)
AND (TRU.Easting = Features.TRU_Easting) AND (TRU.Northing =
Features.TRU_Northing)
WHERE (((Site_boundary.Field_no)=[Forms]![Site List menu]![Field_no]))
GROUP BY TRU.Northing, TRU.Easting
PIVOT qry_ArtifactUnion.Type;

We can't see how your crosstab query or report have been created. Did you set
the column headings property of the crosstab? Are you some how accomodating
dynamic columns in your crosstab?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Hi All,

I have a form acting as a menu for sites (continuous form so it looks like a
list). On this form I have a command button that opens 2 reports for that
site, Site Summary (simple query based, works fine) and Cell Summary
(crosstab based).

The crosstab query has a Where clause [Forms]![Site List menu]![Field_no] as
well as that in the Parameters dialog. The crosstab runs fine by itself, but
as soon as I try and open the report based on it using the button on the
form, I get "MS Jet db engine does not recognize " as a valid field name or
expression." Help please!

Thanks

 

[Duane Hookom]

The Concatenate() function looks a bit familiar. I expect your [Type] values
will be a fairly static list. If so, you can set the Column Headings property
of the crosstab to all possible [Type] values.

I'm not sure if this will clear your issue but will be a step in the right
direction.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

The column headings property is set to the results of a Union query. No I'm
not handling the dynamic columns in the report because I don't know how (that
was going to be another post probably). Guessing the lack of dynamic columns
is part of the problem? Below is the SQL for the crosstab (sorry its so
ugly).

PARAMETERS [Forms]![Site List menu]![Field_no] Text ( 255 );
TRANSFORM Sum(qry_ArtifactUnion.Count) AS SumOfCount
SELECT TRU.Northing, TRU.Easting, Concatenate("SELECT Field_Number FROM
Features WHERE TRU_Northing =" & [Northing] & " And TRU_Easting =" &
[Easting]) AS Features, First(TRU.FCR_Count) AS FCR, First(Comment.Comment)
AS Comment
FROM ((Site_boundary RIGHT JOIN ((TRU LEFT JOIN qry_ArtifactUnion ON
(TRU.Easting = qry_ArtifactUnion.TRU_Easting) AND (TRU.Northing =
qry_ArtifactUnion.TRU_Northing)) LEFT JOIN lnk_TruSite ON (TRU.Easting =
lnk_TruSite.TRU_Easting) AND (TRU.Northing = lnk_TruSite.TRU_Northing)) ON
Site_boundary.Id = lnk_TruSite.SiteID) LEFT JOIN Comment ON (TRU.Date =
Comment.Date) AND (TRU.Initials = Comment.Initials) AND (TRU.Easting =
Comment.TRU_Easting) AND (TRU.Northing = Comment.TRU_Northing)) LEFT JOIN
Features ON (TRU.Date = Features.Date) AND (TRU.Initials = Features.Initials)
AND (TRU.Easting = Features.TRU_Easting) AND (TRU.Northing =
Features.TRU_Northing)
WHERE (((Site_boundary.Field_no)=[Forms]![Site List menu]![Field_no]))
GROUP BY TRU.Northing, TRU.Easting
PIVOT qry_ArtifactUnion.Type;

We can't see how your crosstab query or report have been created. Did you set
the column headings property of the crosstab? Are you some how accomodating
dynamic columns in your crosstab?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Hi All,

I have a form acting as a menu for sites (continuous form so it looks like a
list). On this form I have a command button that opens 2 reports for that
site, Site Summary (simple query based, works fine) and Cell Summary
(crosstab based).

The crosstab query has a Where clause [Forms]![Site List menu]![Field_no] as
well as that in the Parameters dialog. The crosstab runs fine by itself, but
as soon as I try and open the report based on it using the button on the
form, I get "MS Jet db engine does not recognize " as a valid field name or
expression." Help please!

Thanks

 

[Shaun]

Yeah I would hope it would look familiar. That's what I did for the query
(the Type values, although they vary quite a bit from site to site and it
would be nice to have the report not have the unused Types).

However, this doesn't help with the report (unless I'm missing something).
How do I get the report to show only the specific site?

The Concatenate() function looks a bit familiar. I expect your [Type] values
will be a fairly static list. If so, you can set the Column Headings property
of the crosstab to all possible [Type] values.

I'm not sure if this will clear your issue but will be a step in the right
direction.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

The column headings property is set to the results of a Union query. No I'm
not handling the dynamic columns in the report because I don't know how (that
was going to be another post probably). Guessing the lack of dynamic columns
is part of the problem? Below is the SQL for the crosstab (sorry its so
ugly).

PARAMETERS [Forms]![Site List menu]![Field_no] Text ( 255 );
TRANSFORM Sum(qry_ArtifactUnion.Count) AS SumOfCount
SELECT TRU.Northing, TRU.Easting, Concatenate("SELECT Field_Number FROM
Features WHERE TRU_Northing =" & [Northing] & " And TRU_Easting =" &
[Easting]) AS Features, First(TRU.FCR_Count) AS FCR, First(Comment.Comment)
AS Comment
FROM ((Site_boundary RIGHT JOIN ((TRU LEFT JOIN qry_ArtifactUnion ON
(TRU.Easting = qry_ArtifactUnion.TRU_Easting) AND (TRU.Northing =
qry_ArtifactUnion.TRU_Northing)) LEFT JOIN lnk_TruSite ON (TRU.Easting =
lnk_TruSite.TRU_Easting) AND (TRU.Northing = lnk_TruSite.TRU_Northing)) ON
Site_boundary.Id = lnk_TruSite.SiteID) LEFT JOIN Comment ON (TRU.Date =
Comment.Date) AND (TRU.Initials = Comment.Initials) AND (TRU.Easting =
Comment.TRU_Easting) AND (TRU.Northing = Comment.TRU_Northing)) LEFT JOIN
Features ON (TRU.Date = Features.Date) AND (TRU.Initials = Features.Initials)
AND (TRU.Easting = Features.TRU_Easting) AND (TRU.Northing =
Features.TRU_Northing)
WHERE (((Site_boundary.Field_no)=[Forms]![Site List menu]![Field_no]))
GROUP BY TRU.Northing, TRU.Easting
PIVOT qry_ArtifactUnion.Type;

We can't see how your crosstab query or report have been created. Did you set
the column headings property of the crosstab? Are you some how accomodating
dynamic columns in your crosstab?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi All,

I have a form acting as a menu for sites (continuous form so it looks like a
list). On this form I have a command button that opens 2 reports for that
site, Site Summary (simple query based, works fine) and Cell Summary
(crosstab based).

The crosstab query has a Where clause [Forms]![Site List menu]![Field_no] as
well as that in the Parameters dialog. The crosstab runs fine by itself, but
as soon as I try and open the report based on it using the button on the
form, I get "MS Jet db engine does not recognize " as a valid field name or
expression." Help please!

Thanks

 

[Duane Hookom]

If you have a need to report different [Type] values then you may need to try
the solution from the crosstab report sample found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Yeah I would hope it would look familiar. That's what I did for the query
(the Type values, although they vary quite a bit from site to site and it
would be nice to have the report not have the unused Types).

However, this doesn't help with the report (unless I'm missing something).
How do I get the report to show only the specific site?

The Concatenate() function looks a bit familiar. I expect your [Type] values
will be a fairly static list. If so, you can set the Column Headings property
of the crosstab to all possible [Type] values.

I'm not sure if this will clear your issue but will be a step in the right
direction.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

The column headings property is set to the results of a Union query. No I'm
not handling the dynamic columns in the report because I don't know how (that
was going to be another post probably). Guessing the lack of dynamic columns
is part of the problem? Below is the SQL for the crosstab (sorry its so
ugly).

PARAMETERS [Forms]![Site List menu]![Field_no] Text ( 255 );
TRANSFORM Sum(qry_ArtifactUnion.Count) AS SumOfCount
SELECT TRU.Northing, TRU.Easting, Concatenate("SELECT Field_Number FROM
Features WHERE TRU_Northing =" & [Northing] & " And TRU_Easting =" &
[Easting]) AS Features, First(TRU.FCR_Count) AS FCR, First(Comment.Comment)
AS Comment
FROM ((Site_boundary RIGHT JOIN ((TRU LEFT JOIN qry_ArtifactUnion ON
(TRU.Easting = qry_ArtifactUnion.TRU_Easting) AND (TRU.Northing =
qry_ArtifactUnion.TRU_Northing)) LEFT JOIN lnk_TruSite ON (TRU.Easting =
lnk_TruSite.TRU_Easting) AND (TRU.Northing = lnk_TruSite.TRU_Northing)) ON
Site_boundary.Id = lnk_TruSite.SiteID) LEFT JOIN Comment ON (TRU.Date =
Comment.Date) AND (TRU.Initials = Comment.Initials) AND (TRU.Easting =
Comment.TRU_Easting) AND (TRU.Northing = Comment.TRU_Northing)) LEFT JOIN
Features ON (TRU.Date = Features.Date) AND (TRU.Initials = Features.Initials)
AND (TRU.Easting = Features.TRU_Easting) AND (TRU.Northing =
Features.TRU_Northing)
WHERE (((Site_boundary.Field_no)=[Forms]![Site List menu]![Field_no]))
GROUP BY TRU.Northing, TRU.Easting
PIVOT qry_ArtifactUnion.Type;

:

We can't see how your crosstab query or report have been created. Did you set
the column headings property of the crosstab? Are you some how accomodating
dynamic columns in your crosstab?

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

Hi All,

I have a form acting as a menu for sites (continuous form so it looks like a
list). On this form I have a command button that opens 2 reports for that
site, Site Summary (simple query based, works fine) and Cell Summary
(crosstab based).

The crosstab query has a Where clause [Forms]![Site List menu]![Field_no] as
well as that in the Parameters dialog. The crosstab runs fine by itself, but
as soon as I try and open the report based on it using the button on the
form, I get "MS Jet db engine does not recognize " as a valid field name or
expression." Help please!

Thanks