asked on

Form with Parameters, Query, and Report - Complex query requirements

Dear Experts:

Background: I am working on a solution for a friend of mine's Audit Dept. in Access 2003.

I have been a programmer for 25 years, but have never really used Access for anything
so I am at beginner level at best. My background is C, C++, Java, PHP, etc. I mention
this only to indicate I do have an understanding of programming and syntax, but
am not verse with Access or VBA.

Challenge: Produce a Form, Query, and Report where the form passes parameters
to the Query in turn producing the Report.

Currently: I have successfully created the Form, Query, and Report. When I
execute the report, it pops the Form and passes the criteria to the query. Once
the "Run Query" button I have on the form is clicked, the form disappears the query runs,
and the report is produced. One additional question I have in this is whether there
is a way to hide the Query from showing on the screen when executed like the
Form does? However, this is the least of my issues right now.

In order to get to the completion level I am, I have the following simple version
of the query running:

SELECT MASTER_DDA.BRANCH, MASTER_DDA.ACCTNO, MASTER_DDA.DDA_CLASS_46,
MASTER_DDA.DDA_TYPE_47, MASTER_DDA.DDA_SHORT_NAME, MASTER_DDA.DDA_CUR_BAL_21
FROM MASTER_DDA
WHERE MASTER_DDA.BRANCH = [Forms]![a_AuditForm]![comboBranch]
AND MASTER_DDA.DDA_CLASS_46 = [Forms]![a_AuditForm]![comboClass]
AND MASTER_DDA.DDA_TYPE_47 = [Forms]![a_AuditForm]![comboType]

Here is where I am absolutely struggling:

The Form has several parameters (I have not included the Percent yet in my query).

Application Type - comboAppType
Branch - comboBranch
Class - comboClass
Type - comboType
Percentage (of random records to return) - txtBoxPercent

The "Application Type" variable distinguishes which table and a substring
of the field name needs to be used.

For example:

If AppType = 1 Then use table MASTER_DDA and fields DDA_CLASS_46, DDA_TYPE_47, etc.
If AppType = 2 Then use table MASTER_SAV and fields SAV_CLASS_46, SAV_TYPE_47, etc.
If AppType = 3, etc....

My sample query is hard coded as if the AppType parameter on the form was
selected as 1, if it were 2, it would look something like this:

SELECT MASTER_SAV.BRANCH, MASTER_SAV.ACCTNO, MASTER_SAV.SAV_CLASS_46,
MASTER_SAV.SAV_TYPE_47, MASTER_SAV.SAV_SHORT_NAME, MASTER_SAV.SAV_CUR_BAL_21
FROM MASTER_SAV
WHERE MASTER_SAV.BRANCH = [Forms]![a_AuditForm]![comboBranch]
AND MASTER_SAV.SAV_CLASS_46 = [Forms]![a_AuditForm]![comboClass]
AND MASTER_SAV.SAV_TYPE_47 = [Forms]![a_AuditForm]![comboType]

I tried to put a condition on the FROM clause using SWITCH and CASE
but had no luck.

FROM SWITCH ([Forms]![a_AuditForm]![comboAppType] = 1, "MASTER_DDA",
[Forms]![a_AuditForm]![comboAppType] = 2, "MASTER_SAV", etc.)

FROM CASE WHEN [Forms]![a_AuditForm]![comboAppType] = 1 THEN "MASTER_DDA"
WHEN [Forms]![a_AuditForm]![comboAppType] = 2 THEN "MASTER_SAV" etc. END

Note - the AppType will only ever be a total of
5 possibilities so if I need to SWITCH or CASE the field names,
rather than use a SUBSTRING() type function, that is fine.

In addition to this, the final data set needs to contain a percentage
of the total number of records, randomly selected.

So, Once all the conditions are met a random [Forms]![a_AuditForm]![txtboxPercentage]
need to be presented on the report. I'm not sure how to attack this. Do the number of records
need to be determined in advance with a COUNT(*), then that number fed
to a RAND() type function? Is there a way of cascading the queries?
I don't see how a UNION could be used in this case.

In any event, I hope I have conveyed the challenge clearly.

Thank you in advance for any assistance you can offer.

Best regards,
Mike

Jeffrey Coachman

First, the report can be based on the query.
In this way you don't actually need to "Run" the query first.
(This will perhaps also eliminate the need to hardcode the query with any criteria)

In other words, if the report is based on the query (with no criteria), you need only pass the Form's Open parameters directly in the code that opens the report:
ex:
DoCmd.OpenReport "YourReportName", acViewPreview, , "CustomerID=" & Me.CustomerID
(For Numerical Values)
OR
DoCmd.OpenReport "YourReportName", acViewPreview, , "CustomerID=" & "'" & Me.CustomerID & "'"
(For string values)

In the examples above, a Report opens with only the records for the specified "CustomerID" on the form.
(again, no actual query criteria is hardcoded)

Also, in your case, the Form value perhaps needs to be linked to the corresponding Conditions field value. Thus eliminating the need to use the "Switch" Function.
For example. you should have a table listing the "DDA_TYPE" and the corresponding "AppType"
Then link to this table, again, eliminating the need for any Switch function.

(Even if you had to use Switch, I would instead opt to use an if-then-else syntax. This is just a personal preference, as I sometimes find the Switch function syntax confusing is certain cases)

As for the other parts of your question:
"Note - the AppType will only ever be a to..."
"So, Once all the conditions are met..."

These seem to be separate issues.

The format of this forum is Question and Answer.
Meaning you post one specific question requiring a single direct answer.
(There are exceptions, naturally, ...I am merely stating the rules of the site.
There may be experts willing to take on an "Extended" question, though...)

You can't really post multiple issues as a "Challenge"

So, ...
See how far you get with what I posed above and let us know...

;-)

JeffCoachman

mwheeler_fsd

ASKER

Dear Jeff,

Thank you for your response. I sincerely appreciate your comments. Part of the problem I had with posing this question is my total lack of experience with Access. I truly don't know if this is/was a single issue or not, because it is all so integrated.

All Experts - PLEASE take this in context - if this were posed to me using a language I am familiar with, I would potentially be in a position to offer a rapid solution. I told this to my friend, but he would prefer to, and understandably so, keep it all within Access. I am truly just trying to do him a favor.

I apologize to all members of this zone if I have violated the general rules. It is unintentional.

Jeff - I will try what you have suggested.

Thank you,
Mike

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mwheeler_fsd

ASKER

Jeff - this definitely pointed me in the right direction. Thank you for your help and feedback. Best regards --Mike

Jeffrey Coachman

;-)