Combining SUM, Range of Fields, and dialog/parameter box (1 Viewer)

[hamed_gan]

Hi all,

I have a table containing the details of the sold quantities of a particular Item to a Customer;

Field1: CustomerNumber
Field2: Customer Name
Field3: ItemNumber
Field4: Sep-03 (the whole column contains the quantity of a given item sold to a given customer in the Month September-2003)
Field5: Oct-03 (same as Sep-03, only for October 2003)
Field6: Nov-03
Field7: Dec-03

What I want to run is a query that asks the runner to enter a beginning period and ending period, and Access in turn takes the sum of the data in the given range.

I have tried almost everything what I was capable of, but with no result I can manage to use the “+” operator to sum up the values of fields, but then I have to type in the field my self;

TotalSales: [Sep-03] + [Oct-03] + [Nov-03] + [Dec-03]

But this approach is inflexible and does not allow the user to select just a period. I’m familiar with Excel, and in excel it would be easy to do something like this; SUM(A). In this case it sums up all the data in Rows A to D. Using the Offset function, you can make this also dynamic. But how can I manipulate the same procedure with Access? There are SUM and DSUM functions, but they don’t operate the same way; for example, I cannot type in a empty field in a query;

TotalSales: SUM([Sep-03]:[Dec-03]). It gives a syntax error.

This is also not possible, same error

TotalSales: SUM([Enter beginning date:]:[Enter ending date:])


Could anybody help me with this?? Any suggestions would be welcome and really really appreciated.

Thanks!
Zurvy

 

[Mile-O]

Any suggestions would be welcome and really really appreciated.

Normalise your data and you won't have this problem.

 

[Mile-O]

i.e.

a proper sales table

tblSales
SaleID - Autonumber
CustomerID - Number; foreign key related to customer table
ProductID - Number; foreign key related to product table
DateOfSale - Date

Now you can query monthly results based on the DateOfSale and then use a Crosstab query to display it like your current (non-1NF) table.

 

[hamed_gan]

i.e.

a proper sales table

tblSales
SaleID - Autonumber
CustomerID - Number; foreign key related to customer table
ProductID - Number; foreign key related to product table
DateOfSale - Date


Hey,
Tnx for the reply,
I have the following tables actually, Look at the attachements. The sales_table actually cannot be done otherway, or can it?

Tnx a lot!
Zurvy

 

[Mile-O]

The sales_table actually cannot be done otherway, or can it?

This is my way of looking at it.

 

[hamed_gan]

tnx again,

I know exactly what you mean, but there is one factor I cannot change,

The data are exported from Oracle. The data are extracted on montlhy bassis. So on a Oracle report, the month will apear as in columns. An the order ID is igonerd, as it is not necessary for the purpose of the user. An the data each month are first merged together by using a query. after that they null values are removed (because some custometrs havn't ordered an Item and other have). Finally they are all combined with eacb other by using a make-table query. See new relationships again.

Maybe I can do it in a form? I know that there is property called RunningSum. Would that do as well?

Tnx again.