Pivot Table Query

[ctr1085]

I have a query I saved which pulls data and a form that creates a pivot table based upon the query.

Is there a way to create a query based upon criteria such as dates to limit my recordset? I'm trying to set date values in a form and update my saved query but I cannot figure it out. Any help would be appreciated.

Here is the SQL I use to create the standard query. What I plan on including through vb is a "Revenue_Date" variable "FROM" and "TO" date in the code to limit the output based upone the dates entered.

SELECT PARENT, TYPE, SUM(TOTAL) AS COMBINED
FROM [

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '1. IND_Amount' as TYPE,
  IND_Amount AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '2. SBG_Amount' as TYPE,
 SBG_Amount AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '3. IND_Bonus_Amount' as TYPE,
  IND_Bonus_Amount AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '4. SBG_Bonus_Amount' as TYPE,
  SBG_Bonus_Amount AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION 

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '5. Licensing Fees' as TYPE,
  Licensing_Fees AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION 

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '6. IND Misc Expenses' as TYPE,
  IND_Misc_Expenses AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION 

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '7. SBG Misc Expenses' as TYPE,
  SBG_Misc_Expenses AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION 

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '8. Other Receivables' as TYPE,
  Other_Receivables AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID

UNION 

SELECT 
  tblStmt_Tracking.Parent_Carrier_Name AS PARENT, 
  '9. Unknown_Amount' as TYPE,
  Unknown_Amount AS TOTAL
FROM 
  tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
]. AS BREAKOUT
GROUP BY PARENT, TYPE
ORDER BY PARENT, TYPE;

 

[ctr1085]

Anyone

Anyone??? I'm trying to create an access pivot table with variables. has anyone heard of this?

 

[ctr1085]

Anyone

Anyone??? I'm trying to create an access pivot table with variables. has anyone heard of this?

 

[mminick]

I'm not sure how to do this via vb, but if you add the Revenue_Date variable into your data table, and subsequently into the original query, this should become an easy problem to solve. Then you can go into the query and at the cell for Revenue_Date criteria, enter something such as "Between [Start Date] and [End Date]". The user will be prompted to enter both dates and only records between those dates will come up in the search. If your PivotTable is already based on that query, then the table will also inherit the date limitation. If needed you can also add date formating tips to to the user between the []'s such as [Start Date: mm/dd/yyyy]. Good luck.