Report groups: declaring a variable ???

[Damir Dezeljin]

Hi.

This is going to be a long post - I hope someone will take time to read
it ... even better would be if I will get any reply


I designed a simple Access database with some forms for entering data.
Heh ... this was a big achievement for me as I'm completely new to
Access, VB and so on. Another problem is my strong background from C and
similar programming languages - I spent quite some time to figure out
that ' is used for comments in VB
Another thing I’m still looking for is how to use bitwise AND and
bitwise OR functions (e.g. 1 | 4 should be 5; 5 & 2 should be 0).


First the description of what I would like to do:
I was using a simple Excel spreadsheet for my personal accounting
(incomes / outgoings). Last month I spent lot of time to get needed data
in a readable form out of the spreadsheet. So I decided I will do a
simple Access program for my accounting (this way I will learn something
new).

I defined below databases:
----
purchase: // contains purchase records
pID Integer AutoIncrement
priece Number // price
cID Number // currency ID
tID Number // type ID
date Date // date of purchase
desc Text // purchase description

currency: // contains currencies
cID Integer AutoIncrement
curr Text // currency abbreviation (e.g. USD)

type: // contains types (e.g. sport, food)
tID Integer AutoIncrement
type Text // e.g. sport, food, fun,…

c_rate: // change rate X <local_currency> = 1 <currency>
rID Integer AutoIncrement
cID Integer
lVal Integer // how much local currency is one unit of foreign one
date Date // date of the change rate
----

Of course I defined all relations too.

After creating the input tabular forms, I entered all the data.

Now I’m trying to define a simple report. I already grouped purchases
first by month with currency sub grouping. This works fine. Now I would
like to have an field containing the amount of local currency spend for
each month. So this means that the report should calculate a local
amount for each currency group sum and multiply it with the change rate.

My form is based on first three tables (it is retrieving price, date,
curr and type – the last one is not used for now). So I’m wondering what
the best way of achieving desired results is.

I got an idea to define a global variable in a module. Whenever a new
month group is started a global variable should be initialized to e.g.
0. Afterwards every time a certain currency group is processed, the
sum() amount should be multiplied with the change rate and the resulting
number should be added to the global variable. The question is how I
should retrieve the change rate for the current month (let say that
there will be only one change rate per month / currency in c_rate table.
I guess a solution would be to use ADO and do a query every time. Is
there any other way to do it?

Is there a way to avoid using a global variable?

Oracle PL-SQL allows defining variables – is it possible to use query
variables in Access too (BTW: is it possible to use T-SQL with Access DB?)?

Is there any high level data type like Dictionary or associative arrays
available in Access VB (I tried to use
CreateObject(Scripting.Dictionary) << or something similar , however I
got an error)?



The below text is here to avoid an additional posting
I guess it is possible to use some sort of filters in Access forms. Is
it possible to use it in a ‘standard’ form (e.g. in my tabular form for
entering data)? If yes – how?


Thanks and best regards,
Dezo

 

[Nikos Yannacopoulos]

Dezo,

I feel your C background is carrying you away.... yes, you can use VBA,
and yes, you could use a global variable (not recommended; they reset
with every untrapped error!) but there are easier ways to do it without
any coding. Here's one: suppose you add a calculated field to the query
that currently serves as the report's recordsource, to return the
month/year of each transaction (in the same format as the matching field
in your c_rate table); then make a second query including the previous
one and the c_rate table, joined on the matching date field. Now you
have the transaction and the rate readily available in each record, so
the rest is obvious.
Alternatively, you could just use a DLookup function to get the rate in
your original query (look it up in help); domain aggreagte functions
like DLookup can be very convenient at times, but the older users tend
to avoid them in queries for they used to impair performance in older
days (i.e. versions), although this is not necessarily the case any longer.

Two points on your design:
(a) avoid the use of "date" as a field name, as it is an Access reserved
keyword (function which returns current system date), or any other
Access reserved keyword for that matter, and may get you in trouble;
suggest you change it to something else.
(b) you could well live without the currency table if it's just what you
posted; the standard currency abbreviations like USD, EUR, GBP etc.
could serve very well the purpose. As a matter of fact, major ERP
systems use those as the PK in the currencies table (used to hold long
names in that case).

HTH,
Nikos