Parameter Queries

[Udayan]

Kindly help....

I have got a table with following fields

Date received = Type (date)
Item-A = Type (type number)
Item-B = Type (type number)
Item-C = Type (type number)
Item-D = Type (type number)

And got a parameter queires
Between [Enter start Date]And[Enter End date]

And the result is comming fine as per the entered date.

My question is is there any way to total all columns as shown below whenever
parameter querie run?

Date Item-A Item-B Item-C Item-D
01-Jun 3 0 1 6
02-Jun 0 1 3 4
03-Jun 3 4 5 0
04-Jun 5 1 2 3

 

[Allen Browne]

Previous reply didn't work for you?

The idea is to type an expression into the Field row in query design:
Nz([Item-A],0) + Nz([Item-B], 0) + ...

The real problem here is the repeating fields. Whereever you see ItemA,
ItemB, ..., it always means that you need to break this into a related
table. In your case, the table would have fields like this:
TheDate Date/Time
ItemType Which type of item this record is for
Quantity Number how many of this type on this date
Sample records:
1 Jun A 3
1 Jun C 1
1 Jun D 6
2 Jun B 1
2 Jun C 3

That's the most crucial concept to grasp when designing relational
databases: how to create the one to may relationships, so you have many
*records* in a related table, rather than many repeating fields in one table
(like a spredsheet.)

Once you have that structure it's dead easy to sum the Quantity for the
date.

If you need a report laid out like your current table, use a crosstab query.
If you already have the right structure, and you are trying to get a row
total into your crosstab, see:
http://allenbrowne.com/ser-67.html#RowTotal