Query by Week Problem 1

[xeb]

Hi:

I have the following query for getting averaged records by the week.

SELECT [Week], WeekTotal, WeekRecords, WeekTotal/WeekRecords AS WeekAvg FROM
(SELECT format([Date], "ww&quot AS [Week], SUM([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+[21]+[22]+[23]+[24]+[25]+[26]+[27]+[28]+[29]+[30]+[31]+[32]+[33]+[34]+[35]+[36]+[37]+[38]+[39]+[40]+[41]+[42]+[43]+[44]+[45]+[46]+[47]+[48]+[49]+[50])
AS WeekTotal, COUNT(*) AS WeekRecords
FROM TargetSheetData
GROUP BY format([Date], "ww&quot)
ORDER BY [Week]

However, it doesn't seem to be working quite right.

Also, I need the WeekAvg to return as the sum of all records divided by the count of all records.

In other words, if the WeekTotal is 100 and the number of records for that week is 50 then the average I need would be 2.0.

My reports use the following expression to get the average I need:

=((NZ(Sum([1]))+NZ(Sum([2]))+NZ(Sum([3]))+NZ(Sum([4]))+NZ(Sum([5]))+NZ(Sum([6]))+NZ(Sum([7]))+NZ(Sum([8]))+NZ(Sum([9]))+NZ(Sum([10]))+NZ(Sum([11]))+NZ(Sum([12]))+NZ(Sum([13]))+NZ(Sum([14]))+NZ(Sum([15]))+NZ(Sum([16]))+NZ(Sum([17]))+NZ(Sum([18]))+NZ(Sum([19]))+NZ(Sum([20]))+NZ(Sum([21]))+NZ(Sum([22]))+NZ(Sum([23]))+NZ(Sum([24]))+NZ(Sum([25]))+NZ(Sum([26]))+NZ(Sum([27]))+NZ(Sum([28]))+NZ(Sum([29]))+NZ(Sum([30]))+NZ(Sum([31]))+NZ(Sum([32]))+NZ(Sum([33]))+NZ(Sum([34]))+NZ(Sum([35]))+NZ(Sum([36]))+NZ(Sum([37]))+NZ(Sum([38]))+NZ(Sum([39]))+NZ(Sum([40]))+NZ(Sum([41]))+NZ(Sum([42]))+NZ(Sum([43]))+NZ(Sum([44]))+NZ(Sum([45]))+NZ(Sum([46]))+NZ(Sum([47]))+NZ(Sum([48]))+NZ(Sum([49]))+NZ(Sum([50])))/(Count([1])+Count([2])+Count([3])+Count([4])+Count([5])+Count([6])+Count([7])+Count([8])+Count([9])+Count([10])+Count([11])+Count([12])+Count([13])+Count([14])+Count+([15])+Count([16])+Count([17])+Count([18])+Count([19])+Count([20])+Count([21])+Count([22])+Count([23])+Count([24])+Count([25])+Count([26])+Count([27])+Count([28])+Count([29])+Count([30])+Count([31])+Count([32])+Count([33])+Count([34])+Count([35])+Count([36])+Count([37])+Count([38])+Count([39])+Count([40])+Count([41])+Count([42])+Count([43])+Count([44])+Count([45])+Count([46])+Count([47])+Count([48])+Count([49])+Count([50])))

Should I use this in the query?

Thanks,

xeb

 

[dhookom]

Are you ever going to tell me what the 50 fields are for? Honestly, if you don't, you will have to find someone else to help. I can't waste anymore time with attempting to support someone who will not answer a simple question. I have asked the same question 4 times. Do you understand the question?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[hneal98]

I think one thing you can do is put a list box on the form and have the user select the fields to be averaged. Then you can do the averageing in the background through VB by running through each item one at a time and adding it up and keeping track of the count at the same time. Then when you get that info, you can save that value in a global variable and add it to your report.

You could actually have several controls on your form, like radio buttons,multi-select list boxes, check boxes and do something where your users can select all fields they want averaged.

You could have the user select the ones they want to average and store their values in an array. Then, you could run code to loop through the array to add up the values. Since this is in an array, you already have the count, which would be the size of the array, and since the array starts with 0, you would just add 1.

Finally, you can do your average, store it in a variable and pass it to the report when the report is opened.

Any thoughts?

 

[xeb]

hneal98:

Thanks for answering.

Your suggestions sound good. However, it's all new stuff to me (VB, arrays, etc.) so I'm going to have to read up on that stuff so I can do it.

I'm sure I'll be back for help.

Thanks again,

xeb

 

[hneal98]

No problem.

 

[xeb]

I just started work on the changes, but I don't see how to do it.

My form looks like this (1-50 are my fields on the form as they now appear.)

Date - mm/dd/yy

 1    2    3    4    5    
 6    7    8    9   10    
11   12   13   14   15    
16   17   18   19   20    
21   22   23   24   25
26   27   28   29   30
31   32   33   34   35
36   37   38   39   40
41   42   43   44   45
46   47   48   49   50

How will my users enter data using the new method?

If the table looks like this for one date how can I separate out the specific numbers I want to average?

1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1
1/5/04 2
1/5/04 3
1/5/04 1

Thanks,

xeb

 

[dhookom]

Your table would look like:
1/5/04 1 1
1/5/04 2 2
1/5/04 3 3
1/5/04 1 4
1/5/04 2 5
1/5/04 3 6
1/5/04 1 7
1/5/04 2 8
1/5/04 3 9
1/5/04 1 etc
1/5/04 1 50
Where the right most column corresponds to your previous field number (whatever they stood for). Call this column "Col" and then you can easily select and average any group of "Col" values by using a criteria of (going back to one of your previous questions):
[Col] IN (1, 11, 21, 31, 41)
or
[Col] IN (1, 2, 11, 12, 21, 22, 31, 32, 41, 42)
or
[Col] IN (3, 4, 13, 14, 23, 24, 33, 34, 43, 44)

[Col] (2, 12, 22, 32, 42)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xeb]

Thanks, the table makes sense now. I'm sure this stuff is elementary to you, but I just couldn't understand how it would work.

So if the table was like that, would I be using an expression or a query to get results? If it's not too much trouble could you write one for me to get me started?

Also, regarding data entry, my form looks like what I have posted here. It was designed that way because it is an exact duplicate of the paper version that my users generate during the day. It made data entry much eaiser. They simply filled in the date and all fifty boxes and pressed enter. What's the best (and easiest) way to do it using the new method?

Also, how do I convert my existing data to the new table?

Thanks,

xeb

 

[dhookom]

You would use simple queries to pull averages. FOr instance, if you wanted the average of a group of [Col] values for all dates then:

SELECT Avg(ValueField) as MyAvg
FROM tblNormalized
WHERE [Col] (2, 12, 22, 32, 42);

To limit the date range:

SELECT Avg(ValueField) as MyAvg
FROM tblNormalized
WHERE [Col] (2, 12, 22, 32, 42)
AND [DateField] Between #1/1/2004# and Date();


To create an update form, I would probably start with your existing form and remove its record source. The text boxes should all have a significant name like txtDate, txt1, txt2, ...txt50.
You would then run code to append records to your table. The code would be run by the user clicking a button:

Dim intCol as Integer
Dim strTextBox as String
DIm strSQL as String
'turn off warning messages
DoCmd.SetWarnings False
For intCol = 1 to 50
    strSQL = "INSERT into tblNormalized ([DateField, ValueField, Col) " & _
        "Values (#" & me.txtDate & "#," & Me("txt" & intCol) & _
        ", " & intCol & ")"
    'insert one record
    DoCMd.RunSQL strSQL
Next
DoCMd.SetWarnings True
MsgBox "Done Adding 50 Records"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xeb]

Duane:

Thanks. I really appreciate your help.

I'm going to start work tomorrow changing things. I'm sure I'll be back for help.

One thing I'm not clear on right now. In the table I will have three fields ("txtDate", "txtData", "txtCol"). Is that right? If so, how does the existing field numbers ("txt1", "txt2", "txt3", etc.) get there.

Thanks again for everything.

xeb

 

[dhookom]

You would need to either create an append query or write some code that would loop through your existing records and append to the new table structure. I would opt for the code method.

BTW: I don't name my fields beginning with "txt". I reserve "txt" as a prefix for text boxes on forms or reports.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xeb]

Thanks for answering.

Assuming that I have no existing data, in other words, that I'm building a database from scratch, how does the existing field numbers ("txt1", "txt2", "txt3", etc.) get into the table. Does it have to be entered on the form?

 

[dhookom]

I explained this in a previous message with the two sample SQL statements and code. Users enter the data as they always have and click a button that runs the code to append the data to the table.

I have created a sample of how this can be done. You can download it from

http://access.hookom.net/xeb.zip.

It took me about 20 minutes to create and test this.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xeb]

