Count occurrences of unique values cross fields

[bms]

Hi,
I have a table like so, and I am looking for the number of unique values in fields in access
I used the following in excel but not sure how to accomplish the same in access query

SUM(IF(FREQUENCY(Table1[@[D1]:[D5]],Table1[@[D1]:[D5]])>0,1))

Thanks much

D1	D2	D3	D4	D5	Unique
1	2	3	4	5	5
1	1	1	2	3	3
2	2	2	2	2	1
2	2	2	3	3	2
4	4	4	5	6	3

[June7]

This is not a normalized data structure. Would be fairly easy with normalized structure.

Options:

1. custom VBA function.

2. normalize the data structure so queries can accomplish the count - a UNION query can manipulate the data into a normalized structure then use that query as the source for subsequent queries

[bms]

do you mean normalize like in 1/0 ? could you kindly give me an example
Thanks again

[June7]

What does 1/0 mean?

Normalized data structure:

Category	Data
D1	1
D2	2
D3	3
D4	4
D5	5
D1	1
D2	1
D3	1
D4	2
D5	3

A UNION query could generate that structure. There is no wizard for UNION, must type or copy/paste in SQL View of query builder. Limit of 50 SELECT lines.

SELECT "D1" AS Category, D1 As Data FROM tablename
UNION SELECT "D2", D2 FROM tablename
UNION SELECT "D3", D3 FROM tablename
UNION SELECT "D4", D4 FROM tablename
UNION SELECT "D5", D5 FROM tablename;

[bms]

got it, thank you so so much for the quick respond !

1/0 as in a yes/no field

[June7]

Access uses -1/0 for Yes/No.