Crosstab Query Help!

  • Thread starter Thread starter Kash
  • Start date Start date
K

Kash

Hello,

I am trying to design a crosstab query in MS Access 97 &
it wont allow me to have any sort of criteria in it. How
would I be able to add criteria in sucha query? Please
advise..
 
Post your SQL and describe what error messages are being displayed that
prevent you from adding criteria.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I am making a crosstable query out of the following query:

Query Name: Qr_VM_ZoneReport_ZoneTop15

SELECT Tb_VM_ZoneReports_Summ.Year,
Tb_VM_ZoneReports_Summ.Week,
Tb_VM_ZoneReports_Summ.Joint_Name,
Tb_VM_ZoneReports_Summ.Zone, Tb_VM_ZoneReports_Summ.Bldg,
Tb_VM_ZoneReports_Summ.Requests,
Tb_VM_ZoneReports_Summ.Request_Area, DD_II_Weeks.Start_DT,
DD_II_Weeks.End_DT, Tb_VM_ZoneReports_Summ.Res_Count AS
T_Res, Tb_ZoneReport_Week_Loc_Zone.Total AS T_Req_Area,
Tb_ZoneReport_Week_Loc_Zone.[Top 15 Total] AS T_Zone,
Tb_ZoneReport_Week_Loc_Zone.[Total Loc] AS T_Loc
FROM (Tb_VM_ZoneReports_Summ INNER JOIN DD_II_Weeks ON
(Tb_VM_ZoneReports_Summ.Year = DD_II_Weeks.Week_Year) AND
(Tb_VM_ZoneReports_Summ.Week = DD_II_Weeks.Week_No)) INNER
JOIN Tb_ZoneReport_Week_Loc_Zone ON
(Tb_VM_ZoneReports_Summ.Zone =
Tb_ZoneReport_Week_Loc_Zone.Zone) AND
(Tb_VM_ZoneReports_Summ.Year =
Tb_ZoneReport_Week_Loc_Zone.Year) AND
(Tb_VM_ZoneReports_Summ.Week =
Tb_ZoneReport_Week_Loc_Zone.Week) AND
(Tb_VM_ZoneReports_Summ.Joint_Name =
Tb_ZoneReport_Week_Loc_Zone.Joint_Name) AND
(Tb_VM_ZoneReports_Summ.Request_Area =
Tb_ZoneReport_Week_Loc_Zone.Request_Area)
WHERE (((Tb_VM_ZoneReports_Summ.Year)=[Enter the
correct year below (yyyy)]) AND
((Tb_VM_ZoneReports_Summ.Week)=[Your report is for which
week?]))
ORDER BY Tb_VM_ZoneReports_Summ.Year,
Tb_VM_ZoneReports_Summ.Week,
Tb_VM_ZoneReports_Summ.Joint_Name,
Tb_VM_ZoneReports_Summ.Zone, Tb_VM_ZoneReports_Summ.Bldg,
Tb_VM_ZoneReports_Summ.Requests DESC ,
Tb_VM_ZoneReports_Summ.Request_Area;

Following is the sql for the crosstab query:

TRANSFORM Count(Qr_VM_ZoneReport_ZoneTop15.Requests) AS
[The Value]
SELECT Qr_VM_ZoneReport_ZoneTop15.Request_Area, Count
(Qr_VM_ZoneReport_ZoneTop15.Requests) AS [Total Of
Requests]
FROM Qr_VM_ZoneReport_ZoneTop15
GROUP BY Qr_VM_ZoneReport_ZoneTop15.Request_Area
PIVOT Qr_VM_ZoneReport_ZoneTop15.Bldg;

When I try to run the query it gives me the following
error:

The Microsoft Jet database engine does not
recognise '[Enter the correct year below (yyyy)]' as a
field name or expression

Now, if I remove all the criterias then it works fine but
doesnt serve my needs.

This is what I am trying to do:

I am building a report in which the "Request_Area" should
come in the rows & "bldg" goes horizontally across in
columns & somehow I have to get it by "Zone". Please let
me know.

Thanks,

Kash

______________________________________________
 
