Calculated Field in Query that won't work!

[Taliesyn]

I'm trying to produce a calculated field (Bradford Factor) using a count of the start date (number of incidences) and the workdays total. I've tried pretty much everything I can think of and looked at all manner of websites to no avail. The calculation works fine on the report but I want to count the number of results below and above certain values - which is when the wheels all fall off! Has anyone done this in Access or is there a simple way to transfer a result from a calculated report field to a query? Thanks :banghead:

 

[JHB]

Show some sample data and how you want the result!

 

[Taliesyn]

I've attached the database itself. I have a calculation on the AbsencebyEmployeeName report using the number of incidences of absence (Count of [BegDate]) and the number of working days ([WorkDays]) that works fine on the report but I want to have it in the query so that I can graph the results... and that's where everything falls apart! The structure is what I want but the data is "padding" to help assess the various queries and reports so there are no data protection issues!

 

[JHB]

To get the result you've in the report you can get the same with a query:
Past the below in a new query:

SELECT Datatable1.[Current Employee?], Datatable1.[Name of Employee], Datatable1.[Employee Number] AS [Datatable1_Employee Number], Datatable1.[Employee's Department], Datatable2.[Employee Number] AS [Datatable2_Employee Number], Count(Datatable2.BegDate) AS CountOfBegDate, Sum(Work_Days([BegDate],[EndDate])) AS WorkDays, Count([BegDate])*Count([BegDate])*Sum(Work_Days([BegDate],[EndDate])) AS Expr1
FROM Datatable1 RIGHT JOIN Datatable2 ON Datatable1.[Employee Number] = Datatable2.[Employee Number]
GROUP BY Datatable1.[Current Employee?], Datatable1.[Name of Employee], Datatable1.[Employee Number], Datatable1.[Employee's Department], Datatable2.[Employee Number]
HAVING (((Datatable1.[Current Employee?])=Yes));

 

[Taliesyn]

It may be politically incorrect to hint about "orientation" but I am so happy I could kiss you!! My colleague will tell you that I have been a bear with a sore head, belly, @ss and other things trying to sort this out.

THANK YOU!!!

PS As a way of saying thanks, I'm posting the database onto the sample databases for everyone that wants to use it (after a little bit of "prettification")

 

[JHB]

You're welcome - good luck!
Don't make my wife jealous!

 

[Taliesyn]

I suppose now is a bad time to let you know that I forgot that it's supposed to be a rolling 12 month query...
I tried to add
WHERE (((Datatable2.BegDate) Between Date() And Date()-365))
at the end of the query but it just returns an error message.
Sorry... :-(

 

[JHB]

Then try the below:

SELECT Datatable1.[Current Employee?], Datatable1.[Name of Employee], Datatable1.[Employee Number] AS [Datatable1_Employee Number], Datatable1.[Employee's Department], Datatable2.[Employee Number] AS [Datatable2_Employee Number], Count(Datatable2.BegDate) AS CountOfBegDate, Sum(Work_Days([BegDate],[EndDate])) AS WorkDays, Count([BegDate])*Count([BegDate])*Sum(Work_Days([BegDate],[EndDate])) AS Expr1
FROM Datatable1 RIGHT JOIN Datatable2 ON Datatable1.[Employee Number] = Datatable2.[Employee Number]
WHERE (((Datatable2.BegDate) Between Date() And Date()-365))
GROUP BY Datatable1.[Current Employee?], Datatable1.[Name of Employee], Datatable1.[Employee Number], Datatable1.[Employee's Department], Datatable2.[Employee Number]
HAVING (((Datatable1.[Current Employee?])=Yes));

 

[Taliesyn]

I cannot say "Thank you!" enough...
The query works perfectly. I have a few things to finish off on the database and then I will post it for all to use - with a special "thanks" on a splash screen to you!

 

[JHB]

It is okay, have a nice day!

 

[Taliesyn]

The database is posted on the Sample Databases page (once approved by the moderators) with a "many thanks" for your input.