State Reports - Help

[TroxlSha]

I need to create a report to turn into the state as part of normal reporting procedures. The issue I am have is getting the format to show properly.



The database is entered as follows: Each day samples for 4 separate chemicals are taken every 4 hours, thus the data is entered into the table as such.

Date - Time - Sample 1 - Sample 2 - Sample 3 - Sample 4
10/20 0800 1 1 1 1
10/20 1000 1 1 1 1
10/20 1200 1 1 1 1

and so on...

The report needs to read as follows, but this is where I am stuck any help would be greatly appreciated.


.............................Sample1.............. ..................................Sample2......... ....................
Date 0400 - 0800 - 1000 - 1200 - 1600 - 2000 | 0400 - 0800 - 1000 - 1200 - 1600 - 2000 |....
10/01
10/02
10/03
10/04
...
10/31

Basically it needs to report similar to an excel spread sheet with the dates going down and the time readings for each sample across.

[AlexHedley]

Have you thought about using a crosstab query?

[June7]

A crosstab will be difficult with that structure because you want a 2-tier column heading. I think Excel can handle that but Access has no intrinsic facility for it. One possible approach might be to do 4 crosstab queries then join them on the common date value.

[Dal Jeanis]

Looks like you need a crosstab query.

Two questions first:
1) Is the time in a separate field, or is there a single date/time field?
2) Do the four types of samples have to be across on the same page, or can they be separate reports?

[Dal Jeanis]

Crosstabs are cooler, when they work, but here's the trivial, brute-force way to get what you need, assuming that the samples are numeric. You'll have to adjust all the table and field names, but you should be able to work it out.

SELECT
   [ReadDate],
   SUM(IIF([ReadHour]=0000,[SampleA],0)) AS A0000,
   SUM(IIF([ReadHour]=0400,[SampleA],0)) AS A0400,
   SUM(IIF([ReadHour]=0800,[SampleA],0)) AS A0800,
   SUM(IIF([ReadHour]=1200,[SampleA],0)) AS A1200,
   SUM(IIF([ReadHour]=1600,[SampleA],0)) AS A1600,
   SUM(IIF([ReadHour]=2000,[SampleA],0)) AS A2000,
   SUM(IIF([ReadHour]=0000,[SampleB],0)) AS B0000,
   SUM(IIF([ReadHour]=0400,[SampleB],0)) AS B0400,
   SUM(IIF([ReadHour]=0800,[SampleB],0)) AS B0800,
   SUM(IIF([ReadHour]=1200,[SampleB],0)) AS B1200,
   SUM(IIF([ReadHour]=1600,[SampleB],0)) AS B1600,
   SUM(IIF([ReadHour]=2000,[SampleB],0)) AS B2000,
   SUM(IIF([ReadHour]=0000,[SampleC],0)) AS C0000,
   SUM(IIF([ReadHour]=0400,[SampleC],0)) AS C0400,
   SUM(IIF([ReadHour]=0800,[SampleC],0)) AS C0800,
   SUM(IIF([ReadHour]=1200,[SampleC],0)) AS C1200,
   SUM(IIF([ReadHour]=1600,[SampleC],0)) AS C1600,
   SUM(IIF([ReadHour]=2000,[SampleC],0)) AS C2000,
   SUM(IIF([ReadHour]=0000,[SampleD],0)) AS D0000,
   SUM(IIF([ReadHour]=0400,[SampleD],0)) AS D0400,
   SUM(IIF([ReadHour]=0800,[SampleD],0)) AS D0800,
   SUM(IIF([ReadHour]=1200,[SampleD],0)) AS D1200,
   SUM(IIF([ReadHour]=1600,[SampleD],0)) AS D1600,
   SUM(IIF([ReadHour]=2000,[SampleD],0)) AS D2000
FROM
   MyTable
GROUP BY [ReadDate]
ORDER BY [ReadDate];

[TroxlSha]

Looks like you need a crosstab query.

Two questions first:
1) Is the time in a separate field, or is there a single date/time field?
2) Do the four types of samples have to be across on the same page, or can they be separate reports?

1.) One record consist of the Current Date | Time of the Sample | Sample 1 Reading | Sample 2 Reading | Sample 3 Reading | Sample 4 Reading
2.) The report must look identical to the form given by the state which makes this difficult, I know.

The report should show as follows:

Date | Sample 1 - 0000 | Sample 1 - 0400 | Sample 1 - 0800 | Sample 1 -1200 | Sample 1 - 1600 | Sample 1 - 2000 | Sample 1 - 0000 ... and so on for each sample of the 4 samples on the same report.

I looked into a Crosstab, but it seems I would only be able to display one sample, rather than all 4 on the same report.

[June7]

See post 3 regarding use of crosstab. If you want every time slot to have a column then each time slot must have data. If that is the case, then the crosstab approach should be fairly simple.

Dal's suggestion is to emulate a crosstab using expressions. Here is an example of that: http://datapigtechnologies.com/flashfiles/crosstab.html

[TroxlSha]

See post 3 regarding use of crosstab. If you want every time slot to have a column then each time slot must have data. If that is the case, then the crosstab approach should be fairly simple.

Dal's suggestion is to emulate a crosstab using expressions. Here is an example of that: http://datapigtechnologies.com/flashfiles/crosstab.html

Thank you. I will use this in attempt to make the table and see how it works, I have one question however, would I be able to make a report from this query so that I can create the exact form heading etc to submit to the state?

[June7]

Yes, either approach should provide the dataset you require for report.

In what form must you submit to state - PDF, Excel, Word?

[TroxlSha]

It doesn't really matter what format, as it is printed and a paper copy is submitted. The major factor is getting the form to look identical; which the form is actually an excel sheet which is why I figure I should be able to make this in access some how or another.

[June7]

Let us know how it goes with the suggested methods.

[TroxlSha]

I certainly will. I won't be able to get to it until tonight, but I will let you know how it goes tomorrow morning.

Thank you for the help. Hopefully this will work - if so it will save an ungodly amount of time as there are multiple reports very similar to this that take hours to input the data by hand each month.

[TroxlSha]

I am trying to do the cross tab query utilizing the video, but it keeps sending back an #Error. My expression is as follows, perhaps I made a mistake somewhere

IIF([timeStamp]="12:00 AM", [Clarifier NTU Plt 1], 0)

My idea was that this would search the timeStamp field for the 0000 hour and return Clarifier NTU Plt 1 reading.

[June7]

Is the timeStamp field a text or date/time type? You have the criteria delimited as text and that won't work with a date/time field. The sample you posted looks like text because there is no year part.

[TroxlSha]

timeStamp is defined as a date/time type while the Clarifier is a number field