summarizing and finding the max value within a row over 52 column

In an Access database query I have a table/ query with values per week (52 
weeks), shown in 52 columns. 

I can't find the right way to find to the summary; average and Max -value 
within a row.

As I do have next to none knowledge of visual basic, and  I can't find the 
solution within the design grid, I'm looking for some help on this subject.

Thanks in advance.


0
Utf
4/4/2007 1:54:07 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
2316 Views

Similar Articles

[PageSpeed] 50

Hans,

You need to seriously rethink your table structure.  Each of your weekly 
values should actually be in a single colum, with an additional column to 
indicate the week that the value is for, so instead of:

ID   Wk1  Wk2   Wk3
X     23     32      19

You would have:

ID  SalesWeek   Qty
X         1           23
X         2           32
X         3           19

With this structure, you could use Access built-in functions to get the Max, 
Min, Average, .... values for across all the weeks, or some subset of weeks.

There is no simple way to do what you want to do.  I do have a function that 
you could use for the Max and Min across multiple fields, but have to run to 
a meeting.  I'll try to post back later today with the function.
-- 
Email address is not valid.
Please reply to newsgroup only.


"Hans V" wrote:

> In an Access database query I have a table/ query with values per week (52 
> weeks), shown in 52 columns. 
> 
> I can't find the right way to find to the summary; average and Max -value 
> within a row.
> 
> As I do have next to none knowledge of visual basic, and  I can't find the 
> solution within the design grid, I'm looking for some help on this subject.
> 
> Thanks in advance.
> 
> 
0
Utf
4/4/2007 5:32:01 PM
Hans V <Hans V@discussions.microsoft.com> wrote:

>In an Access database query I have a table/ query with values per week (52 
>weeks), shown in 52 columns. 
>
>I can't find the right way to find to the summary; average and Max -value 
>within a row.
>
>As I do have next to none knowledge of visual basic, and  I can't find the 
>solution within the design grid, I'm looking for some help on this subject.


The reason you can not find a convenient way to do that is
because your table is designed to look like a spreadsheet.
A relational database like Access needs to be designed
following the rules of database Normalization that has that
kind of data in rows instead of columns. See
http://support.microsoft.com/kb/289533/en-us

At this point, I can only think of four choices you have to
dealing with your problem (in order of decreasing
effectiveness):

1) Normalize your data tables
		This will allow the standard database functions to
		calculate the desired values in a very straightforward
		way.  It will also avoid all kinds of other problems you
		have not run into yet.

2) Simulate a normalized table by creating a UNION query.
		This kind of query will put the weekly values in a
		single column:
		SELECT pk, 1 As Week, wk1 As WkVal FROM table
		UNION ALL
		SELECT pk, 2, wk2 FROM table
		UNION ALL
			. . .
			. . .
		Then you use that query as the source for a standard
		query to calculate the aggregate values:
		SELECT pk, Sum(WkVal) As YrTotal,
							Avg(WkVal) As YrAvg,
							Max(WkVal) As YrMax
		FROM qryNorm
		GROUP BY pk

		Even if you don't exceed the limits on Union queries,
		this approach may get you past the current problem,
		it may be too slow to be useful and it may not help with
		future problems.

3) Move your data to Excel
		While this will make other data operations more
		difficult, at least you can perform those column
		operations using spreadsheet functions.

4) Use some very messy expressions in your query.
		For example, the sum of the columns would be:
		Nz(wk1,0) + Nz(wk2,0) + . . .
		The Max will be especially complex and will probably
		exceed several limits for expressions.

-- 
Marsh
MVP [MS Access]
0
Marshall
4/4/2007 5:51:56 PM
As noted elsewhere in this thread, the problem is your structure.  If at 
all possible you need to restructure your data.  Sometimes it is not 
possible to do this.  In those cases, you can try the following VBA 
function.  Copy the code and paste it into a VBA module and save the 
module with a name that is NOT fRowMax

Since you need to do this over so many fields (query only allows 29? 
parameters in a function call), you will have to nest the calls.  That 
will look something like the following.

