How to sum and calculate from tables?

[tihmir]

That's more tricky.
It will need dynamic Sql for the form record source when the search filters are applied.
I'll try and look at it later if I have time.

Тhank you very much in advance, isladogs!



Sorry I didn't understand what you mean

BTW which version of the ESF did you use as it went through many iterations and what you have doesn't look familiar

 

[isladogs]

Sorry. Just remembered this and will try and look at it tomorrow.

My comment was that I didn't recognise the code use in your 'split form'.
In fact its not a split form at all.
The screenshot shows a split form.

 

[tihmir]

Sorry. Just remembered this and will try and look at it tomorrow.
My comment was that I didn't recognise the code use in your 'split form'.
In fact its not a split form at all.
The screenshot shows a split form.

The code I use in my "Split Form" is from http://allenbrowne.com/casu-08.html. For my purpose it works perfectly. I use it often.

Sorry. Just remembered this and will try and look at it tomorrow.

I am very grateful for your help, isladogs. I hope there is a solution and an opportunity for the task.

 

[isladogs]

Actually you are using a split form but hiding the single form part of it ...which defeats the object of using it really!
Possibly easier to just use a continuous subform?

With apologies, I really don't have the time to sort this out for you.
Rather than keep you waiting I've asked in the VIP forum and hopefully someone else will take it on.

In the meantime, have a look at the way I build multiple SQL strings from selected values in textboxes / combos in the IncidentAnalysis example app
https://www.access-programmers.co.uk/forums/showthread.php?t=302189
This includes selections based on date ranges

 

[tihmir]

Possibly easier to just use a continuous subform?

Thank you for the advice. I'll try.

Rather than keep you waiting I've asked in the VIP forum and hopefully someone else will take it on.

I keep waiting for help and advice. When you have free time about the decision, please write.

 

[June7]

@tihmir, still an issue to resolve?

 

[tihmir]

@tihmir, still an issue to resolve?

