Query counting similar items from several fields (1 Viewer)

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
Hello,

I came across a task which I can't resolve.

As it is shown in a table attached I have three fields, namely Cutup 1, Cutup 2 and Cutup 3. These fields contain values (allocated consultants) related to the same table. I need now to calculate how may cutups in total each consultant has done. I can't find the solution. The only way I can do it is to do it separately for each Cutup 1, 2 3. But I would like to have the total figure.

Many thanks
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,585
Hi. If that is your table structure, then it's a little bit off. You have what we call a "repeating group," and it's against normalization rules. You won't have any problems counting the consultants if your table structure is more like this:
Code:
Date         Cutup   Number
06/01/2020   PS      1
06/01/2020   CEM     2
06/01/2020   EK      3
... and so on
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I am sorry the attached file was bad and I am attaching another one
 

Attachments

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,721
This is one of the pitfalls of un-normalized data, which this appears to be. Should you ever need Cutup 4, you have to modify your table, queries and reports that use this data. That is a sure sign of problems.

Aggregate functions (sum, count, etc) are far easier to do when the data is normalized. Without knowing more about how that data relates, it's not possible to make any suggestions in that regard. I also think you'd be better off storing nothing (null) in a field rather than --- .Wondering if you are going to say it's too late to fix the underlying problem....
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
Hi. If that is your table structure, then it's a little bit off. You have what we call a "repeating group," and it's against normalization rules. You won't have any problems counting the consultants if your table structure is more like this:
Code:
Date         Cutup   Number
06/01/2020   PS      1
06/01/2020   CEM     2
06/01/2020   EK      3
... and so on
Yes I know It is not particularly normal from access point of view. But I had to do this because it was easy for me to enter data most user friendly.
So what will happen now? Shall I forget this?
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
This is one of the pitfalls of un-normalized data, which this appears to be. Should you ever need Cutup 4, you have to modify your table, queries and reports that use this data. That is a sure sign of problems.
..
I know it is not a good practice but I had to do that because i needed a form fr entering data like in the attached picture. Otherwise I would not be able to have such a form
 

Attachments

Uncle Gizmo

Nifty Access Guy
Staff member
Joined
Jul 9, 2003
Messages
10,815
So what will happen now? Shall I forget this?
I am reasonably sure that both Adam - (VBA_PHP) and MajP offer users un-normalised methods for building databases. Indeed there's nothing wrong with the unnormalized approach, as long as you understand the major disadvantages it can lead to.

I also used to help people develop their database in an un-normalised fashion, but I don't anymore, because it just leads to more and more convoluted methods of extracting data. I encourage uses to learn the correct methods.

In particular Adam has expressed an interest in helping people develop their un-normalized databases, I'm not sure if he's available at the moment as I believe he's gone into a retreat... trolls and spammers using "whois" database You might want to send him a private message to see if he wants to volunteer to help.
 
Last edited:

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I am reasonably sure that both Adam - (VBA_PHP) and MajP offer users un-normalised methods for building databases. Indeed there's nothing wrong with the unnormalized approach, as long as you understand the major disadvantages it can lead to.
The reaso why I used unnormalised is I wanted to get a report looking like a spreadsheet, rather than a stepped or blocked report.
This is the only pitfall I came accross and this is not crucial for me. I have just two unnormaised big tables which accumulate into one spreadcheet in the final rota report. I'll have to leave with this.
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,721
I know it is not a good practice but I had to do that because i needed a form fr entering data like in the attached picture. Otherwise I would not be able to have such a form
I see what you mean. That form may still be possible as a crosstab query based on normalized data. Maybe not ideal but not as limited either.

You can join your separate queries that work in a Union query. You might also use a Totals query on your current table. On my phone now so am limited as to what I can do now.
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,291
MajP offer users un-normalised methods for building databases.
That is not true and not sure what you are talking about. I have never provided in my thousands and thousands of posts on Access any suggestions for building non-normalized databases. Out of curiosity do you have an example of this? I may have suggested to a user to normalize their data, but if unable provided a workaround.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,585
The reaso why I used unnormalised is I wanted to get a report looking like a spreadsheet, rather than a stepped or blocked report.
This is the only pitfall I came accross and this is not crucial for me. I have just two unnormaised big tables which accumulate into one spreadcheet in the final rota report. I'll have to leave with this.
Hi. Just my 2 cents, but no matter what the "desired output" might look like, you should try to avoid using non-normalized table structure as much as possible. Normalized table structure just plays well with a relational database. It avoids clunky/complex workarounds because the database engine is built to work well with normalized data.


