JohnHarkins
MIS
I have an interesting problem that I thought I'd solved, but I can’t quite get it to work. Maybe you can help. So, let me first describe the environment, then give my proposed solution and perhaps you can tell me where I've stumbled.
The main query that the report uses is a straightforward listing of solicitations that are sent out periodically. Each solicitation (a "campaign," found as a record in tblCampaigns) is based on a particular item (a "program" whose characteristics are specified in a "program" table, tblPrograms) that has certain attributes and (here's the hitch) if the program on which the campaign is based has characteristics that conflict with another program that would be mailed within a two-week period, I want to let a "referee" know about it by tagging the record on the report with a "conflict" tag so that the problem can be resolved before the campaign begins. There’s can be multiple campaigns for a single program and each program can be assigned 1 to “n” attributes (or “categories”); for example, a given program might be assigned the categories “Christmas,” “Seniors,” and “New York State.” The campaign includes the date the solicitation is to be mailed. If another campaign is to be mailed within a two-week period (+/- 14 days) and has any of the same categories, flag the campaign for remedial action.
My first step is to find out if there’s a conflict, so I created a query (_qryDxConflicts) that reduces the campaigns, programs, categories, etc. to a single table. I created another query (qryDxConflicts) that links qryDxConflicts and a clone, qryDxConflicts_1 where I specify the criteria that will produce a list of the conflicts with the ID for the campaign as the result.
Next I created the query that is used that’s used as the basis of the report (campaign, program info, geography (e.g., “New York State”).
The tough part? If a detail line has a match from qryDxConflicts, I want the detail line flagged. I thought the solution was to use a domain count (Dcount) function to determine if a given detail line had a match in qryDxConflicts, but I’m not able to specify that the “campaignId” on the detail line (txtCampaignId) is to be used as one of the parameters of Dcount. Please tell me how to do this. Should I use Dcount? If so, what’s the syntax, please? If not, is there a better way to handle this kind of problem using SQL or even another way to determine the conflicts that’s a more elegant solution?
Now, of course, I'd really like to resolve all the problems at data-entry time on a form by letting the program manager who sets up the campaign request see the problem and not even request a schedule slot until , instead of just leaving it to the referee to print out his report and go back to the program manager who's requested a campaign.
You get a star (well almost) if you've taken the time to read through this epistle! At any rate, thanks for listening. I hope you can help.
Thanks!
John Harkins
The main query that the report uses is a straightforward listing of solicitations that are sent out periodically. Each solicitation (a "campaign," found as a record in tblCampaigns) is based on a particular item (a "program" whose characteristics are specified in a "program" table, tblPrograms) that has certain attributes and (here's the hitch) if the program on which the campaign is based has characteristics that conflict with another program that would be mailed within a two-week period, I want to let a "referee" know about it by tagging the record on the report with a "conflict" tag so that the problem can be resolved before the campaign begins. There’s can be multiple campaigns for a single program and each program can be assigned 1 to “n” attributes (or “categories”); for example, a given program might be assigned the categories “Christmas,” “Seniors,” and “New York State.” The campaign includes the date the solicitation is to be mailed. If another campaign is to be mailed within a two-week period (+/- 14 days) and has any of the same categories, flag the campaign for remedial action.
My first step is to find out if there’s a conflict, so I created a query (_qryDxConflicts) that reduces the campaigns, programs, categories, etc. to a single table. I created another query (qryDxConflicts) that links qryDxConflicts and a clone, qryDxConflicts_1 where I specify the criteria that will produce a list of the conflicts with the ID for the campaign as the result.
Next I created the query that is used that’s used as the basis of the report (campaign, program info, geography (e.g., “New York State”).
The tough part? If a detail line has a match from qryDxConflicts, I want the detail line flagged. I thought the solution was to use a domain count (Dcount) function to determine if a given detail line had a match in qryDxConflicts, but I’m not able to specify that the “campaignId” on the detail line (txtCampaignId) is to be used as one of the parameters of Dcount. Please tell me how to do this. Should I use Dcount? If so, what’s the syntax, please? If not, is there a better way to handle this kind of problem using SQL or even another way to determine the conflicts that’s a more elegant solution?
Now, of course, I'd really like to resolve all the problems at data-entry time on a form by letting the program manager who sets up the campaign request see the problem and not even request a schedule slot until , instead of just leaving it to the referee to print out his report and go back to the program manager who's requested a campaign.
You get a star (well almost) if you've taken the time to read through this epistle! At any rate, thanks for listening. I hope you can help.
Thanks!
John Harkins
