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.lNotEligibleRe ason 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.lNotEligibleRe ason 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")); ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12huhsi3g/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123772074/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org ">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life - brought to you by One Economy</a>.</font> --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
