Query/VB Script for Time Block for a Report 1

[kmkland]

What I am trying to accomplish:
I need to produce a report which shows the number of incidents which occurred within 2-hour blocks during a 24-hour period. So, just to have a visual...

	               [b]2-Hour Blocks	
Procedure #	0000	0200	0400	0600	etc…..
4.1[/b]		     2		4	
[b]6.3[/b]	         1		5		
[b]8.4[/b]	                  2	  4       8	
[b]9.2[/b]		     3	    4

The Table names and Field names:
Primary Table = tblProcedures
Fields = Procedure#

Linked Table = tblNonconformance
Fields = Procedure#
NonconformanceTime (00:00 format)

Without resorting to creating another field in tblNonconformance for Time Blocks and then updating that field using parameter queries from the data from NonconformanceTime, is there any other way of accomplishing what I need to accomplish? I'm strictly a newbie when it comes to VB, so anyone volunteering to help would probably need to explain the steps I would need to take in detail.

I appreciate any and all help on this matter.

Kim

 

[PHV]

Something like this ?
SELECT [Procedure#],
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #00:00:00# And #01:59:59#) AS [0000],
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #02:00:00# And #03:59:59#) AS [0200],
...
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #220:00:00# And #231:59:59#) AS [2200],
FROM tblNonconformance A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

When I ran this query, I received the following error message:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
Then, the "FROM" is highlighted....

Kim

 

[PHV]

My bad, sorry for the typo.
Replace this:
AS [2200],
By this:
AS [2200]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

Hmm...
I'm still getting the same error message.
Kim

 

[PHV]

Can you please post the sql code and which FROM is highlighted ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

Here is the code, and since I am not too keen on SQL as I would like to be, but yet still knowledgeable in it enough to know when something is missing, I think that there may be something I am omitting from this statement:

SELECT [Procedure#],
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #00:00# And #01:59#) AS [0000]
[COLOR=red][b]([/b][/color]SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #02:00# And #03:59#) AS [0200]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #04:00# And #05:59#) AS [0400]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #06:00# And #07:59#) AS [0600]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #08:00# And #09:59#) AS [0800]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #10:00# And #11:59#) AS [1000]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #12:00# And #13:59#) AS [1200]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #14:00# And #15:59#) AS [1400]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #16:00# And #17:59#) AS [1600]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #18:00# And #19:59#) AS [1800]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #20:00# And #21:59#) AS [2000]
(SELECT Count(*) FROM tblNonconformance B WHERE B.[Procedure#]=A.[Procedure#] AND B.NonconformanceTime BETWEEN #22:00# And #23:59#) AS [2200]
FROM tblNonconformance A;

The left parenthesis in the 3rd line is the new highlighted area, since I removed the commas.

I do appreciate your help on this...
Kim

 

[PHV]

I said to remove the last comma only

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

Brilliant PH!
Sorry I got confused on your reply earlier. I wasn't too clear on whether it was every line or just the last one.
Thanks so much!!!!
Kim

 

[kmkland]

I am having problems getting the data to show up correctly. Somehow, the query is counting the results for 1 procedure 3 times (which is actually how many time blocks this procedure uses).
This is what the results look like:

[b]Procedure# ...0800 1000   1200   1400   1600    1800   2000   2200
4.10[/b]	        1	  0	   1	   0	   0	   0	   0	   1
[b]5.10[/b]	        0	  0	   0	   0	   0	   1	   0	   0
[b]10.1[/b]	        0	  0	   1	   0	   0	   0	   0	   0
[b]4.10[/b]	        1	  0	   1	   0	   0	   0	   0	   1
[b]4.10[/b]	        1	  0	   1	   0	   0	   0	   0	   1

This is what the results should look like:

[b]Procedure# ...0800  1000  1200   1400   1600   1800   2000   2200
4.10[/b]	        1	  0	   1	   0	   0	   0	   0	   1
[b]5.10[/b]	        0	  0	   0	   0	   0	   1	   0	   0
[b]10.1[/b]	        0	  0	   1	   0	   0	   0	   0	   0

So, basically, the query is duplicating rows.

Anyone who can assist on this matter, I would be totally grateful!!!

Thanks in advance.
Kim

 

[PHV]

Replace this:
SELECT [Procedure#],
By this:
SELECT DISTINCT [Procedure#],

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

Brilliant!
Worked like a charm!
Kim