Duane:

Thanks so much for that file. It helped more than you know. You're the best!

I spent the day working on it and as I expected I have some questions.

1. The single query is working great, but my reports need to return averaged data for several different configurations. I tried to get the query to take more than one but after three hours I gave up. In other words, I need the following query to run for one report.

SELECT Avg(xebValue) AS MyAvg
FROM tblXeb
WHERE (((tblXeb.xebCol) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,
23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,
42,43,44,45,46,47,48,49,50)));

SELECT Avg(xebValue) AS MyAvg
FROM tblXeb
WHERE (((tblXeb.xebCol) In (1,2,3,4,5,6,7,8,9,10)));

SELECT Avg(xebValue) AS MyAvg
FROM tblXeb
WHERE (((tblXeb.xebCol) In (11,12,13,14,15,16,17,18,19,20)));

Etc.

2. My old report that simply displays the values for given dates is not working using the new format. On the old report I simply put [FieldName] (In my old case, [1], [2], [3], etc.) to get it. What should I use now?

3. On the new form (I love the features "clear boxes" and "append records") there is a "fill boxes" buttom that seems to fill the boxes with randomly generated numbers (1-3). I like this, but how can I make it fill all the boxes with my default number, which is "3"?

4. I have three more fields for data that have to be added at some point. Just so I do it right this time, I'm thinking that they go in a separate table. They relate to each customer, but they have little direct connection to the 50 fields. Is a separate table correct?