Field: fRowMax([Wk1],[Wk2],...,[wk26],fRowMax([Wk27],...[Wk52]) )

If you need a count of the fields that have a value or the sum or the 
average, I do have other functions that can work across rows.  I can't 
stress too much that you are better off changing your date structure.

'================= Code starts ==============
Public Function fRowMax(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum Number of a group of values passed to it.
'Sample call:  myMax = GetMaxNumber("-21","TEST","2", "3",4,5,6,"7",0) 
returns 7
'Ignores values that cannot be treated as numbers.

Dim i As Integer, vMax As Variant, tfFound As Boolean, dblCompare As Double

    vMax = -1E+308  'very large negative number
    For i = LBound(Values) To UBound(Values)
       If IsNumeric(Values(i)) Then
          dblCompare = CDbl(Values(i))
          If dblCompare > vMax Then
             vMax = dblCompare
             tfFound = True
          End If
       End If
    Next

    If tfFound Then
       fRowMax = vMax
    Else
       fRowMax = Null
    End If

'================= Code Ends ==============

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Marshall Barton wrote:
> Hans V <Hans V@discussions.microsoft.com> wrote:
> 
>> In an Access database query I have a table/ query with values per week (52 
>> weeks), shown in 52 columns. 
>>
>> I can't find the right way to find to the summary; average and Max -value 
>> within a row.
>>
>> As I do have next to none knowledge of visual basic, and  I can't find the 
>> solution within the design grid, I'm looking for some help on this subject.
> 
> 
> The reason you can not find a convenient way to do that is
> because your table is designed to look like a spreadsheet.
> A relational database like Access needs to be designed
> following the rules of database Normalization that has that
> kind of data in rows instead of columns. See
> http://support.microsoft.com/kb/289533/en-us
> 
> At this point, I can only think of four choices you have to
> dealing with your problem (in order of decreasing
> effectiveness):
> 
> 1) Normalize your data tables
> 		This will allow the standard database functions to
> 		calculate the desired values in a very straightforward
> 		way.  It will also avoid all kinds of other problems you
> 		have not run into yet.
> 
> 2) Simulate a normalized table by creating a UNION query.
> 		This kind of query will put the weekly values in a
> 		single column:
> 		SELECT pk, 1 As Week, wk1 As WkVal FROM table
> 		UNION ALL
> 		SELECT pk, 2, wk2 FROM table
> 		UNION ALL
> 			. . .
> 			. . .
> 		Then you use that query as the source for a standard
> 		query to calculate the aggregate values:
> 		SELECT pk, Sum(WkVal) As YrTotal,
> 							Avg(WkVal) As YrAvg,
> 							Max(WkVal) As YrMax
> 		FROM qryNorm
> 		GROUP BY pk
> 
> 		Even if you don't exceed the limits on Union queries,
> 		this approach may get you past the current problem,
> 		it may be too slow to be useful and it may not help with
> 		future problems.
> 
> 3) Move your data to Excel
> 		While this will make other data operations more
> 		difficult, at least you can perform those column
> 		operations using spreadsheet functions.
> 
> 4) Use some very messy expressions in your query.
> 		For example, the sum of the columns would be:
> 		Nz(wk1,0) + Nz(wk2,0) + . . .
> 		The Max will be especially complex and will probably
> 		exceed several limits for expressions.
> 
0
John
4/4/2007 11:22:32 PM
Reply:

Similar Artilces:

find a space; replace w/no space?
I have a very large dataset with a "Name" field that people have been using with this format: Lastname, Firstname. (Note the space after the comma.) I want to reconfigure the table with 2 separate fields, one for First Name and one for Last Name, so I did the "Text to Columns" thing and split the data into two fields. However, all of the new entries for First Name are now preceded by a space, inherited from the previous format. We're talking thousands of entries here, so not really feasible to manually go into each record and remove the leading space. (I'm...

Tab delimited text problems within Excel
When opening a tab delimited file within excel I am experiencing problems. The file's last column is blank, which is needed, but when I open it within excel it recognises that the column exists for the first 15 rows then it doesn't recognises the column when saving back as a text file.. Any ideas? A lot of programs don't need the tab if there is nothing in the values. But if your program does, how about putting something in that last column that you want. Just put a single apostrophe (') in that last column of each row and excel will understand what you want. Andrew...

