Parameter Query

  • Thread starter Thread starter Deb
  • Start date Start date
D

Deb

I finally got a query to pull exactly what I wanted from the table, however
the query is based on a crosstab query and a select query. I'm trying to
make it a parameter query using beginning and ending report dates.

Crosstab Query is as follows:

TRANSFORM Sum(tbl_History.DRMOTrans) AS DRMO
SELECT tbl_History.Division, tbl_History.Program
FROM tbl_History
GROUP BY tbl_History.Division, tbl_History.Program
PIVOT tbl_History.ShippingPriority;

Second query is as follows:

SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE (((tbl_History.DRMOTrans) Is Not Null)) OR (((tbl_History.SHPTrans) Is
Not Null))
ORDER BY tbl_History.Division, tbl_History.Program;

The query that is working with the exception of the date parameters is:

PARAMETERS [Enter Report Start Date:] DateTime, [Enter Report End Date]
DateTime;
SELECT DISTINCTROW sqry_MthDrmPriority.Division,
sqry_MthDrmPriority.Program, sqry_MthDrmPriority.[1],
sqry_MthDrmPriority.[2], sqry_MthDrmPriority.[3], sqry_MthDrmPriority.CASREP,
sqry_MthDrmPriority.DTO, Sum(sqry_MthRpt1.DRMOTrans) AS SumOfDRMOTrans,
Sum([UnitCost]*[DRMOTrans]) AS SumOfUnitCost, sqry_MthRpt1.TransactionDate
FROM sqry_MthDrmPriority INNER JOIN sqry_MthRpt1 ON
(sqry_MthDrmPriority.Program = sqry_MthRpt1.Program) AND
(sqry_MthDrmPriority.Division = sqry_MthRpt1.Division)
GROUP BY sqry_MthDrmPriority.Division, sqry_MthDrmPriority.Program,
sqry_MthDrmPriority.[1], sqry_MthDrmPriority.[2], sqry_MthDrmPriority.[3],
sqry_MthDrmPriority.CASREP, sqry_MthDrmPriority.DTO,
sqry_MthRpt1.TransactionDate
HAVING (((sqry_MthDrmPriority.[1]) Is Not Null)) OR
(((sqry_MthDrmPriority.[2]) Is Not Null)) OR (((sqry_MthDrmPriority.[3]) Is
Not Null)) OR (((sqry_MthDrmPriority.CASREP) Is Not Null)) OR
(((sqry_MthDrmPriority.DTO) Is Not Null))
ORDER BY sqry_MthDrmPriority.Division, sqry_MthDrmPriority.Program;

Accomplished this using the Query Dialog Box, which I looked up in the
Microsoft Help site. The site also said that I needed to "Set the
ColumnHeadings property for the query that contains the parameter". How do I
go about setting the ColumnHeadings property for this query? Once that is
properly set, will the query pull only the data within the specified date
range?
 
Q) How do I go about setting the ColumnHeadings property for this query?
A) View the properties dialog in the crosstab and you will see it. Enter all
possible values of the column headings.

Q) Once that is properly set, will the query pull only the data within the
specified date range?
A) No, you need to add your parameters to the criteria like:
Between [Enter Report Start Date:] and [Enter Report End Date]

IMO, you really should use controls on a form rather than parameter prompts.

--
Duane Hookom
Microsoft Access MVP


Deb said:
I finally got a query to pull exactly what I wanted from the table, however
the query is based on a crosstab query and a select query. I'm trying to
make it a parameter query using beginning and ending report dates.

Crosstab Query is as follows:

TRANSFORM Sum(tbl_History.DRMOTrans) AS DRMO
SELECT tbl_History.Division, tbl_History.Program
FROM tbl_History
GROUP BY tbl_History.Division, tbl_History.Program
PIVOT tbl_History.ShippingPriority;

Second query is as follows:

SELECT tbl_History.Division, tbl_History.Program,
tbl_History.ShippingPriority, tbl_History.DRMOTrans, tbl_History.SHPTrans,
tbl_History.TransactionDate, tbl_History.UnitCost, tbl_History.EMR,
tbl_History.Requisition
FROM tbl_History
WHERE (((tbl_History.DRMOTrans) Is Not Null)) OR (((tbl_History.SHPTrans) Is
Not Null))
ORDER BY tbl_History.Division, tbl_History.Program;

The query that is working with the exception of the date parameters is:

PARAMETERS [Enter Report Start Date:] DateTime, [Enter Report End Date]
DateTime;
SELECT DISTINCTROW sqry_MthDrmPriority.Division,
sqry_MthDrmPriority.Program, sqry_MthDrmPriority.[1],
sqry_MthDrmPriority.[2], sqry_MthDrmPriority.[3], sqry_MthDrmPriority.CASREP,
sqry_MthDrmPriority.DTO, Sum(sqry_MthRpt1.DRMOTrans) AS SumOfDRMOTrans,
Sum([UnitCost]*[DRMOTrans]) AS SumOfUnitCost, sqry_MthRpt1.TransactionDate
FROM sqry_MthDrmPriority INNER JOIN sqry_MthRpt1 ON
(sqry_MthDrmPriority.Program = sqry_MthRpt1.Program) AND
(sqry_MthDrmPriority.Division = sqry_MthRpt1.Division)
GROUP BY sqry_MthDrmPriority.Division, sqry_MthDrmPriority.Program,
sqry_MthDrmPriority.[1], sqry_MthDrmPriority.[2], sqry_MthDrmPriority.[3],
sqry_MthDrmPriority.CASREP, sqry_MthDrmPriority.DTO,
sqry_MthRpt1.TransactionDate
HAVING (((sqry_MthDrmPriority.[1]) Is Not Null)) OR
(((sqry_MthDrmPriority.[2]) Is Not Null)) OR (((sqry_MthDrmPriority.[3]) Is
Not Null)) OR (((sqry_MthDrmPriority.CASREP) Is Not Null)) OR
(((sqry_MthDrmPriority.DTO) Is Not Null))
ORDER BY sqry_MthDrmPriority.Division, sqry_MthDrmPriority.Program;

Accomplished this using the Query Dialog Box, which I looked up in the
Microsoft Help site. The site also said that I needed to "Set the
ColumnHeadings property for the query that contains the parameter". How do I
go about setting the ColumnHeadings property for this query? Once that is
properly set, will the query pull only the data within the specified date
range?
Click to expand...
 
Back
Top