Thanks again,

xeb

 

[dhookom]

There are at least two methods of dynamically selecting xebCol values. One is to create a table of xebCol values in one field and yes/no values in the other. Update all the yes/no values to Yes/checked for the xebCols you want to include in your report and then add this new table to your query and join the xebCol fields. Set a criteria under the yes/no field to -1 to include the checked xebCol values.

The other method is to create a multi-select list box based on the new xebCol table and build the " In ( )" dynamically.

Take a look at the code behind the form and you should be able to see how to set all the values to 3.

I don't know your data so I don't know where the other fields should be added. I would expect that you would have your original table with an autonumber, your date field and the other three fields. Then, rather than appending the dates to the field as it exists, use the autonumber field.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

I have uploaded a sample with more features. I created a main tables with the extra fields etc as suggested previously. I also included a list box of all col values that the user can multi-select and find the average. The download is at

http://access.hookom.net/xeb.zip.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xeb]

Duane:

I love the new stuff! Thank you so much!

I just finished working on it for four hours and I'd like to e-mail it to you. Can I do that? I can't post it here like you do.

Thanks,

xeb

 

[dhookom]

I don't post files here. The file was posted on my "haven't started yet" web site. I haven't got much time to work on this since the weekend is about over. If you want to show me your work then you can compact it, zip it, and attach it to an email to duane@hookom.net.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.