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

T

tihmir

Registered User.
Local time
Today, 05:57
Joined
May 1, 2018
Messages
257
Hi, I need some help, please! I have table “tbl_CodeObjets” with fields “ObjectType” and “CodeObjectID”.
I have another table “tbl_Objects” with fields “NameObject” and “CodeObjectID”.
And I have “tbl_Inspections” with fields “TypeOfCheck” and “ObjectID.
So, I want to sum and calculate this: For each ObjectType how many different checks there are. Here is the Database and the picture:
 

Attachments

  • Picture1.png
    Picture1.png
    8.2 KB · Views: 109
  • DataBase.zip
    263.7 KB · Views: 82
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:57
Joined
Oct 29, 2018
Messages
21,455
Hi. Looks like you could use a Crosstab query.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
You can do this with 2 queries:
1. qryCountInspectionTypes
Code: 
SELECT tbl_CodeOjects.ObjectType, tbl_Inspections.TypeOfCheck, Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes
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.ObjectType, tbl_Inspections.TypeOfCheck;

I used LEFT JOINS as otherwise you'll get no records for RZI as there is no data.

2. qryCountInspectionTypes_Crosstab
Code: 
TRANSFORM First(qryCountInspectionTypes.CountCheckTypes) AS FirstOfCountCheckTypes
SELECT qryCountInspectionTypes.ObjectType
FROM qryCountInspectionTypes
GROUP BY qryCountInspectionTypes.ObjectType
PIVOT qryCountInspectionTypes.TypeOfCheck;

As you have no data currently for RZI, you may need to tweak this slightly
e.g. change the first query to use two inner joins
 

Attachments

  • Database_EN.zip
    87.3 KB · Views: 80
Last edited:
T

tihmir

Registered User.
Local time
Today, 05:57
Joined
May 1, 2018
Messages
257
you'll get no records for RZI as there is no data.
Click to expand...
I'll don't want to include RZI in it, because I will include it in a report.
Тhat's exactly what i need.Тhank you very much, isladogs!!!

Тhank you for your advice, theDBguy!
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:57
Joined
Oct 29, 2018
Messages
21,455
tihmir said:
I'll don't want to include RZI in it, because I will include it in a report.
Тhat's exactly what i need.Тhank you very much, isladogs!!!

Тhank you for your advice, theDBguy!
Click to expand...
Hi. You're welcome. I see Colin did end up using a Crosstab query. Good luck with your project.
 
T

tihmir

Registered User.
Local time
Today, 05:57
Joined
May 1, 2018
Messages
257
I have one more question - In my "qryCountInspectionTypes_Crosstab" I do not want them to be arranged in alphabetical order, but to preserve the order as they are in the table. How to do it?
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
To do that specify the column headers in the desired order in the crosstab query property sheet.
 
T

tihmir

Registered User.
Local time
Today, 05:57
Joined
May 1, 2018
Messages
257
isladogs said:
To do that specify the column headers in the desired order in the crosstab query property sheet.
Click to expand...
I couldn't find where to change the order on the objects type of the column "ObjectType" to be same as tbl_CodeObject :banghead:

But I want to do somethig more - to create this crosstab query on a splitform with comboboxes and dates and use them for filter data by cbo amd date form - to. How it possible to do that?
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
tihmir said:
I couldn't find where to change the order on the objects type of the column "ObjectType" to be same as tbl_CodeObject :banghead:

But I want to do somethig more - to create this crosstab query on a splitform with comboboxes and dates and use them for filter data by cbo amd date form - to. How it possible to do that?
Click to expand...

The attached screenshot should explain the column headers

You may find that a little tricky with a standard split form as they are difficult to customise. I suggest you look at the emulated split form which has similar functionality but is much easier to customise. The example includes several combos for filtering the data
https://www.access-programmers.co.uk/forums/showthread.php?t=294421
Use the latest version in post #8.

If that's not suitable, just create your own with a form and subform.
Hope that helps
 

Attachments

  • Capture.PNG
    Capture.PNG
    62.8 KB · Views: 79
Last edited:
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
Sorry I misread the request regarding the sort order.
Ignore my previous comments about column headers

Instead add the CodeObjectID field to both queries.

1. qryCountInspectionTypes
Code: 
SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.TypeOfCheck, Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes
FROM (tbl_CodeOjects INNER JOIN tbl_Objects ON tbl_CodeOjects.CodeObjectID = tbl_Objects.CodeObjectID) INNER JOIN tbl_Inspections ON tbl_Objects.ObjectID = tbl_Inspections.ObjectID
GROUP BY tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.TypeOfCheck;

This time I've used inner joins which will exclude RZI if no data exists
The crosstab query is now
Code: 
TRANSFORM First(qryCountInspectionTypes.CountCheckTypes) AS FirstOfCountCheckTypes
SELECT qryCountInspectionTypes.CodeObjectID, qryCountInspectionTypes.ObjectType
FROM qryCountInspectionTypes
GROUP BY qryCountInspectionTypes.CodeObjectID, qryCountInspectionTypes.ObjectType
PIVOT qryCountInspectionTypes.TypeOfCheck;

If you do want to change the column header order, you would specify 'By registration', 'Current Inspection', 'Targeted Inspection' instead
 
Last edited:
T

tihmir

Registered User.
Local time
Today, 05:57
Joined
May 1, 2018
Messages
257
isladogs said:
Sorry I misread the request regarding the sort order.
Ignore my previous comments about column headers

Instead add the CodeObjectID field to both queries.

1. qryCountInspectionTypes
Code: 
SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.TypeOfCheck, Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes
FROM (tbl_CodeOjects INNER JOIN tbl_Objects ON tbl_CodeOjects.CodeObjectID = tbl_Objects.CodeObjectID) INNER JOIN tbl_Inspections ON tbl_Objects.ObjectID = tbl_Inspections.ObjectID
GROUP BY tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.TypeOfCheck;
This time I've used inner joins which will exclude RZI if no data exists
The crosstab query is now
Code: 
TRANSFORM First(qryCountInspectionTypes.CountCheckTypes) AS FirstOfCountCheckTypes
SELECT qryCountInspectionTypes.CodeObjectID, qryCountInspectionTypes.ObjectType
FROM qryCountInspectionTypes
GROUP BY qryCountInspectionTypes.CodeObjectID, qryCountInspectionTypes.ObjectType
PIVOT qryCountInspectionTypes.TypeOfCheck;
If you do want to change the column header order, you would specify 'By registration', 'Current Inspection', 'Targeted Inspection' instead
Click to expand...
Everything works perfectly. Thank you, isladogs
Now the big question is how to filter the crosstab query in SplitForm with cbo_worker and txt_dateForm and txt_DateTo. How to do it?
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
Excellent news.
For the split form, follow my other suggestion from post #9

You may find that a little tricky with a standard split form as they are difficult to customise. I suggest you look at the emulated split form which has similar functionality but is much easier to customise. The example includes several combos for filtering the data
https://www.access-programmers.co.uk/forums/showthread.php?t=294421
Use the latest version in post #8.

If that's not suitable, just create your own with a form and subform.
Click to expand...
 
Last edited:
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
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
 
T

tihmir

Registered User.
Local time
Today, 05:57
Joined
May 1, 2018
Messages
257
isladogs said:
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
Click to expand...
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
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
tihmir said:
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
Click to expand...

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)
 

Attachments

  • Database_EN_v3.zip
    90.2 KB · Views: 80
T

tihmir

Registered User.
Local time
Today, 05:57
Joined
May 1, 2018
Messages
257
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)
Click to expand...
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:
 

Attachments

  • Pic2.png
    Pic2.png
    53.1 KB · Views: 92
  • Database_EN_v4.zip
    141.7 KB · Views: 90
Last edited by a moderator:
isladogs

isladogs

MVP / VIP
Local time
Today, 13:57
Joined
Jan 14, 2017
Messages
18,211
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.
 
You must log in or register to reply here.

Similar threads

T
Solved How to count distinct id?
Replies
5
Views
747
tihmir
T
M
Solved Calculated field from marks
2 3
Replies
59
Views
1,829
Martynwheeler
M
P
Rolling 12 Months Total
Replies
6
Views
272
arnelgp
arnelgp
B
Sum (iff) with multiple strings issue
Replies
6
Views
320
BethHawkins
B
Etxezarreta
Sum function giving me an error
Replies
12
Views
346
Etxezarreta
Etxezarreta

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom