Developer Forum

I have a master trial balance table with the following fields:

Period
CoNo
AcctNo
AcctName
Balance

I'm trying to set up parameter queries that can be used to analyze the
master table for all future periods. My problem is how to set up the
necessary queries, macros, etc. that will allow the user to indicate
two different periods and present the mathematical difference between
those two. I have set up temp tables to hold values from a "later
period" append query and an "earlier period" append query that each
prompt the user for the sought dates and send the resulting data to the
temp tables. I would use macros to automate the steps. I was expecting
to create another query based on these two temp tables using SQL to
create a full outer join so all accounts would be presented when I
realized I don't know how to indicate the period in the output columns
presented. In other words, I want the output of this query that
compares and does math on the temp table account balances to look like
this:

CoNo AcctNo AcctName Balance 0412 Balance 0506 Difference
9999 110100 Cash 1,000.00 1,500.00 500.00

Since the periods presented will vary depending on the months needing
to be analyzed, I'm at a loss as to how to capture the parameter (or
the value in the Period field which is the same) so it can be used in
the output.

I've considered using a parameter crosstab query with which to make the
table to be further analyzed to calculate the balance differences. This
produces the periods in the column headings (i.e., the field names) as
I want but I can't set up a reusable query to run against this table
since the names of the balance fields will always be changing. Does
anyone have any suggestions? Thanks in advance.

Alan Lemly

You might be able to set up a form (frmPeriods) with two text boxes for
periods:
txtPeriod1
txtPeriod2
Then your query can be something like:
SELECT CoNo,AcctNo, AcctName,
Sum(Abs(Period = Forms!frmPeriods!txtPeriod1) * Balance) As Period1,
Sum(Abs(Period = Forms!frmPeriods!txtPeriod2) * Balance) As Period2
FROM tblTrialBalance
GROUP BY CoNo,AcctNo, AcctName;

Then, in your report, you can subtract one period from the other.

--
Duane Hookom
MS Access MVP

uane, thanks for the reply. Let me recap my understanding of your
post:

1. I set up a form containing two text boxes to capture the periods
that the user wants to analyze. I assume this form can be called via a
macro ("control macro") and that it will prompt the user for the two
months making the parameter query unnecessary?
2. The control macro runs a query containing the SQL you provided
below.
3. The next step is where I need some help. For purposes of doing the
math (e.g., subtracting one period from another), do I have to set up a
report or can this be done with another query? In either case, how do I
reference the values for each period? Will the report or query which
does this math be usable regardless of the periods selected? What is
the reference to the period amounts and how can it handle the varying
periods each time this query is run?

Thanks for your assistance.

Alan Lemly

Duane Hookom wrote:

. why not use code to open the form? A command button on the form opens a
report.
2. the query is used as the record source of a report
3. the math is done in the report. Subtract Period1 from Period2.

--
Duane Hookom
MS Access MVP


< XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

. I forgot to mention that I'm an accountant, not a programmer and am
not familiar with coding in Access.
2. I'm familiar with reports and basing them on queries. Can you humor
me and tell me what would be the label over the columns containing the
monthly data? From your SQL below, the Forms!frmPeriods!txtPeriod1 and
...txtPeriod2 controls contain the values I want to present in the
report labels but I don't know how to pass the values so they can be
displayed in a report label (or in a query column heading).
3. I know how to do the math in either a report or query. My question
is more about presentation so that the periods being acted upon can be
displayed to the user rather than using some generic label such as
Period1, Period2, etc. I'm designing these queries, reports, macros,
etc. so they can reamin static but continue to function against an
ever-changing trial balance table. Therefore, I can't hard code any
reference to periods since they will vary with each user.

Thanks.

Alan

Duane Hookom wrote:

'm not sure how far you got with creating a form that feeds values to the
query. You can create a column "label" in a report by using text boxes bound
to expressions like:
=Forms!frmPeriods!txtPeriod1
=Forms!frmPeriods!txtPeriod2

--
Duane Hookom
MS Access MVP


< XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

Ok, Duane. I see that you can use text boxes in reports and bind them
to either a form "prompted" value or to a parameter in a query. When I
bound a sample report text box to my [Enter month:] query parameter,
the input period was presented in the report just as I wanted. Thanks
for sticking with me on that point.

I think I know the answer to my last question but I'll seek
confirmation all the same. If I want to present my output in a query
instead of a report, is there any way to use a form or parmeter value
as a query's "field name" label (i.e., the alternate name that is
presented in the QBE grid in front of the fieldname and heads the
column when displayed in datasheet view)?

For example: [Enter month:]: [Period]

Thanks for your help with this.

Alan

You can't set the column heading/alias like this. You would need to resort
to code to change the SQL property of a saved query.

--
Duane Hookom
MS Access MVP