You must specify the parameters in the query. In must cases, Access does not
require you to declare your parameters, but with crosstab queries it is
REQUIRED. Try adding the following to your query (I've guessed as to the type
of your parameters).

Query Name: Qr_VM_ZoneReport_ZoneTop15

Parameters [Enter the correct year below (yyyy)] Long,
[Your report is for which week?] Long;

SELECT Tb_VM_ZoneReports_Summ.Year,
Tb_VM_ZoneReports_Summ.Week, ...

I am making a crosstable query out of the following query:

Query Name: Qr_VM_ZoneReport_ZoneTop15

SELECT Tb_VM_ZoneReports_Summ.Year,
Tb_VM_ZoneReports_Summ.Week,
Tb_VM_ZoneReports_Summ.Joint_Name,
Tb_VM_ZoneReports_Summ.Zone, Tb_VM_ZoneReports_Summ.Bldg,
Tb_VM_ZoneReports_Summ.Requests,
Tb_VM_ZoneReports_Summ.Request_Area, DD_II_Weeks.Start_DT,
DD_II_Weeks.End_DT, Tb_VM_ZoneReports_Summ.Res_Count AS
T_Res, Tb_ZoneReport_Week_Loc_Zone.Total AS T_Req_Area,
Tb_ZoneReport_Week_Loc_Zone.[Top 15 Total] AS T_Zone,
Tb_ZoneReport_Week_Loc_Zone.[Total Loc] AS T_Loc
FROM (Tb_VM_ZoneReports_Summ INNER JOIN DD_II_Weeks ON
(Tb_VM_ZoneReports_Summ.Year = DD_II_Weeks.Week_Year) AND
(Tb_VM_ZoneReports_Summ.Week = DD_II_Weeks.Week_No)) INNER
JOIN Tb_ZoneReport_Week_Loc_Zone ON
(Tb_VM_ZoneReports_Summ.Zone =
Tb_ZoneReport_Week_Loc_Zone.Zone) AND
(Tb_VM_ZoneReports_Summ.Year =
Tb_ZoneReport_Week_Loc_Zone.Year) AND
(Tb_VM_ZoneReports_Summ.Week =
Tb_ZoneReport_Week_Loc_Zone.Week) AND
(Tb_VM_ZoneReports_Summ.Joint_Name =
Tb_ZoneReport_Week_Loc_Zone.Joint_Name) AND
(Tb_VM_ZoneReports_Summ.Request_Area =
Tb_ZoneReport_Week_Loc_Zone.Request_Area)
WHERE (((Tb_VM_ZoneReports_Summ.Year)=[Enter the
correct year below (yyyy)]) AND
((Tb_VM_ZoneReports_Summ.Week)=[Your report is for which
week?]))
ORDER BY Tb_VM_ZoneReports_Summ.Year,
Tb_VM_ZoneReports_Summ.Week,
Tb_VM_ZoneReports_Summ.Joint_Name,
Tb_VM_ZoneReports_Summ.Zone, Tb_VM_ZoneReports_Summ.Bldg,
Tb_VM_ZoneReports_Summ.Requests DESC ,
Tb_VM_ZoneReports_Summ.Request_Area;

Following is the sql for the crosstab query:

TRANSFORM Count(Qr_VM_ZoneReport_ZoneTop15.Requests) AS
[The Value]
SELECT Qr_VM_ZoneReport_ZoneTop15.Request_Area, Count
(Qr_VM_ZoneReport_ZoneTop15.Requests) AS [Total Of
Requests]
FROM Qr_VM_ZoneReport_ZoneTop15
GROUP BY Qr_VM_ZoneReport_ZoneTop15.Request_Area
PIVOT Qr_VM_ZoneReport_ZoneTop15.Bldg;

When I try to run the query it gives me the following
error:

The Microsoft Jet database engine does not
recognise '[Enter the correct year below (yyyy)]' as a
field name or expression

Now, if I remove all the criterias then it works fine but
doesnt serve my needs.

This is what I am trying to do:

I am building a report in which the "Request_Area" should
come in the rows & "bldg" goes horizontally across in
columns & somehow I have to get it by "Zone". Please let
me know.

Thanks,

Kash

______________________________________________
-----Original Message-----
Post your SQL and describe what error messages are being displayed that
prevent you from adding criteria.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-




