troubleshooting Question

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

Avatar of mwheeler_fsd
mwheeler_fsd asked on
Microsoft AccessSQL
5 Comments1 Solution384 ViewsLast Modified:
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:

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:

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,
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros