473,324 Members | 2,179 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Count Unique Data in Separate Field?

yosiro
34
Tabel
---------------------------------------------
ID | YEAR | FIELD1 | FIELD2 | FIELD3
---------------------------------------------
1 | 2015 | A | B | D
2 | 2015 | B | C | D
3 | 2016 | C | C | D
4 | 2016 | C | C | A
5 | 2016 | C | A | B


How to make a query table like this:

QUERY WITH CRITERIA YEAR "2015"
-------------
FIELD | COUNT
-------------
A | 1
B | 2
C | 1
D | 2

QUERY WITH CRITERIA YEAR "2016"
--------------
FIELD | COUNT
A | 2
B | 1
C | 5
D | 1
Dec 7 '16 #1
2 768
jforbes
1,107 Expert 1GB
For this, I think that Database Normalization should be implemented. If you were to normalize the data a basic COUNT() aggregate Query would work:
Expand|Select|Wrap|Line Numbers
  1. SELECT [YEAR], [FIELD1], COUNT([FIELD1]) AS FieldCount
  2. FROM Tabel 
  3. GROUP BY [YEAR], [FIELD1]
Maybe someone else has a trick up their sleeve, but any other solution I can think of would be second rate.
Dec 7 '16 #2
yosiro
34
the query result is not what i want to show like the first post
Dec 7 '16 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: SD | last post by:
Hi, This is driving me nuts, I have a table that stores notes regarding an operation in an IMAGE data type field in MS SQL Server 2000. I can read and write no problem using Access using the...
2
by: blast | last post by:
hi, thanks for heping me in my previous issues....i m really confused reg this issue, please help me: i'm selecting a row from table a for that particular row there are 3 persons (assignee,...
0
by: hwiesmu | last post by:
I have defined a unique data type and some UDF's. After create table with "SET PATH=..." everything works fine. But some standard applications do not deal with "SET PATH=.." and will not work. Is...
1
newnewbie
by: newnewbie | last post by:
Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our...
0
denny1824
by: denny1824 | last post by:
I have a crystal report that someone else wrote and already has a Formula Field if Criteria then (Count ({FirstField},{SecondField}) / Count ({FirstField})) When the Criteria is true, this...
3
by: Scott2112 | last post by:
Hello, I have a simple datatable ("Co_Size")with non-unique data and I would like to populate a combobox with unique data (VB 2005). I haven't figured it out yet. Any help? Scott
1
newnewbie
by: newnewbie | last post by:
Hi, Could somebody please help me with VBA code to count unique values in a Report? Report is based on a query that has grouped values. Basically, I would like to use formula...
2
by: keirnus | last post by:
Hello, I actually don't know where to post for SQL Queries used in MS Access. If this is not the correct thread, please redirect this to the correct one. Anyway, I am kinda newbie to the query...
4
Fary4u
by: Fary4u | last post by:
Hi how to count the data ( not count the record )from the table & with the following id ? i'm using asp classic with ado MS Access database with simple query "select * from order where...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.