Is there a way to count unique field values in Access Web Apps?

Question

Is it possible to query for group by counts of unique field values?

Let's say Sarah picks up crayons and records the crayons she picks up with the time she picked it up and a lookup to a list of colors.

Table.Colors (this is the list of colors)

1. Green
2. Red
3. Blue
4. Yellow
5. Orange

Table.Pickup (this is a log of crayons that Sarah picked up)

1. 5:33 PM - 1
2. 6:24 PM - 4
3. 6:35 PM - 3
4. 7:45 PM - 1
5. 7:55 PM - 3
6. 8:07 PM - 1

How can I query the database to see how many colors she picked up?

Desired result:

Unique colors collected: 3

---

Peter McDermott Specialist

Answer 1

I can't comment on its use in a Web database as I never have any occasion to use such, but the query would be like this: SELECT COUNT(*) AS DistinctPicks FROM (SELECT DISTINCT ColorID FROM Pickup);

---

Ken Sheridan, Stafford, England

Answer 2

Hi Peter,

In Access Web App, we need to create two queries to count the unique field values. One is used to return the unique value using GroupBy. The other is used to query the first query to count. Or we could create a data macro using For Each Record in the first query to get the count.

E.g.

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Celeste,

Thank you for sharing these tips. I think I understand what you've posted, however how could I get the two data points together to show the number of crayons picked up and how many of each color on the same data sheet view?

Also, if I wanted to attached each pickup task to people, how could I then show the data in a way that shows number of crayons picked up and number of colors picked up?

In Excel I can easily find this information with a PivotTable.

Here's a breakdown of what I would like to see. Is this possible?

Also important to note: I will need to then filter this data by date. Is that also possible with a query?

Thank you so much for your help with this!

---

Peter McDermott Specialist

Answer 4

Hello,

Could you get the expected result for your original question?

If it works, i suggest discuss one question in one thread. Please create a new thread for your further issue. Thanks for your understanding.

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

I don't have an answer to your question, but just wanted to pass along some information regarding a new announcement from MS regarding AWAs, in case you weren't already aware, see: https://www.devhut.net/2017/03/27/steer-clear-of-access-web-applications-awa/ this way you have as much time as possible to plan what course of action you will take to migrate things.

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 6

Hello Celeste,

Yes, I was able to get the count of the count. Thank you for that. My struggle now is trying to show the opportunity total count next to the opportunity unique count in the same query. :(

---

Peter McDermott Specialist

Answer 7

Daniel,

Very frustrating to read after I spent several weeks developing this--but grateful to know!

thank you,

Peter

---

Peter McDermott Specialist