Summing contents in columns

[Skip Bisconer]

I have searched for an answer here but I suppose I am not wording my search properly. I have a query with inventory records with a 24 month history of sales labeled M1 through M24.

When analalizing economic order quantities and some other calculations to reveal excessive inventory by item my user must be able to grab the last seven months of sales units and take an average to use in other calculations.

In Excel I would just do a Sum(M1:M7)/7 but this doesn't seem to work in Access and Access help on summing gives no examples of what I need to do. I am currently just manually doing a Exp (M1+M2... etc)/7 but my problem with that is when every month changes the user has to move forward one month. Having the user enter this in a [What Months ] query parameter could lead to critical errors.

If I can find the right function I would then be able to make it easier for the user to do this when using the query. Any help or ideas would be appreciated.

 

[khawar]

Actually access and excel are different your months should be in row instead of columns then you can perform any action on data i-e sum, count, average etc

Other wise you have to do it manually or by using some complex vba code

But I recommend you to change your database structure

 

[Skip Bisconer]

Thanks Khawar for responding.

If I understand you correctly, I didn't specify the lay out properly and didn't give a correct designation to my Excel example. The correct example is Sum(A1:A7). Sorry about misleading you.

Usage is in a row for 24 Months, left to right M1,M2,M3 etc., for each inventory item. If I changed my database strucure I would end up the 20000+ columns. Maybe I am using the incorrect terminology. I would love to be able to use the (Between M2 and M8) in a sum function, as another example, but I can't seem to make this work in an expression.

For every inventory item I have 24 columns left to right with 24 months of data on the row of each inventory number.

 

[khawar]

Can you describe the name of fields and what data they are storing

 

[Skip Bisconer]

Fields are labled M1 for last Jan M2 for last Feb....M13 for prev Jan and M14 for previous Feb...all fields are and numeric with no decimals and no blanks but lots of 0's.

 

[khawar]

Thats what I was talking about your structure in not normalized and needs revision

 

[Skip Bisconer]

I am at a loss here. How would you structure 10722 inventory numbers each with 24 different months of activity. My structure is in this manner:

Labels
Inv # M1, M2, M3 M4 etc.

Are you saying I should have it structured in this manner, or 1 query for every inventory number?
Labels
M1
M2
M3
etc.

I would need to have 10722 columns in my query?

 

[georgedwilkinson]

That's not what Khawar was suggesting. Access is much more flexible than Excel and can do this quite easily.

Of course, all this depends on whether this is a live (OLTP) system or just a reporting database (with no live updates of the data).

I submit to you that you don't store any months of activity with an inventory number. You would query the invoice line items table to get your sums. I really don't know exactly what you're after here, but you should take a look at how the Northwind (sample database) sales records are arranged and start from there.

 

[Skip Bisconer]

This is not a live update database. Without an inventory number attached to the Inventory Qty History number how would anyone know what each 24 fields represent?

I have three tables that I cannot change, however I can and do query. An "Inventory Master" table, "In-warehouse Qty" table and "Inventory Qty History" table. All three of these tables have the inventory number in common. The only items I use from the Inventory Qty History are the 24 fields holding the sales activity for each inventory number. In order to accomplish what I need to is add seven different fields together (these seven change by 1 month each month) and get an average of the seven months. I can't change how the data is structured in the tables as it comes from our sever in the format that I explained previously. Can you help me with that?

 

[neileg]

The point is that you should have 24 rows in a related table not 24 columns. That would be the normalised structure. However, you have no control over the table format.

How are the columns populated? My interpretation of your information is that if you need the last 7 months data then this will be in columns M18 to M24. Is this correct?

 

[Skip Bisconer]

Thanks Niel,
It's actually M1 is last January and M13 is the previous January.