Problem in the design report

[azhar2006]

Hello all

Is it possible to work this report?
I enclosed a picture of the report


And also annexed the databaseDatabase.zip

[Dal Jeanis]

You need to post the layouts of the underlying data tables before we can answer the question

MyTable
   MyKey    PK
   MyField1  Text
   MyField2  (FK to table MyOtherTable)

[azhar2006]

Thank you for your interest
Dal Jeanis

There is a database annex

[June7]

Looks like a CROSSTAB query could be relevant and a report using Grouping & Sorting features.

However, designing a perpetually stable report based on CROSSTAB can be difficult.

[azhar2006]

Thank you JUNE

Do we change the tables

[June7]

I don't know your database so hard to address that question about design.

But offhand, I would say no, do not change tables. CROSSTAB is a query to manipulate data.

[azhar2006]

I'm confused
But I found in one of the sites
This example

[June7]

That appears to be doing the opposite of the output shown in your image.

Post an example of your raw data. If the image is the result you want, we can then advise how to get there.

[Dal Jeanis]

I reviewed your main form "Data" and the subforms and suggest the following:

SUBFORM MOLE

Either change the title of the GC average calculation on your "Mole" form to "Total", or change your GC average calculation to

=([C2%(Mole)]+[C3%(Mole)]+[C4%(Mole)]+[C5%(Mole)]+[C6%(Mole)])/5

SUBFORMS FRMSPG AND SUMFRMSPG

Your frmspg subform at the far right of the "Data" form is showing the same data as the sumfrmspg at the bottom left. The titles look correct on the frmspg, except you need to give a meaningful name, such as "Total", to Expr1 in the query and on the form.

The sumfrmspg subform looks to have mostly wrong titles, and is redundant data when compared to frmspg.

REPORT LAYOUT

I reviewed your requested report, and I can't find any way to relate the data in your sample report layout with the actual fields and values in your test data.

The RVP in your sample report does not look anything like the values for RVB calculated by your queries.

The SPGR in your sample report does not look anything like the values for SPGR calculated by your queries.

There is no data in the database to get 1a for Copper or NEL for Sulphur.

I can only guess that the headings are intended to be HH/DD where Nov 11th at 08:00 AM is 08/11???

ODD TABLE

There is an empty "Time" table (not a good name since it's a reserved word), and I can't see what it is intended to be used for. It appears to be intended to link the Data and Mole tables, although the TimeID in the mole table contains an actual time rather than a foreign key to that Time table.

[Dal Jeanis]

If you can explain what the column headings are supposed to be, then I can probably help you figure out your report.

[azhar2006]

Thank you very much Dal Jeanis
On those tips
Can you help me if you can.
As for the field average
For example,
I have a query result-set with several columns: A, B, C ,D. All Four of these
columns display numbers. From this query, which I will call the "SetUp"query, I want to query it and obtain the average for each of
the columns A, B , C ,D. However, some of the values in columns A, B , C ,D are zero. If I use
the standard 'Avg' function in Access, it averages
the column AND INCLUDES THE ZEROS IN THE AVERAGE. How can I get an average of just all the
non-zero entries?

There is also another problem, a report
There is no problem of making it vertically
Is it possible example
Thanks again,

[June7]

Nulls are ignored in Sum and Avg functions. Convert the 0 to null with an expression and Avg the constructed field.

IIf([A]=0, Null, [A])

[Dal Jeanis]

So, your desired average calculation could look something like this

=IIF(([A]+[B]+[C]+[D])=0,0,(([A]+[B]+[C]+[D])/(IIF([A]=0,0,1)+IIF([B]=0,0,1)+IIF([C]=0,0,1)+IIF([D]=0,0,1))))

The explanation for this equation goes like this:
A) If all four values are zero, then return 0 as the average. This avoids the error where the divisor (bottom number) of the next calculation is zero.
B) Add up all four values.
C) Count up the number of values that are not zero.
D) divide B by C and return that as the average.

The above code assumes the numbers are all positive, which should be okay for your application. Use this if your values might ever be negative.

=IIF(([A]=0 And [B]=0 And [C]=0 And [D]=0),0,(([A]+[B]+[C]+[D])/(IIF([A]=0,0,1)+IIF([B]=0,0,1)+IIF([C]=0,0,1)+IIF([D]=0,0,1))))

[Dal Jeanis]

azhar - I still don't know what those headings are suppose to be. In your sample, what does 11/11 and 12/11 mean? is that 11 November and 12 November? Are the values in the report supposed to be the averages for the day?

[azhar2006]

Thank you very much Dal
This report is
There are checking for gas pumps
This equipment is checked four times per day, for example,
c2 checked at 12:00 and at 02:00 and 04:00 and 06:00 on 11/30/2013
There is a report of one day enclose you a picture of it
The report is a total of ten days only includes one field which average