advice on consolidating reports

[psimon88]

I have written a series of queries for an MDB file that I am upgrading to an ADP one. See below for the series. Long story short, I created a series of queries that fill other queries.

It's not uncommon for me to follow the following process:

query 1: query a large table to get my dataset
query 2: get a count of a field by a value in that data set
query 3: write that 'count' to a table (as an append query)
etc.

I'd like eto know if I can consolidate this process for ADP files and, eventually, .NET. Right now, I'm pretty sure that my rpt development could stand some improvement.

Thanks in advance. Code example below:

qry_media_eff_1
SELECT dbo_CostsIncome.MediaName, tbl_VM_leads.bApplicantEligible, tbl_VM_leads.lAutoDispositionStatus, tbl_VM_leads.lNotEligibleReason, dbo_lNotEligibleReason.Description, tbl_VM_leads.dtmTimeStamp
FROM (dbo_CostsIncome INNER JOIN tbl_VM_leads ON dbo_CostsIncome.ReferID=tbl_VM_leads.strReferId) INNER JOIN dbo_lNotEligibleReason ON tbl_VM_leads.lNotEligibleReason=dbo_lNotEligibleReason.lNotEligibleReason
WHERE (((tbl_VM_leads.bApplicantEligible)=2) AND ((tbl_VM_leads.dtmTimeStamp)>=#7/29/2005# And (tbl_VM_leads.dtmTimeStamp)<=#7/30/2005#));


qry_media_eff_2_Crosstab
TRANSFORM Count(qry_media_eff_1.lNotEligibleReason) AS CountOflNotEligibleReason
SELECT qry_media_eff_1.MediaName, Count(qry_media_eff_1.lNotEligibleReason) AS [Total Of lNotEligibleReason]
FROM qry_media_eff_1
GROUP BY qry_media_eff_1.MediaName
PIVOT qry_media_eff_1.Description;


qry_media_eff_3
SELECT qry_media_eff_2_Crosstab.MediaName, [Bad SSN]/[Total Of lNotEligibleReason] AS [Bad SSN2], [Consolidated]/[Total Of lNotEligibleReason] AS Consolid, [Default]/[Total Of lNotEligibleReason] AS Default2, [In School]/[Total Of lNotEligibleReason] AS InSchool2, [Single Lender]/[Total Of lNotEligibleReason] AS [Single Lender2], [Under 10k]/[Total Of lNotEligibleReason] AS [Under 10k2], "Yesterday" AS Source
FROM qry_media_eff_2_Crosstab;


qry_media_eff_4
SELECT dbo_CostsIncome.MediaName, tbl_VM_leads.bApplicantEligible, tbl_VM_leads.lAutoDispositionStatus, tbl_VM_leads.lNotEligibleReason, dbo_lNotEligibleReason.Description, tbl_VM_leads.dtmTimeStamp
FROM (dbo_CostsIncome INNER JOIN tbl_VM_leads ON dbo_CostsIncome.ReferID=tbl_VM_leads.strReferId) INNER JOIN dbo_lNotEligibleReason ON tbl_VM_leads.lNotEligibleReason=dbo_lNotEligibleReason.lNotEligibleReason
WHERE (((tbl_VM_leads.bApplicantEligible)=2) AND ((tbl_VM_leads.dtmTimeStamp) Between #7/1/2005# And #7/31/2005#));


qry_media_eff_5_Crosstab
TRANSFORM Count(qry_media_eff_4.lNotEligibleReason) AS CountOflNotEligibleReason
SELECT qry_media_eff_4.MediaName, Count(qry_media_eff_4.lNotEligibleReason) AS [Total Of lNotEligibleReason]
FROM qry_media_eff_4
GROUP BY qry_media_eff_4.MediaName
PIVOT qry_media_eff_4.Description;


qry_media_eff_6
SELECT qry_media_eff_5_Crosstab.MediaName, [Bad SSN]/[Total Of lNotEligibleReason] AS [Bad SSN2], [Consolidated]/[Total Of lNotEligibleReason] AS Consolid, [Default]/[Total Of lNotEligibleReason] AS Default2, [In School]/[Total Of lNotEligibleReason] AS InSchool2, [Single Lender]/[Total Of lNotEligibleReason] AS [Single Lender2], [Under 10k]/[Total Of lNotEligibleReason] AS [Under 10k2], "30 days" AS Source
FROM qry_media_eff_5_Crosstab;


qry_media_eff_7
SELECT tbl_MediaEffectiveness.*
FROM tbl_MediaEffectiveness
WHERE (((tbl_MediaEffectiveness.MediaName) Not Like "dum*"));


qry_media_eff_7a
SELECT tbl_MediaEffectiveness.*
FROM tbl_MediaEffectiveness
WHERE (((tbl_MediaEffectiveness.MediaName) Not Like "dum*") AND ((tbl_MediaEffectiveness.Source)="30 days"));

 

[misscrf]

this is a query question, not a report question. I would repost in the query forum.

misscrf

It is never too late to become what you could have been ~ George Eliot