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

 

[hneal98]

If it works in the report, there is not reason to change it.

 

[xeb]

Hi:

I don't understand.

My report expressions are not producing results by the week which is what I'm trying to do. It was suggested that I use a query to do that. However, the query that was suggested (the one posted here) does not return the correct average.

I only posted my report expression to show how I get the correct average on my reports.

Thanks,

xeb

 

[hneal98]

OK. What about the query is not working? It looks like the query is ok, so I am not sure what is happening at this point. Why do you have so many fields?

 

[dhookom]

I think if you tables were properly normalized, you might not be having such a huge issue with your calculations.

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

 

[xeb]

Thanks for answering.

What I'm getting with the query is the sum of all my fields (shown as a record) divided by the number of records.

What I need is the sum of each field divided by the count of each field.

In other words, the query posted here looks liks this:

Week   WeekTotal   WeekRecords   WeekAvg

2      468         5             93.6
3      374         3             124.6 
4      105         3             35

It's just what I want, only the average is the WeekTotal divided by the WeekRecords.

My table looks like this:

Date     1   2   3   4   5   6   7   8   9   10   Etc.

1/5/04   1   2   3   1   2   3   1   2   3   1    
1/6/04   2   3   1   2   3   1   2   3   1   2
1/7/04   3   1   2   3   1   2   3   1   2   3

For what I need, the following would be true:

For 1/5/04 the sum would be 19 and the average would be 1.9. (The sum divided by the count of each field.)

For all three dates the sum is 60 and the average is 2.0. (The sum divided by the count of every field.)

If I'm not explaining things right, I'm sorry, but I'm a layperson with this stuff.

My db is structured with 50 fields because of the way I have to make use of the data in my reports.

In other words, one report returns averages (the sum of all fields divided by the count of all fields) for the following:

1-10
11-20
21-30
31-40
41-50
1-50

Another report returns averages for the following:

1,11, 21, 31, 41
2, 12, 22, 32, 42
3, 13, 23, 33, 43
Etc.

These are just examples, but as you can see, each report expression I have combines the fields in a wide variety of configurations that analyze my data in a way that is useful to those who use it.

Thanks for your help.

xeb

 

[xeb]

Hi:

I still need help with this.

Thanks,

xeb

 

[dhookom]

I just wouldn't put any effort into a non-normalized structure like this. As a temporary work around, I would create a temporary, normalized table and append records from your current table into it. Then use your normalized table(s) to create your reports.

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

 

[hneal98]

xeb,

are you still needing help with this? What dhookom is trying to say is that your problem would be quite simple to solve if your database were set up normalized. Do you know what normalized is?

In case you don't, your table mentioned above would be set up like this:

Date Week DataValue
01/05/2004 1 1
01/06/2004 1 2
01/07/2004 1 3
01/05/2004 2 2
01/06/2004 2 3
01/07/2004 2 1
01/05/2004 3 3
01/06/2004 3 2
01/07/2004 3 1
01/05/2004 4 1
01/06/2004 4 2
01/07/2004 4 3
01/05/2004 5 2
01/06/2004 5 3
01/07/2004 5 1
01/05/2004 6 3
01/06/2004 6 2
01/07/2004 6 1
01/05/2004 7 1
01/06/2004 7 2
01/07/2004 7 3
01/05/2004 8 2
01/06/2004 8 3
01/07/2004 8 1
01/05/2004 9 3
01/06/2004 9 2
01/07/2004 9 1
01/05/2004 10 1
01/06/2004 10 2
01/07/2004 10 3


Then you could simply run a query that would sum up all of data for for the week and count the data for the week and calculate your averages. You would do this in your report, since if you try to do this in a query and include the individual date it self, you would have problems.

Let me know if you need further help.

 

[dhookom]

hneal98,
Thanks for taking the time to "illustrate" my thoughts.

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

 

[hneal98]

No problem. Not that you really needed it. I just was not sure if he/she understood it.

 

[xeb]

I'm sorry to bother you again, and I'm sure you're right, but I still don't get it.

We enter a daily record for each customer using a form that has a date and the 50 fields on it. Either a 1, 2, or 3 are entered into each field on the form. We don't know, and at the point of data entry, don't care, what week it is.

Then, the averages on the data must been run horizontally
and vertically in many different configurations using different reports.

Anyway, so each field I now have should be a record with its own date? Do I have to enter the date for each field? Do I have to enter the week number?

Thanks,

xeb

 

[dhookom]

What do the 50 fields correspond to? Is each field, a different customer? If you have a date field, then you should be able to calculate the week number.

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

 

[xeb]

Thanks for answering.

The 50 fields correspond to one customer. Each customer has their own database.

 

[dhookom]

I expected to get a reply like "the 50 fields correspond to 50 different products" or "the 50 fields correspond to survey questions regarding our services" or ...

I'm not sure why each customer should have their own database. Do you have to duplicate reports and queries are all mdbs linked to a single "front-end" mdb?

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

 

[xeb]

Each customer must have their own database. They are not connected. The "master" database program is copied an used where necessary by each employee.

Employees receive from customers hand written reports with dates and 50 numbers. Those 50 numbers must be entered using a form. The way that has been suggested here would require us to have a form with a date field and one number field and employees would have to enter a date and one number 50 times for each date for each customer report they receive. Is that correct?

Thanks

 

[dhookom]

This could easily be set up with a subform so the users would enter the date once and click a button to create 50 new records. All they would have to do then is to enter the same number of values they entered in the past.

You still have not answered my question regarding what the 50 different numbers correspond to. Is this a secret ;-)? Do they correspond with the 50 different states? Have you set an optimum sampling of 50 specimens?

I'm trying to help but you are not making this very easy.

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

 

[xeb]

Duane:

I'm sorry to be taking up your time with this. I appreciate your interest and help.

The data is customer feedback.

It is reported on a hand written paper grid and the form we use is set-up identical to the grid which makes data entry easy.

I guess the reason the table got set-up the way it did was because of the way it was going to be used. Entire columns, entire rows, parts of columns, parts of rows, etc. in many different configurations for specific dates, specific date ranges, and for all records are done on a regular basis. The expressions being used have always returned the correct averages.

If it's best to change things I want to do that. I just want to be sure that the many different things we do with the data can still be done.

Again, thanks.

xeb

 

[dhookom]

So if I enter a "1" in the first column (field [1]) for one customer, it may have a different meaning for a different customer?

Do the meanings of the columns vary from one customer to the next? If so, is there any data some place that says that for customer "X" on date "m/d/yy" field [1] means "how would you rate your breakfast this morning?"

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

 

[xeb]

Duane:

The numbers 1, 2, or 3 all mean the same thing in each database. It's how we need to analyze it that makes the difference. For instance, our reports using a parameter date query, parameter date range query, or simply the all records option, might do the following:

1. Average every record in every field.
2. Average every record in fields (1-10), (11-20), etc.
3. Average records (1, 11, 21, 31, 41), (2, 12, 22, 32, 42), etc.
4. Average records (1, 2, 11, 12, 21, 22, 31, 32, 41, 42), (3, 4, 13, 14, 23, 24, 33, 34, 43, 44), etc.

There are many other variations, but I think this gives you an idea of what I mean. We need the flexability to be able to combine any of the 50 fields with any other one(s). A report that is one way now for one customer could change next week or next month.

Thanks,

xeb