Join Query not joining

[chizzy42]

HI,
Im trying to use some data i store in a database to give me running percentage yields to hours . What i have is a bit flaky. the code sometimes works and sometimes doesnt and when it does it doesnt tansfer to the report.
The part i thnk i have the problem with is on the join in the query Query3_joinRrf2 as the data seems to be there in the other two queries it's just when i try to join the fails dont come through joined to the hour.
The strange thing is its a copy of another bit of code which seems to work more stable. The code i have difficulty with is a copy with the names changed.
I think there's an issue with the GROUP BY statement

Hour(CDate(Format([field1],'00\:00\:00')))

as sometimes when i go to close the query i get an error Syntax error in String in query expression '00\;00')))' and to get out of
this fail i have to put a variable name in the design window..ie time : Hour(CDate(Format([field1],'00\:00\:00'))). This happens with the "good" code and sometimes i have to delete the query and redo it.
Id be grateful if someone could have a look over the code and give me some pointers on where im going wrong and generally how i could make the code more robust and streamlined.

thanks in advance for any help, the code is below

ian

###### Gives number of passes per hour ######################

--------CODE----------

SELECT Hour(CDate(Format([field1],'00\:00\:00'))) AS [Hour],(Count(*)) AS [Fail Count],Left([Field4],25) AS [Fail Desc],tblRoundrf2.Field3 AS status
FROM tblRoundrf2
WHERE (((tblRoundrf2.datein) Between (Date()+#12/30/1899 8:0:0#) And (Date()+#12/30/1899 23:59:0#)))
GROUP BY Left([Field4],25), tblRoundrf2.Field3, Hour(CDate(Format([field1],'00\:00\:00')))
HAVING (((tblRoundrf2.Field3) Like "pass"));

-------RESULT-----------

qrypassRrf2
Hour Fail Count Fail Desc status
8 24 pass
9 10 pass
10 28 pass
11 36 pass
12 2 pass
14 2 pass
15 64 pass
16 24 pass


####### Gives number of fails per hour ##################

------CODE-------------

SELECT Hour(CDate(Format([field1],'00\:00\:00'))) AS [Hour], (Count(*)) AS [Fail Count], tblRoundrf2.Field3 AS status
FROM tblRoundrf2
WHERE (((tblRoundrf2.datein) Between (Date()+#12/30/1899 8:0:0#) And (Date()+#12/30/1899 23:59:0#)))
GROUP BY tblRoundrf2.Field3, Hour(CDate(Format([field1],'00\:00\:00')))
HAVING (((tblRoundrf2.Field3) Like "fail"));

--------RESULT-----------------

qryfailRrf2
Hour Fail Count status
8 12 Fail
9 6 Fail
10 2 Fail


##########Join query to join number of fails to number of hours###########

--------CODE--------------

SELECT qrypassRrf2.Hour, qrypassRrf2.[Fail Count] AS pass, IIf(([fc] Is Null),0,[fc]) AS fail, 
Round(([pass]/([fail]+[pass]))*100,2) AS yield, qrypfailRrf2.[Fail Count] AS fc
FROM qrypassRrf2 LEFT JOIN qrypfailRrf2 ON qrypassRrf2.Hour = qrypfailRrf2.Hour
GROUP BY qrypassRrf2.Hour, qrypassRrf2.[Fail Count], qrypfailRrf2.[Fail Count];

---------RESULT--------------------

Query3_joinRrf2
Hour pass fail yield
8 24 0 100
9 10 0 100
10 28 0 100
11 36 0 100
12 2 0 100
14 2 0 100
15 64 0 100
16 24 0 100

 

[Ranman256]

time format is: "hh:nn:ss"

 

[arnelgp]

you already have the Hour() function:

Hour([field1]) As Hour

 

[chizzy42]

Hi Ranman256 i tried using that new format in the pass query and it no longer gave me passes to the hour it just lumped all passes in 0 hour.

hi arnelgp, after trying that code , i got a dialogue box stating that the expression you entered contains invalid syntax.

thnaks for the replies

 

[Minty]

Try renaming the alias as iHour - Having a function and an alias as the same name is asking for issues.

 

[arnelgp]

what is the field type of Field1?
how does Field1 look like when you run qrypassRrf2 query

 

[chizzy42]

Hi Minty tried that suggestion but still the same.

arnelgp, the field1 is date and time and records data as 25/09/2018 11:14:52

When the query is run field1 gives the hour that the passes are totalled to

thanks again

 

[arnelgp]

try this:

SELECT Hour(CDate([field1] & "")) AS [Hour],(Count(*)) AS [Fail Count],Left([Field4],25) AS [Fail Desc],tblRoundrf2.Field3 AS status
FROM tblRoundrf2
WHERE (((tblRoundrf2.datein) Between (Date()+#12/30/1899 8:0:0#) And (Date()+#12/30/1899 23:59:0#)))
GROUP BY Left([Field4],25), tblRoundrf2.Field3, Hour(CDate([field1] & ""))
HAVING (((tblRoundrf2.Field3) Like "pass"));

 

[chizzy42]

Hi arnelgp, when i ran that query instead of getting row for each hour with the passes all that was returned was

Hour Fail Count Fail Desc status
0 189 pass

 

[chizzy42]

View attachment datab.accdb

hi' ive attached a minimum database of the issues ive been seeing

 

[arnelgp]

you should do it both in querypassrrf2 and queryfailrrf2.

 

[chizzy42]

arnelgp, i tried the code in both queries i still get 0 coming up in the hour column instead of each hour that was logged

 

[arnelgp]

there is Query1 in the attached, i just don't know if this is what you want.
im confused with Field1, what is it does it holds?

 

[chizzy42]

arnelgp, it looks like you've got it bang on, the fails/passes match in the join and the report is working to sowing the current hour and the rolling hour yields,

thanks for your patience with this, definitely learned a bit there about cleaner coding

thanks again

Here's the db if anyone's interested

sample dbView attachment database.accdb

 

[arnelgp]

youre welcome!