Column BookID doesn't exist.
HI friends i am again here with my problem.I have written a code which is compiled without error,But when i want to debug it ,it shows the error Column "BookID" doesn't exist. I dont know why, Becoz in my Databank there is Column BookID. It is my code: void CBookInformationView::DoDataExchange(CDataExchange* pDX) { CRecordView::DoDataExchange(pDX); // you can insert DDX_Field* functions here to 'connect' your controls to the database fields, ex. DDX_FieldText(pDX, IDC_TITLE, m_pSet->m_BookTitle, m_pSet); DDX_FieldText(pDX, IDC_CATEGORY, m_pSet->m_Category, m_p...

Double value subtraction problem....
Hi When I tried to subtract two double values its giving unexpected results!!!! Eg:- 11.846000000000 - 11.846000000000 Its giving some values like -1.02383489238E12 !!!! instead of ZERO !!!! In an MSDN article (Q59407) it says that " subtracting double values greater than or equal to 1.0E+025 may return inaccurate results". But didn't say anything on how to rectify it!!! How to solve this prob? Please help..... rgds, RENAK RENJITH A K wrote: > Hi > When I tried to subtract two double values its giving unexpected > results!!!! > ...

Check DataGridView for existing row
Hello all, I thought this would be simple, I have a DaraGridView with 200 or so rows. All I want to do is check every row to see if a particular string value exists in column[1]. What's the best way to achieve this? I don't really want to do a foreach as there's about 100 + values to compare. Regards, Jon "Jon" <jonmyates@gmail.com> wrote in message news:b6eb3cac-c275-4c07-9360-38fb8a6f8fe2@o10g2000yqa.googlegroups.com... > I thought this would be simple, I have a DaraGridView with 200 or so > rows. All I want to do is check every row to...

File handle in global value
I open a file in one thread, save the file handle in a global value(ignoring for a moment that its not recommended), then close the thread , open a new thread and use the handle inside it to access the file and finally close the file in this thread. Is it legal from the point of view of C++/MFC(again,ignoring for a moment that its not recommended)? Regards Mandi >I open a file in one thread, save the file handle in a global value(ignoring >for a moment that its not recommended), then close the thread , open a new >thread and use the handle inside it to access the file and f...

Automatic recognition of a starting point for a column calculation
Hi Group, I'm having difficulties finding a way that will automate my calculation of Standevp as shown in image (Column AC). The variable is the starting date of the Monthly Performance as shown in image (Column V) What I'm looking for is a way that automatically finds the starting point for that calculation so I don't have to go into each tab and manually change the starting date. The image is located in this post i just created. http://excelproblems.wordpress.com/2010/03/30/hello-world/ Thanks for your help. try this =STDEVP((INDIRECT("v"&MATCH...

SHOW Customer ADDRESS in Customer FIND WINDOW?
Is there a way to show customer street address in the FIND customer lookup window. Our business is address based, and verifying we are not adding a address that already exists is important. Are there any add on's ? -- RMS 1.3, MSDE 3 Lanes, 5 Back Office Clients XP sp2 Server 2003 sp1 Domain Static IP Hi, We have an add on which displays all fields for customer and also allows you to search on these fields. If you register on www.rmsstore.com, you will be able to download the add on, Thanks "DDowningMO" wrote: > Is there a way to show customer street address ...

GAL
Hi - Currently our GAL view is: Name, Business Phone, Office, Title, Company and a few other Exchange related fields. Is there a way to set what columns are displayed here? I would like to add mobile phone, address etc... Thanks - Sean No "SD" <smd6169@hotmail.com> wrote in message news:OyMFPi8vGHA.4160@TK2MSFTNGP06.phx.gbl... > Hi - > > Currently our GAL view is: Name, Business Phone, Office, Title, Company and > a few other Exchange related fields. Is there a way to set what columns are > displayed here? I would like to add mobile phone, address et...

