Query Calculation issue

[Timothy Andrepont]

I, it seems, am a beginner even though I have been working with Access for years.

Today's Issue:
Within 11 tables of number fields I want to average results found in fields with greater than zero values. Each record of each table may have up to 10 potential fields to be averaged.

How do I get started? I have no problem summing all the fields of a record, but cannot seem to get a conditional average.

I am using Access 2010.

Timothy

 

[boblarson]

Welcome to AWF.

Unfortunately it sounds like your data is not normalized and therefore is going to be a much bigger pain to deal with than it would be if it were normalized. If it were normalized then it would be easy to do.

So where does this come from and how do each of the fields relate to each other?

 

[Timothy Andrepont]

Bob,

Thanks for your quick reply.

I will study about normalizing this weekend.

I could attach the data base if I knew how in this forum. Presently the complete file is only 30KB.

Because of ignorance, I have 11 tables linked by a common ID. All linked tables are in a one to one relationship with the common ID. Each table has unique information that requires analysis, separately and combined by common ID.

 

[JHB]

I could attach the data base if I knew how in this forum. Presently the complete file is only 30KB.

First ZIP the database, because you haven't post 10 post jet.
To attach it, click on the button "Manage Attachments", (scrool a little down then you'll see it).

 

[Timothy Andrepont]

JHB,

Thanks for your reply. I only have Access 2007 at home. The file I wanted to attached is in 2010 and compresses only to 3 MB. An Excel sheet is attached. The Excel sheet is only a form but it shows where I am trying to go with Access. I have not been able to accomplish the first step in Access: average fields with values greater than zero. I you can see from the excel sheet I have a long way to go.

The Access file within 3 months will have 550 Master files.

I will go to the office in a view minutes, and see what I am able to do to reduce the Access 2010 file to less than 2.0 MB.

All your help is greatly appreciated.

Timothy

 

[Timothy Andrepont]

JHB,

Attached find PSAssm file. It compacted to 1.7MB.

I anciously await any ideas.

Again thanks for your help.

Timothy

 

[JHB]

JHB,
The Excel sheet is only a form but it shows where I am trying to go with Access. I have not been able to accomplish the first step in Access: average fields with values greater than zero. I you can see from the excel sheet I have a long way to go.

The Access file within 3 months will have 550 Master files.
All your help is greatly appreciated.

Timothy

I couldn't open your Excel file, I've only MS-Access in version 2010, the rest is Office 2000, (Excel and Word etc.).

 

[Timothy Andrepont]

JHB,

I saved the Excel file to 97/2003 format and did not zip.

Let me know if you can open it now.

Thanks again for looking into this for me.

Timothy

 

[JHB]

Yes can open it now, but can not exactly figure out what you are trying to do in MS-Access.
I think you should ask some more specific questions, to where you have problems / difficulties, with references to where the values ​​come from and what result you want.

 

[Timothy Andrepont]

All,

I have been wondering why I have not heard back from anyone. Now I see my last two post are not in the record.
________
I am unable to understand why The "Total" field of Query "QT01BSZ" is giving me totals using the "and" arrgument instead of the simple "+" requested.
_________
Any ideas?
_____
Timothy

 

[boblarson]

I've been unable to visit the forum for a while. But I have to say that I'm not sure what to tell you. Your inputs are quite complex and, from what I remember of your database (I did check back when you first uploaded it but then had issues which prevented me from coming to the site) it is in such a state as to make my head spin.

From what I've seen, unless you are a very competent VBA programmer, you are way over your head with this. To fix this and make it work how you want without going through all of what you are currently going through trying to bandaid it all together using massive numbers of queries, it would be hours and hours to work on.

But to answer your question about the + , you need to use the NZ function to handle NULLs. If you add one field which is NULL to a field which has a value, the value is then NULL. Nulls do that. So, like this for example:
Nz([BS-Fence],0) + Nz([BS-Safety0],0)

But I have other news for you around that. Don't use dashes in names as that, even with the square brackets, will cause problems with Access thinking you want to subtract something. Use UNDERSCORES instead- [BS_Fence].

And another thing, you can't use aliases like
IIf([T01BuildingSite]![BS-Fence]=0,-1) AS [BS-Fence0],
and then use that Alias in the same query. For FIELDS which use expressions, you need to either use the actual expression in the other field or save this query without the calculated fields which depend on the aliases and then use this query in another one and create those calculations there.

 

[Timothy Andrepont]

Bob,

I just go back to the office.

Thanks so much for your suggestions. I will begin to implement your suggestions.

I hope to get back to you with success.

Timothy