.
Click to expand...
Click to expand...
 
Crosstab queries require you to explicitly define the data type of all
parameters. Select Query|Parameters and enter
[Enter the correct year below (yyyy)] Integer
[Your report is for which week?] Integer

--
Duane Hookom
MS Access MVP


I am making a crosstable query out of the following query:

Query Name: Qr_VM_ZoneReport_ZoneTop15

SELECT Tb_VM_ZoneReports_Summ.Year,
Tb_VM_ZoneReports_Summ.Week,
Tb_VM_ZoneReports_Summ.Joint_Name,
Tb_VM_ZoneReports_Summ.Zone, Tb_VM_ZoneReports_Summ.Bldg,
Tb_VM_ZoneReports_Summ.Requests,
Tb_VM_ZoneReports_Summ.Request_Area, DD_II_Weeks.Start_DT,
DD_II_Weeks.End_DT, Tb_VM_ZoneReports_Summ.Res_Count AS
T_Res, Tb_ZoneReport_Week_Loc_Zone.Total AS T_Req_Area,
Tb_ZoneReport_Week_Loc_Zone.[Top 15 Total] AS T_Zone,
Tb_ZoneReport_Week_Loc_Zone.[Total Loc] AS T_Loc
FROM (Tb_VM_ZoneReports_Summ INNER JOIN DD_II_Weeks ON
(Tb_VM_ZoneReports_Summ.Year = DD_II_Weeks.Week_Year) AND
(Tb_VM_ZoneReports_Summ.Week = DD_II_Weeks.Week_No)) INNER
JOIN Tb_ZoneReport_Week_Loc_Zone ON
(Tb_VM_ZoneReports_Summ.Zone =
Tb_ZoneReport_Week_Loc_Zone.Zone) AND
(Tb_VM_ZoneReports_Summ.Year =
Tb_ZoneReport_Week_Loc_Zone.Year) AND
(Tb_VM_ZoneReports_Summ.Week =
Tb_ZoneReport_Week_Loc_Zone.Week) AND
(Tb_VM_ZoneReports_Summ.Joint_Name =
Tb_ZoneReport_Week_Loc_Zone.Joint_Name) AND
(Tb_VM_ZoneReports_Summ.Request_Area =
Tb_ZoneReport_Week_Loc_Zone.Request_Area)
WHERE (((Tb_VM_ZoneReports_Summ.Year)=[Enter the
correct year below (yyyy)]) AND
((Tb_VM_ZoneReports_Summ.Week)=[Your report is for which
week?]))
ORDER BY Tb_VM_ZoneReports_Summ.Year,
Tb_VM_ZoneReports_Summ.Week,
Tb_VM_ZoneReports_Summ.Joint_Name,
Tb_VM_ZoneReports_Summ.Zone, Tb_VM_ZoneReports_Summ.Bldg,
Tb_VM_ZoneReports_Summ.Requests DESC ,
Tb_VM_ZoneReports_Summ.Request_Area;

Following is the sql for the crosstab query:

TRANSFORM Count(Qr_VM_ZoneReport_ZoneTop15.Requests) AS
[The Value]
SELECT Qr_VM_ZoneReport_ZoneTop15.Request_Area, Count
(Qr_VM_ZoneReport_ZoneTop15.Requests) AS [Total Of
Requests]
FROM Qr_VM_ZoneReport_ZoneTop15
GROUP BY Qr_VM_ZoneReport_ZoneTop15.Request_Area
PIVOT Qr_VM_ZoneReport_ZoneTop15.Bldg;

When I try to run the query it gives me the following
error:

The Microsoft Jet database engine does not
recognise '[Enter the correct year below (yyyy)]' as a
field name or expression

Now, if I remove all the criterias then it works fine but
doesnt serve my needs.

This is what I am trying to do:

I am building a report in which the "Request_Area" should
come in the rows & "bldg" goes horizontally across in
columns & somehow I have to get it by "Zone". Please let
me know.

Thanks,

Kash

______________________________________________

-----Original Message-----
Post your SQL and describe what error messages are being displayed that
prevent you from adding criteria.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-




.
Click to expand...
Click to expand...
 
Back
Top