Query Expression building

[Tracy]

I am building a datebase to track sales/inventory for my employer. I
have yearly tables that are broken down into month. Then I created a
query to show quarterly sales totals. For example Qrt 1 Total:
tbl2005!January+tbl2005!February+tbl2005!March shows the total for
quarter 1. Quarter 3 consists of July, August and September. I have
data in place for July but not for August or September, as those months
have not closed. When I run the query, it displays nothing in the
column for Quarter 3. I would like it to show the total to date for
Quarter 3 which would be just the data from July, but it does not.
Anyone know how to make the quarter 3 expression be a running total?
This the current expression I have used : Qrt 3 Total:
[tbl2005]![July]+[tbl2005]![August]+[tbl2005]![September].

thanks in advance!
Tracy

 

[Klatuu]

Tracy,
First, you need to reconsider your database design, It is very inefficient
to have each month's data in a separate table. As you can see, it creates
problems with grouping data for queries and reports.
A better plan would be to add 2 fields to your tables, one for the month and
one for the year. Then you can do your sorting and grouping that way. For
example, in the problem you post, you could do a totals query by creating a
field in your query that would determine the quarter based on the values in
the two fields I suggest. Let's call them TRAN_MONTH and TRAN_YEAR. Then, in
the Field row of a new column in the query:

TranQtr: DatePart("q",dateserial([MyTable]![TRAN_YEAR],
[MyTable]![TRAN_MONTH],1))

If you already have a transaction date field in you table, you could use
that with the above formula without having to create the two new rows.

But, above all, change your database design if you can.

 

[Tracy]

Thanks for the input!

Unfortunately i can not change the design of the database this late in
the game, and the database works perfect for everything else we do.
maybe i wasn't clear when i explained my db. I have a monthly inventory
table and lists by month for every year (for examples sales for
january, 1999, 2000, 2001 and so one). I also have other tables that
lists yearly totals by month (for exapmle sales for 1999, january,
february, march and so on). they are linked together, but provides us
with a look at different type of trends. we have just recently wanted
to look at our inventory in quarters, so i am building queries that
adds the raw data from the simple tables to give me the quarter totals.
It works fantastics for all past years and quarters, up till the
present quarter. Since the expresion refers to feilds that are null,
the expression isn't displaying the running total.

Any ideas on how to get it to do that?

thanks again!

 

[Klatuu]

You can do that by using the Nz function to avoid the Nulls.
Nz([tbl2005]![July],0)+Nz([tbl2005]![August],0)+Nz([tbl2005]![September],0)

 

[Tracy]

Thank you so much! that worked perfectly!!!!