How to sum and calculate from tables? (1 Viewer)

tihmir

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
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.
 

Attachments

  • splitform.PNG
    splitform.PNG
    19.8 KB · Views: 89

tihmir

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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

AWF VIP
Local time
Today, 10:55
Joined
Mar 9, 2014
Messages
5,423
@tihmir, still an issue to resolve?
 

tihmir

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
@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

AWF VIP
Local time
Today, 10:55
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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.
 

Attachments

  • 1.png
    1.png
    28.3 KB · Views: 85
  • 2.png
    2.png
    17.4 KB · Views: 80

June7

AWF VIP
Local time
Today, 10:55
Joined
Mar 9, 2014
Messages
5,423
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

MVP / VIP
Local time
Today, 18:55
Joined
Jan 14, 2017
Messages
18,186
Rather than export a report to Excel, just export your query results
 

tihmir

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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)"
 

Attachments

  • 1.png
    1.png
    18.2 KB · Views: 78
  • 2.png
    2.png
    22.9 KB · Views: 83

June7

AWF VIP
Local time
Today, 10:55
Joined
Mar 9, 2014
Messages
5,423
1=1 evaluates to True and every record would retrieve. I've never used this.

Provide latest version of db.
 

tihmir

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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:
 

Attachments

  • Database_EN_v7.1.accdb.zip
    146.4 KB · Views: 92
Last edited:

June7

AWF VIP
Local time
Today, 10:55
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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

AWF VIP
Local time
Today, 10:55
Joined
Mar 9, 2014
Messages
5,423
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:
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;
 
Last edited:

tihmir

Registered User.
Local time
Today, 11:55
Joined
May 1, 2018
Messages
257
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:
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
 

Attachments

  • 1.png
    1.png
    15.5 KB · Views: 76
  • 2.png
    2.png
    17.3 KB · Views: 72
  • Database_EN_v7.2.zip
    166.3 KB · Views: 70

June7

AWF VIP
Local time
Today, 10:55
Joined
Mar 9, 2014
Messages
5,423
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.
 

Users who are viewing this thread

Top Bottom