How to sum and calculate from tables?

[tihmir]

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.

Thanks, June7. I'll try to solve the problem!

 

[tihmir]

Hello again, I am still looking for a solution to my problem with reporting in the appropriate form. Here's what I've built so far with the huge help of
isladogs and June7:
I have query "qryTypes_Crosstab1" and "Query1". NowI'm trying to set the "Query1" as RecordSource to report but it shows "The specified field tbl_CodeObjects.ObjectType could not more than one"

 

[isladogs]

Query1 isn't needed as those additional fields from tbl_Code_Objets are included in the crosstab.

Use the crosstab as your report record source. See attached

Note: I've not looked at anything else in your database

 

[tihmir]

Maybe I should explain a little more why I added those additional fields from tbl_Code_Objets in Query 1. My idea is when I generate a report, it must be displayed all the entries in the fields "CodeObjectID" and "ObjectType" аnd then if there are records for the specifically filtered worker and/ or date to show the Inspections for the corresponding to the respective objects. If there are no records (inspections) for the relevant period or worker show value "0" for relevant Objects.

 

[isladogs]

OK. Try this

 

[tihmir]

OK. Try this

Thank you again, isladogs. Works great. That's what I wanted.

Now I try to set up the second criteria - Date range in the "qryTypes_Crosstab1"but it doesn't work:

Between [Forms]![fm_Report2]![txt_DateFrom] And [Forms]![fm_Report2]![txt_DateTo]

I want to set up criteria like that:
If txt_DateFrom and txt_DateTo are null, don't apply any filter
If txt_DateFrom and txt_DateTo are not null, filter between those two
If txt_DateFrom is null and txt_DateTo is not null, filter from the beginning of time to txt_DateFrom
If txt_DateFrom is not null and txt_DateTo is null, filter from txt_DateFrom to the end of time

(txt_DateFrom and txt_DateTo are the text box fields on the form "fm_Report2")

 

[isladogs]

Tihmir
Please take the time to study and understand how I got that to work.

As for filtering by date range, please look at the incident analysis example I provided in this thread. https://www.access-programmers.co.uk/forums/showthread.php?t=302189.
That (and the other example provided) show how I build sql strings dynamically to manage one or many filter criteria. If you use that approach you will be able to do any example yourself, no matter how complex.

Good luck with your project

 

[tihmir]

As for filtering by date range, please look at the incident analysis example I provided in this thread. https://www.access-programmers.co.uk...d.php?t=302189.

isladogs,
When I try to open the IncidentAnalysis db it shows me this Error:

 

[isladogs]

Hmm. Never seen that before with this app.
The attached screenshot is what you should see when you click the Day Of Week button.

Which version/bitness of Access are you using?
Check for missing references in the VBE
If that doesn't solve it, debug and see what it shows.
Also see if an error occurs if you click another button such as Location

 

[tihmir]

Hmm. Never seen that before with this app.
The attached screenshot is what you should see when you click the Day Of Week button.
Which version/bitness of Access are you using?
Check for missing references in the VBE
If that doesn't solve it, debug and see what it shows.
Also see if an error occurs if you click another button such as Location

I use MS Access 2016, 64-bit. For every button, the error is the same as the one I showed in the picture. Just the exit button does not show me an error!
By the way, I should mention that when I looked carefully at the latest version you built - Database_EN_v7.4, if I don't apply folter "cbo_Worker" No records appear in the report, but all records should appear when the filter is not applied. It does not work properly!

 

[isladogs]

I use MS Access 2016, 64-bit. For every button, the error is the same as the one I showed in the picture. Just the exit button does not show me an error!
By the way, I should mention that when I looked carefully at the latest version you built - Database_EN_v7.4, if I don't apply folter "cbo_Worker" No records appear in the report, but all records should appear when the filter is not applied. It does not work properly!

With regard to my app, you didn't tell me whether there were reference issues.
Did you try debugging as I requested? If so what did it show?
As I don't see those errors, I'm unable to advise unless you help me out.

There are some 64-bit API declaration issues in the module modResizeForm but I can't see how that would cause the error you described.

EDIT: Just tested it in A2016 64-bit with no errors

With your app, I started with your version 7.2 which didn't work and did what you asked for v7.4. The issue you've now raised wasn't mentioned before. Its caused by the filter in the Worker field of your crosstab query which I had nothing to do with. What have you done to try and solve this yourself?

 

[tihmir]

Did you try debugging as I requested? If so what did it show?

I tried with Debug, but when I start it no error occurs.

With regard to my app, you didn't tell me whether there were reference issues.

I'm sorry I didn't mention it in the specific comment, because I wrote about it in the comments above.

What have you done to try and solve this yourself?

I'm quite a beginner and trying to learn from the things you do and the suggestions you offer. This task is impossible for me and my capabilities. This is the reason I ask for help!
I need help making a report with the query in which report to be able to filter by date and/ or by worker. But when no filter is used, all records are displayed, as I mentioned in the posts above. I apologize if I have not explained correctly

 

[isladogs]

Tihmir
That still didn't answer either of my questions. Perhaps you didn't understand what I was asking?

I dropped out of the thread a long time ago as I didn't have the time to write your app for you. I came back in because it seemed there was a simple solution I could fix quickly. It seems I was mistaken
If you rely on others you will never learn to become self reliant.

I recommend you spend time studying the Access tutorials by Steve Bishop on You Tube. It is a complete course from beginner to advanced and well worth the time

I'm going to drop back out of this thread again now as I have a number of my own clients to support and other projects needing attention.

Good luck with your project

 

[tihmir]

isladogs,
I tried to understand what you were asking me and give you an answer, but I might have missed something.

If you rely on others you will never learn to become self reliant

I will certainly continue to rely on other people (as well as you), (especially here in this forum) because I know that they have a lot of knowledge, skills and extensive experience in Access and development.

I recommend you spend time studying the Access tutorials by Steve Bishop on You Tube. It is a complete course from beginner to advanced and well worth the time

I will listen to your advice and look at the tutorials by Steve Bishop on You Tube.

I'm going to drop back out of this thread again now as I have a number of my own clients to support and other projects needing attention.

Good luck with your own clients and other projects that need more attention!



Still, if anyone has an idea how this report could be compiled (which I wrote about a little earlier), please write. I will be grateful for your advice. This is the last step of my project to be completed. Thank you all.

 

[fertooos]

Hi. Looks like you could use a Crosstab query.