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
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.
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
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.