Use a Date range to sum a column
What is the formula to sum Column F (the price) if the date is between 1/1/04 and 1/31/04 Date Pric 1/1/04 1073 2-A Druid Hills John Willis Homes Roger Swims $18,000.0 2/2/04 1074 3-A Druid Hills John Willis Homes Roger Swims $18,000.0 1/1/04 1075 1/7/04 1076 29 Darthmouth John Willis Homes Kevin Aycock $21,000.0 Thank you for your help. Looks like you are totaling per month =SUMPRODUCT(--(MONTH($A$2:$A$200)=1),--($A$2:$A$200<>""),$F$2:$F$200) if you r...

inserting multiple columns from combo list
Hey everybody, I am a newbie to access. I trying to pull employee firstName, lastName, and middleInitial from a table and insert them into one employee field in a timecard table. I get the drop down list to show complete names in the 3 columns but when I choose a name I only get the first name inserted into the employee field. Evidently the query is finding everything but I can't get them to cancatenate into the field. Any help is greatly appreciated. Thanks, Andre Use the Column property of the combo box. The column reference is 0 based, so assuming your columns are First, Mi...

formula switches to value ?!?
what toggle, got switched where ? if A7 contains the value: "Christopher" and in B9 I enter: "=A7", it automatically switches the formula to the resulting value (both in the cell, and in the formula bar; the value of B9 is now not "=A7", but: "Christopher") thanks in advance, -mark mark kubicki wrote: > what toggle, got switched where ? > > if A7 contains the value: "Christopher" > and in B9 I enter: "=A7", it automatically switches the formula to the > resulting value (both in the cell, and in the formula bar; ...

Macro to color fill rows?
I know Excel doesn't read fill color but can you make a macro that finds all cells with the word "total" in it and fill color that entire row? Thanks in advance for any help! Jennifer Try: Sub ordinate() Dim r As Range For Each r In ActiveSheet.UsedRange If r.Value = "total" Then r.EntireRow.Interior.ColorIndex = 6 End If Next End Sub I picked 6 - yellow -- Gary''s Student "Jennifer" wrote: > I know Excel doesn't read fill color but can you make a macro that finds all > cells with the word "total" in it and...

Truly null value
Hi. Is there a way to enter a TRULY null value in a cell? I intend for the formula below to leave the cell blank if the criteria is met. My problem is that when I go to graph this, it graphs the point as zero. If I completely delete the formula so that the cell is empty, it will not graph the point at all (This is what I want ...) Thanks, Mike. Only workaround is to use NA() =IF(A1="",NA(),A1) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Mike D." <anonymous@discussions.mic...

Formatting page header/footer using cell values.
Hello experts! Excel has a nice option to format various parts of a chart using cell values. Can the same be done for headers/footers, e.g. that the header displays value of cell A5. Thanks, Marko. Hi Marko, Try this... ActiveSheet.PageSetup.CenterFooter = Range("A5").Value Regards Ankur/Kanchan www.xlmacros.com Pygmalion wrote: > Hello experts! > > Excel has a nice option to format various parts of a chart using cell > values. Can the same be done for headers/footers, e.g. that the header > displays value of cell A5. > > Thanks, Marko. Thanks! ...

I find a free jokes, pictures website to recommend to you http://www.3800c.cn/ejokes/,http://www.3800c.cn/ejokes2/
I find a free jokes, pictures website to recommend to you http://www.3800c.cn/ejokes/,http://www.3800c.cn/ejokes2/ ...

How can I reference the last data item in a column or row?
I want to display or use the last (most recent) data in either a column or row withour specifically accessing the individual cell. Is there a funcion similar to Min() or Max() that can help me or perhaps another way to solve this question? Thanks See: http://www.xldynamic.com/source/xld.LastValue.html Hope this helps Rowan snappy wrote: > I want to display or use the last (most recent) data in either a column > > or row withour specifically accessing the individual cell. Is there a > funcion similar to Min() or Max() that can help me or perhaps another > way to solve th...

column headings have changed to numbers.
column headings have changed to numbers. My cells now read (1,1) Any suggestions on how to change back.. Hi Brenda tools / options / general - untick R1C1 reference style. Cheers JulieD "Brenda" <Brenda@discussions.microsoft.com> wrote in message news:688B1077-8C36-4649-B0B5-1ED2B98BAC50@microsoft.com... > column headings have changed to numbers. > My cells now read (1,1) > Any suggestions on how to change back.. ...

Using WorksheetFunction.Find in VBA
Hi, I have the following code snippet that I'm using to find whether one digit is part of another large number using something like: If IsNumeric(WorksheetFunction.Find(Third, y)) Then Result = TRUE Else Result = FALSE End If where y is the large number and 'Third' represents the a particular digit. If the expression in the IF statement does contain the said digit, the result of the statement is that Result variable is assigned the value TRUE. However, if the digit is not contained, then rather than hitting the ELSE bit of the above statement,...

Numbering Unique Values in a list....
I have a list of names. Is there any way to count the unique names in a corresponding row, and if a name repeats, list the same number it had before? Names # Larry 1 Larry 1 Larry 1 Jim 2 Jim 2 Jane 3 Jane 3 Larry 1 Larry 1 Jane 3 Using your posted example... Try this: B2: =IF(A2="","",SUMPRODUCT(--(A2<A$2:A$11),1/COUNTIF(A$2:A$11,A$2:A$11&""))+1) Copy that formula down through B11 Is that something you can work with? Post back if you have more questions. -----------...

Change A Cell Value Directly from a Chart
Folks, this is a follow-up to my earlier post. I've done some experimenting and I can quickly accomplish what I'm after if I could figure out how to change a cell value from a worksheet directly from a chartsheet. Here's my plan. Worksheet1 = PopulationModel Worksheet2 = PrehuntEstimates Worksheet3 = PivotTable Chartsheet4 = Chart A closeup look at each component: Worksheet1 Year PHFD PHYD PHAD WS2 WS2 WS2 (CELLS ARE ALL LINKED TO CELLS IN WORKSHEET2 and updated of course when they change) Worksheet2 (88R*7C) PHFD PHYD PHAD ADJUST UPHFD UPHYD UPHA...

Assigning a value to a text
Hello, I'm currently doing somework in Excel and would greatly appreciate some help since I'm stuck. I've reached this part in which you have to calculate the dscounted price for each group of products. The discounts are ranged as Normal (5% discount), Special (10% discount), Exclusive (15% discount). When setting up you had to state what category each product was without indicating the % of discount. So I was wondering, if anyone knows how to *embed* the discount into the text, you know masking it. So any onlooker wouldn't notice it, but I can still calculate the discount. ...

How do you combine two columns to one?
I have a column of address numbers that needs to combine with street names so that I have a combined column of a complete address. Example - What I have in column A is "24513" and in column B I have "Main Street". So I need to combine all of column A with all of column B so that all the addresses appear in one column as "24513 Main Street" Try this in C1: =A1&" "&B1 HTH Jason Atlanta, GA >-----Original Message----- >I have a column of address numbers that needs to combine with street names so >that I have a combined column of...

Cant se some columns! Why?
I have a spreadsheet that displays COL A and them col BL-BM-BN etc How do I get to see the cols B to BK I am working with EXCEL 2007 Thanks Either columns B through BK are hidden (most likely), or the Window is frozen and you've scrolled way over to the right, bringing column BL next to A. Assuming they're just hidden, Click on the 2 that identifies row 2 so that the entire row is selected, then On the [HOME] tab, in the CELLS group, pull down the "Format" list and choose Hide and Unhide and click the UNHIDE COLUMNS option. If its a Frozen window thin...

Searching for Substrings Within Strings
I would like to be able to search and flag a list of string data based on another list of substring data. If I have a list of strings in Column A and input the list of substrings in Column C, I would like an "X" to appear in Column B every time that a substring mentioned in Column C is included in any of the strings indicated in Column A. (There cannot be multiple instances of the same substring, or a combination of different substrings, in each string of data in Column A.) For instance, given the following information indicated in Columns A and C, the "X" should appear a...