Query counting similar items from several fields (1 Viewer)

lbs

Registered User.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
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

  • table_01.png
    table_01.png
    17.4 KB · Views: 91

theDBguy

I’m here to help
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
I am sorry the attached file was bad and I am attaching another one
 

Attachments

  • table_01.jpg
    table_01.jpg
    94.1 KB · Views: 89

Micron

AWF VIP
Local time
Today, 16:10
Joined
Oct 20, 2018
Messages
3,476
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.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
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.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
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

  • table_02.png
    table_02.png
    85.6 KB · Views: 95

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:10
Joined
Jul 9, 2003
Messages
16,245
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.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
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
Local time
Today, 16:10
Joined
Oct 20, 2018
Messages
3,476
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

You've got your good things, and you've got mine.
Local time
Today, 16:10
Joined
May 21, 2018
Messages
8,463
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
Staff member
Local time
Today, 13:10
Joined
Oct 29, 2018
Messages
21,358
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
Local time
Today, 15:10
Joined
May 11, 2011
Messages
11,611
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.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
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.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
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
Local time
Today, 16:10
Joined
Oct 20, 2018
Messages
3,476
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.
Local time
Today, 20:10
Joined
Dec 22, 2019
Messages
109
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
Local time
Today, 20:10
Joined
Jul 9, 2003
Messages
16,245
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

You've got your good things, and you've got mine.
Local time
Today, 16:10
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 16:10
Joined
May 21, 2018
Messages
8,463
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

  • RoomBooking Ver 4.0.accdb
    904 KB · Views: 88

Users who are viewing this thread

Top Bottom