Yes, June7. I am still waiting for any solution on my problem - to sum up for each type of objectrecords in my qryCountInspectionTypes_Crosstab (as I showed in pic2 in my post #19). Аnd then I can search by name and date how many checks have been completed.
If there is another solution to achieve this I will be pleased to learn it.

 

[June7]

Build a report with the CROSSTAB query and use report Sorting & Grouping features with aggregate calcs in textboxes and apply filter to report when opens.

OR

Build a CROSSTAB that doesn't include Worker and DateTask fields as Row Headers but uses those fields in WHERE clause. Will have to use Column Headers or Parameters - review http://allenbrowne.com/ser-67.html

 

[tihmir]

Build a report with the CROSSTAB query and use report Sorting & Grouping features with aggregate calcs in textboxes and apply filter to report when opens.

OR

Build a CROSSTAB that doesn't include Worker and DateTask fields as Row Headers but uses those fields in WHERE clause. Will have to use Column Headers or Parameters - review http://allenbrowne.com/ser-67.html

Thank you for the advice, June7. I'll try to do it

 

[tihmir]

I built a report with the 1st method and now it works fine (Thanks again June7).

Build a report with the CROSSTAB query and use report Sorting & Grouping features with aggregate calcs in textboxes and apply filter to report when opens.

What I'm trying to do now is this - When I do not set a filter all objects appear and those that do not have inspections.When I filter with the filterform I want the objects that are not inspected (with value 0) to appear as well on the report. I want all objects (objects with Value=0 inspections) to be displayed even when I filter by date and worker.
[FONT=&quot]The SQL on the Crosstab is:[/FONT]

TRANSFORM Avg(qryTypes.CountCheckTypes) AS AvgOfCountCheckTypes
SELECT qryTypes.CodeObjectID, qryTypes.ObjectType, qryTypes.DateTask, qryTypes.Worker, Avg(qryTypes.CountCheckTypes) AS [Total Of CountCheckTypes]
FROM qryTypes
GROUP BY qryTypes.CodeObjectID, qryTypes.ObjectType, qryTypes.DateTask, qryTypes.Worker
PIVOT qryTypes.TypeOfCheck;

The VBA code оn the cmd button is http://allenbrowne.com/casu-08.html - Method 2: Form for entering the dates
And now I save the report in pdf

DoCmd.OutputTo acOutputReport

Is it possible to save the report to excel and how?

Thanks in advance.

 

[June7]

Try joining CROSSTAB query to a table of all Objects, probably a RIGHT JOIN.

Can export a report to Excel. However, the result can be less than satisfactory for complex reports.

DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, strFilePath

 

[isladogs]

Rather than export a report to Excel, just export your query results

 

[tihmir]

Try joining CROSSTAB query to a table of all Objects, probably a RIGHT JOIN.

I am trying to look for a solution to this case, but so far I am not succeeding.
When I apply in my QUERY RIGHT JOIN and run it the objects that have no entries (Value=0) do not appear, but I want to be there. When I use LEFT JOIN in QUERY everything is fine.
So, when I run Crosstab query it is work fine, but...the problem is that when I use filterform to sort by dates or workers on the report it is shows exactly record between dates or for the designated work.
Is there possibly a reason for this part of VBA code:

If Trim(Me.cbo_Worker & "") <> "" Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[Worker] = '" & Me.cbo_Worker & "'"
End If

' check if the strWhere has some value
If Trim(strWhere) = "" Then strWhere = "(1=1)"

 

[June7]

1=1 evaluates to True and every record would retrieve. I've never used this.

Provide latest version of db.

 

[tihmir]

1=1 evaluates to True and every record would retrieve. I've never used this.
Provide latest version of db.

This is my latest version of db:

 

[June7]

If you apply a worker and/or date filter then of course records not matching that criteria will not be retrieved. If you still want to include all object types then need to JOIN or UNION filtered query to a dataset of all objects.

This probably means having to use a dynamic parameterized query instead of applying filter to report. I've never needed dynamic parameterized query and avoid them because they are tricky when there are multiple criteria.

 

[tihmir]

If you apply a worker and/or date filter then of course records not matching that criteria will not be retrieved. If you still want to include all object types then need to JOIN or UNION filtered query to a dataset of all objects.
This probably means having to use a dynamic parameterized query instead of applying filter to report. I've never needed dynamic parameterized query and avoid them because they are tricky when there are multiple criteria.

So, is this the best way to apply a worker and/or date filter with the dynamic parameterized query? I'm afraid I won't be able to do it. Seems pretty complicated to me. Would you do it on my latest version of db, please?

 

[June7]

I prefer you to learn and do instead of me building your db. Dynamic parameterized query is basic Access functionality and a common topic. Review https://ittrainingtips.iu.edu/acces...-using-a-filter-dialog-box-in-a-form/12/2010/

I am not sure it is best but if I understand what you require, it may be the only way.

Parameterized query that allows any or all or no parameter inputs for multiple criteria is tricky. See example at end of this article http://allenbrowne.com/ser-62.html

Dynamic parameters when CROSSTAB is involved is even trickier. Review http://allenbrowne.com/ser-67.html#Param

It is possible to emulate a CROSSTAB by using IIf() expressions in a regular aggregate query. Example:

SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker, 
Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes, Count(IIf([TypeOfCheck] Like "Current*",1,Null)) AS [Current], 
Count(IIf([TypeOfCheck] Like "Target*",1,Null)) AS Target, Count(IIf([TypeOfCheck] Like "By Reg*",1,Null)) AS ByReg
FROM (tbl_CodeOjects LEFT JOIN tbl_Objects ON tbl_CodeOjects.CodeObjectID = tbl_Objects.CodeObjectID) LEFT JOIN tbl_Inspections ON tbl_Objects.ObjectID = tbl_Inspections.ObjectID
GROUP BY tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker;

 

[tihmir]

I prefer you to learn and do instead of me building your db. Dynamic parameterized query is basic Access functionality and a common topic. Review https://ittrainingtips.iu.edu/access...-form/12/2010/
I am not sure it is best but if I understand what you require, it may be the only way.
Parameterized query that allows any or all or no parameter inputs for multiple criteria is tricky. See example at end of this article http://allenbrowne.com/ser-62.html
Dynamic parameters when CROSSTAB is involved is even trickier. Review http://allenbrowne.com/ser-67.html#Param

I totally agree with that. I learn many things every time and then practice and develop them.Thanks June7. I built Query with your code:

SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker,  Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes, Count(IIf([TypeOfCheck] Like "Current*",1,Null)) AS [Current],  Count(IIf([TypeOfCheck] Like "Target*",1,Null)) AS Target, Count(IIf([TypeOfCheck] Like "By Reg*",1,Null)) AS ByReg 
FROM (tbl_CodeOjects LEFT JOIN tbl_Objects ON tbl_CodeOjects.CodeObjectID = tbl_Objects.CodeObjectID) LEFT JOIN tbl_Inspections ON tbl_Objects.ObjectID = tbl_Inspections.ObjectID 
GROUP BY tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker;

I made the report with the Query. Now when I try to filter the query by dates and/or worker it is not display all records again. Where am I wrong? Аnd how should I filter that quey on my Filterform? I apologize very much for the many questions I ask

 

[June7]

You have to use dynamic parameters in that query then build another query that joins first query to table. Use second query as report RecordSource. Do not apply filter to report. Filtering has to be done by the query parameters.

Query parameters must refer to controls on your Filterform as shown in Allen Browne article.