Join Query not joining (1 Viewer)

chizzy42

Registered User.
Local time
Today, 16:57
Joined
Sep 28, 2014
Messages
102
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
Code:
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----------

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-------------


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--------------

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
Code:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
16,419
you already have the Hour() function:

Hour([field1]) As Hour
 

chizzy42

Registered User.
Local time
Today, 16:57
Joined
Sep 28, 2014
Messages
102
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

AWF VIP
Local time
Today, 16:57
Joined
Jul 26, 2013
Messages
9,288
Try renaming the alias as iHour - Having a function and an alias as the same name is asking for issues.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
16,419
what is the field type of Field1?
how does Field1 look like when you run qrypassRrf2 query
 

chizzy42

Registered User.
Local time
Today, 16:57
Joined
Sep 28, 2014
Messages
102
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
16,419
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

Registered User.
Local time
Today, 16:57
Joined
Sep 28, 2014
Messages
102
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
16,419
you should do it both in querypassrrf2 and queryfailrrf2.
 

chizzy42

Registered User.
Local time
Today, 16:57
Joined
Sep 28, 2014
Messages
102
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
16,419
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?
 

Attachments

  • datab.zip
    35.4 KB · Views: 441

chizzy42

Registered User.
Local time
Today, 16:57
Joined
Sep 28, 2014
Messages
102
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 db View attachment database.accdb
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:57
Joined
May 7, 2009
Messages
16,419
youre welcome!
 

Users who are viewing this thread

Top Bottom