Cross-Tab Queries

[SarahAirProducts]

I'm trying to create a report from a cross-tab query. I want to base the
cross-tab on specific data by Division and Location. I'd like to total the
PO's per day and then by therapy. It would kind of look like the below:

Division, Location

RX DME ENT WHSE
Date Count POs Total Total Total Total Total Across

Grand Total G.Total G.Ttl G.Ttl GTtl Grand
Total

I am having a difficult time assigning any criteria to the query.

Thanks,
Sarah

 

[Duane Hookom]

"having a difficult time" doesn't tell us much. You should provide your sql
view as well the exact error message.

I expect you need to select the data type of your criteria. Select
Query->Parameters and enter your exact criteria parameters and their data
types.

 

[SarahAirProducts]

I'm using two different tables to create the crosstab query. I'm not getting
any error messages, it just isn't coming up. I have already tried that, it
is not working. The PO Number, Division, Address and Date ordered are in
Table 1. The order information such as Therapy and Extended Price (which is
what I need the total of under each therapy) are in Table 2. I want to have
a look-up, where I can choose the Division, then choose the location, the
Date Range and look up the total number of PO's by day along with the total
amount under each individual therapy as described below.

Does that help?

Thanks,
Sarah

 

[Duane Hookom]

Please provide requested information "should provide your sql view". Also,
"it just isn't coming up"... does this mean you don't see any records but do
see column headings?

 

[SarahAirProducts]

The records come up for the order detail table, but not from the other table.
The column headings are there for one table but not the other. I'm not sure
what 'should provide your sql view' is.

Thanks,
Sarah

 

[Duane Hookom]

While in the crosstab query design view, select View->SQL View. Then copy
the entire SQL statement and paste it into a reply.

 

[SarahAirProducts]

TRANSFORM Sum(qryPURCHASINGREPORT1.[Extended Price]) AS [SumOfExtended Price]
SELECT qryPURCHASINGREPORT1.DateOrdered,
Count(qryPURCHASINGREPORT1.PONumber) AS CountOfPONumber,
Sum(qryPURCHASINGREPORT1.[Extended Price]) AS [Total Of Extended Price]
FROM qryPURCHASINGREPORT1
WHERE (((qryPURCHASINGREPORT1.DateOrdered) Between [Enter Begin Date] And
[Enter End Date]) AND ((qryPURCHASINGREPORT1.Division)=[Enter Division]) AND
((qryPURCHASINGREPORT1.Address)=[Enter Location]))
GROUP BY qryPURCHASINGREPORT1.DateOrdered, qryPURCHASINGREPORT1.Division,
qryPURCHASINGREPORT1.Address
PIVOT qryPURCHASINGREPORT1.Therapy;

The Division and Location will be drop down boxes.

Thanks,
Sarah

 

[Duane Hookom]

Apparently you need a form with text boxes for Begin and End dates as well
as combo boxes to select Division and Location. Then change your SQL to
something like the following:

PARAMETERS [Forms]!frmRpt![txtStartDate] DateTime,
[Forms]!frmRpt![txtEndDate] DateTime,
[Forms]!frmRpt![cboDivision] Long, [Forms]!frmRpt![cboLocation] long;
TRANSFORM Sum([Extended Price]) AS [SumOfExtended Price]
SELECT DateOrdered,
Count(PONumber) AS CountOfPONumber,
Sum([Extended Price]) AS [Total Of Extended Price]
FROM qryPURCHASINGREPORT1
WHERE DateOrdered Between Forms!frmRpt!txtStartDate And
Forms!frmRpt!txtEndDate
AND Division=[Forms]!frmRpt![cboDivision] AND
Address=[Forms]!frmRpt![cboLocation]
GROUP BY DateOrdered, Division, Address
PIVOT Therapy;


--
Duane Hookom
MS Access MVP

TRANSFORM Sum(qryPURCHASINGREPORT1.[Extended Price]) AS [SumOfExtended
Price]
SELECT qryPURCHASINGREPORT1.DateOrdered,
Count(qryPURCHASINGREPORT1.PONumber) AS CountOfPONumber,
Sum(qryPURCHASINGREPORT1.[Extended Price]) AS [Total Of Extended Price]
FROM qryPURCHASINGREPORT1
WHERE (((qryPURCHASINGREPORT1.DateOrdered) Between [Enter Begin Date] And
[Enter End Date]) AND ((qryPURCHASINGREPORT1.Division)=[Enter Division])
AND
((qryPURCHASINGREPORT1.Address)=[Enter Location]))
GROUP BY qryPURCHASINGREPORT1.DateOrdered, qryPURCHASINGREPORT1.Division,
qryPURCHASINGREPORT1.Address
PIVOT qryPURCHASINGREPORT1.Therapy;

The Division and Location will be drop down boxes.

Thanks,
Sarah

While in the crosstab query design view, select View->SQL View. Then copy
the entire SQL statement and paste it into a reply.