Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-31-2019, 12:45 PM   #16
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,345
Thanks: 115
Thanked 3,107 Times in 2,825 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to sum and calculate from tables?

Is it a small issue we can help you with or a big issue?
If the latter try creating your own form with combo boxes and a subform with the crosstab data

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-31-2019, 10:13 PM   #17
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

Quote:
Originally Posted by isladogs View Post
Is it a small issue we can help you with or a big issue?
If the latter try creating your own form with combo boxes and a subform with the crosstab data
I apologize, but I cannot understand the meaning of the small or big issuе.
I try to explain to myself how it is possible to look for something in the Crosstab that is missing. I mean that in the Crosstab there are none fields - "Workers" and "Date". When I add these fields to the "qryCountInspectionTypes" it working properly. But when I started crosstab "qryCountInspectionTypes" it does not show all records.
I apologize if I have not expressed myself correctly in my post #15
tihmir is online now   Reply With Quote
Old 11-01-2019, 12:32 AM   #18
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,345
Thanks: 115
Thanked 3,107 Times in 2,825 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to sum and calculate from tables?

Quote:
Originally Posted by tihmir View Post
I apologize, but I cannot understand the meaning of the small or big issuе.
I try to explain to myself how it is possible to look for something in the Crosstab that is missing. I mean that in the Crosstab there are none fields - "Workers" and "Date". When I add these fields to the "qryCountInspectionTypes" it working properly. But when I started crosstab "qryCountInspectionTypes" it does not show all records.
I apologize if I have not expressed myself correctly in my post #15
Hi
Sorry if I wasn't clear. I wondered if you were stuck on a specific issue or had no idea where to start.
As for the extra fields, its easy enough to add them to both queries. See attached

If that's what you want, it should be fairly straightforward to use that query as the record source in an adapted version of the emulated split form (ESF)
Attached Files
File Type: zip Database_EN_v3.zip (90.2 KB, 5 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
tihmir (11-01-2019)
Old 11-01-2019, 04:27 AM   #19
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

Hi
Quote:
Sorry if I wasn't clear. I wondered if you were stuck on a specific issue or had no idea where to start.
As for the extra fields, its easy enough to add them to both queries. See attached

If that's what you want, it should be fairly straightforward to use that query as the record source in an adapted version of the emulated split form (ESF)
Thanks again for the help you give me! I created emulated split form (ESF) with cbo and Date from/ To filters. The query works perfect. It show records sorted between dates and sorted by workes. But the records into the (ESF) are not
united for each Object. Here's what I mean:
Attached Images
File Type: png Pic2.png (53.1 KB, 21 views)
Attached Files
File Type: zip Database_EN_v4.zip (141.7 KB, 12 views)

Last edited by isladogs; 11-01-2019 at 05:13 AM. Reason: Added quote tags
tihmir is online now   Reply With Quote
Old 11-01-2019, 05:30 AM   #20
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,345
Thanks: 115
Thanked 3,107 Times in 2,825 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to sum and calculate from tables?

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

BTW which version of the ESF did you use as it went through many iterations and what you have doesn't look familiar.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-01-2019, 07:02 AM   #21
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

Quote:
Originally Posted by isladogs View Post
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
Quote:
BTW which version of the ESF did you use as it went through many iterations and what you have doesn't look familiar
tihmir is online now   Reply With Quote
Old 11-02-2019, 03:00 PM   #22
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,345
Thanks: 115
Thanked 3,107 Times in 2,825 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to sum and calculate from tables?

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.
Attached Images
File Type: png splitform.PNG (19.8 KB, 15 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-03-2019, 01:33 AM   #23
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

Quote:
Originally Posted by isladogs View Post
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.
Quote:
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.
tihmir is online now   Reply With Quote
Old 11-04-2019, 04:33 AM   #24
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,345
Thanks: 115
Thanked 3,107 Times in 2,825 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to sum and calculate from tables?

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...d.php?t=302189
This includes selections based on date ranges
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-04-2019, 09:29 AM   #25
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

Quote:
Possibly easier to just use a continuous subform?
Thank you for the advice. I'll try.
Quote:
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.
tihmir is online now   Reply With Quote
Old 11-06-2019, 05:07 PM   #26
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,469
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How to sum and calculate from tables?

@tihmir, still an issue to resolve?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
Old 11-06-2019, 09:40 PM   #27
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

Quote:
Originally Posted by June7 View Post
@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.
tihmir is online now   Reply With Quote
Old 11-06-2019, 10:36 PM   #28
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,469
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: How to sum and calculate from tables?

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
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
tihmir (11-06-2019)
Old 11-06-2019, 11:11 PM   #29
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

Quote:
Originally Posted by June7 View Post
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 is online now   Reply With Quote
Old 11-14-2019, 11:15 AM   #30
tihmir
Newly Registered User
 
Join Date: May 2018
Posts: 75
Thanks: 33
Thanked 0 Times in 0 Posts
tihmir is on a distinguished road
Re: How to sum and calculate from tables?

I built a report with the 1st method and now it works fine (Thanks again June7).
Quote:
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.
The SQL on the Crosstab is:
Quote:
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
Quote:
DoCmd.OutputTo acOutputReport
Is it possible to save the report to excel and how?

Thanks in advance.
Attached Images
File Type: png 1.png (28.3 KB, 6 views)
File Type: png 2.png (17.4 KB, 7 views)

tihmir is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to calculate data from two tables dmorgan20 Queries 2 04-04-2018 09:26 AM
Calculate a table field from other tables bposkie Tables 5 04-09-2016 12:24 PM
[SOLVED] How to create tbl that calculate between 2 other tables? calvinle Modules & VBA 4 09-03-2015 07:46 AM
calculate difference between 2 fields from 2 tables gdanalakshmi Queries 5 12-11-2002 04:17 PM




All times are GMT -8. The time now is 12:24 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World