general reporting assistance...

[psimon88]

Hello

I wrote a ton of queries in an MDB and now have to rewrite most of them in an ADP. I am used to writing a series of queries to get from point A TO B. Qry1 feeds 2, 2 feeds 3, etc.

I'd like to know if this is a good general practice in ADP files. I am also concerned about the lack of crosstab query functionality (unless I'm wrong) in ADP files.

Thanks...any advice/tips I would appreciate.

 

[cmmrfrds]

On the first issue, it depends. Normally, in ANSI SQL the subqueries are written in-line with the main query. You could make the sub queries Views and then combine the views into the final query. Whatever your preference, it probably doesn't make much difference from an efficiency standpoint.

A simple defined limit crosstab can be done with CASE statements, much like, you could do with the iif function in Access. If the columns are open-ended then it is necessary to make a #temp table and then do a select on the #temp table. This can be done in a stored procedure or in a series of steps on the same ADO connection.

 

[psimon88]

Since I rely on xtab queries so much, could you please provide an example of the equivalent (taken from an MDB file)?

TRANSFORM Count([tbl_sales_by_rep_2-1].lApplicationID) AS CountOflApplicationID
SELECT [tbl_sales_by_rep_2-1].[Rep FName], [tbl_sales_by_rep_2-1].[Rep LName], [tbl_sales_by_rep_2-1].date, Count([tbl_sales_by_rep_2-1].lApplicationID) AS [Total Of lApplicationID]
FROM [tbl_sales_by_rep_2-1]
GROUP BY [tbl_sales_by_rep_2-1].[Rep FName], [tbl_sales_by_rep_2-1].[Rep LName], [tbl_sales_by_rep_2-1].date
PIVOT [tbl_sales_by_rep_2-1].category;

Thanks.

 

[cmmrfrds]

That would be easier to do if you could give the values that you are pivoting on. How many values? Give an example of the column headings.

PIVOT [tbl_sales_by_rep_2-1].category