Now, once you have a normalized structure, you can design your UI (data input forms) and reports into any shape you want, even non-normalized image. But, the internal structure of the data should be normalized, in my humble opinion.
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,565
I'm just here to be the 4th voice telling you to normalize your data and to watch the internet fight UncleG just provoked for some reason.

Step 1 of building a house or a database is laying the proper foundation. No point picking out accent colors and cabinet fixtures for the 2nd floor bathroom if the house can't support the bathtub when it gets half filled with water.

To get the input form you want is going to require some hacking. You can either hack the form together with VBA tricks or you can compromise your foundation with the hack you are currently using. When you go the route you choose everything else in your database relies on that hack. If you just hack your form, its just that form which is the hack.

Now carry on with the entertaining, petty arguments please.
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
I see what you mean. That form may still be possible as a crosstab query based on normalized data. Maybe not ideal but not as limited either.

You can join your separate queries that work in a Union query. You might also use a Totals query on your current table. On my phone now so am limited as to what I can do now.
I tried normalised first before I desired to use unnormalised. I did not get to edit or enter records in crosstabs. Also reports for cross tabs are possible only if you define the columns somewhere in query design. This is not ideal for me.
Yes ideally I should go normalised. But I have to squash a lot of data into minimum paper space in the final report and that also should be readable and in the form of spread sheet.
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
To get the input form you want is going to require some hacking. You can either hack the form together with VBA tricks or you can compromise your foundation with the hack you are currently using. When you go the route you choose everything else in your database relies on that hack. If you just hack your form, its just that form which is the hack.

Now carry on with the entertaining, petty arguments please.
Ok, thanks. I will now try to normalise. I’ll see what I can achieve. Unfortunately I am not so sophisticated in access. I d Ed Ben don’t know what is to hack form)
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,721
There is more than one way to 'skin a cat' (no idea why that's said).
A main form with 3 subforms based on normalized tables
Unbound forms based on normalized tables

You are correct that a cross tab form is not editable. You cannot have 3 subforms?
Not sure if 'hack' includes an unbound form, but that's one way to have normalized tables with that sort of layout. Easy to aggregate because it's normalized, layout is possible because of being unbound.
 

lbs

Registered User
Joined
Dec 22, 2019
Messages
67
There is more than one way to 'skin a cat' (no idea why that's said).
A main form with 3 subforms based on normalized tables
Unbound forms based on normalized tables

You are correct that a cross tab form is not editable. You cannot have 3 sub forms?.
Yes I can. Actually I have other activities such as MDT and absentees in normalised way and to have them in one line in report I used concatenate function. May be I will have to use concatenate here as well to get a string like
Cutup 1 Ls; Cutup 2 Tw; Cutup 3 Aw
 

Uncle Gizmo

Nifty Access Guy
Staff member
Joined
Jul 9, 2003
Messages
10,815
I may have suggested to a user to normalize their data, but if unable provided a workaround.
MajP... This is the thread where the where I got the idea you were up for helping people was denormalized Solutions:-

https://www.access-programmers.co.uk/forums/showpost.php?p=1661988&postcount=24

Maybe I misunderstood. Mind you in this thread you mention "offering a workaround". So I'm interpreting your offer of a workaround as supporting an un-normalized database... Clearly there is some room for misinterpretation.
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,291
Maybe I misunderstood. Mind you in this thread you mention "offering a workaround". So I'm interpreting your offer of a workaround as supporting an un-normalized database... Clearly there is some room for misinterpretation
Not sure how that could be misinterpreted. Only said it three times that the OP should use a normalized structure and with a little vba the UI could be accomplished, and that the solution provided demonstrates a normalized structure. How about looking at the provided solution which is clearly normalized?

Even though there may not be a native Access control to facilitate user input you are better off making a normalized DB and then trying to figure out a user interface.
But if you are willing to dip your toes into VBA we can help build the UI you want with a normalized data structure
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,291
There is more than one way to 'skin a cat' (no idea why that's said).
A main form with 3 subforms based on normalized tables
Unbound forms based on normalized tables
Here is another cat skinning technique I use often. You take normalized data and push it into a non-normal display table. In this example the table tblTimes_Rooms is a non normal table used only for display purpose, the real data is saved in a normalized table.
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom