Count Distinct Values in a Table

[ivandgreat]

Dears,

I have a table that consist of 20 fields from a query, i would like to show the unique values from all fields in a text inside the form. And also count that unique values in another text in the same form.

How will i do it?

br,
ivan

 

[plog]

I have a table that consist of 20 fields from a query,

Stop the presses. Queries are based on tables, what does your above statement mean exactly? Most importantly why do you want to do this?

That aside, this is going to take 40 queries, 20 (1 for each field) to get the unique values and another 20 to get the count of those distinct values. For every field you would make a query that looks like this:

SELECT Field1Name AS FieldValues FROM YourTableNameHere GROUP BY Field1Name;

Be sure to replace Field1Name with the name of your first field, and YourTableNameHere with the name of your table. Run the above query to get all the distinct values in Field1Name. I would call this query (and the 19 other similar ones) Field1Values.

Then to get the count of distinct values of each field, you use those 20 queries in a query structured like this:

SELECT  COUNT(FieldValues) FROM Field1Values;

Again, though, why?

 

[ivandgreat]

Actually, my data is like a highway, on some points they meet on a certain points but different paths and also will be on the same highway, that's why I wanted to know each unique points.

It is possible to have it as a vba and collects all unique values on each fields and have it in on one text box?

 

[plog]

Actually, my data is like a highway, on some points they meet on a certain points but different paths and also will be on the same highway

That's as scary a statement as when a chick says she's not religious, but she's very spiritual. Instead of running the other direction, I'll stay and ask you to explain that statement. Can you post the structure of your table along with some sample data?

As to wanting to get all the distinct values of those 20 fields into one combo box, you wouldn't use VBA, you would do as I suggest before, then bring all the data together using a UNION query (http://www.techonthenet.com/sql/union.php). But like I said, doing this is probably the wrong way to do it. I have a strong sense your table isn't properly structured.

 

[jdraw]

I'd be interested in seeing a jpg of your tables and relationships.

It might be interesting to readers if you gave us a 4-5 line overview of what your database is about, rather than "my data is like a highway".

 

[ivandgreat]

I have 3 tables in it, i only want to compute an output based on table "Input", the output is in the table "Output".

the logic is this,
- in table "Input", i have two fields "NE" and "FE".
- there is no duplicate ID @ field "NE"
- @ field "NE" first lookup the records in field "FE", and record it, after search it will lookup the adjacent "NE" again and loop until nothing found. example. In item 1, 2100 will found one item in field "FE" which is in item 4, the adjacent "NE" is ID 2002, this ID will search the value @ field "FE" so it has two with "NE" 2001 and 2200, these two IDs will do the same until found nothing. All found ID will be recorded on different table which is called table "Ouput".
- The table "Output" is only the result of the logic mentioned above.
- Other field @ table "Output" is part of the loop that were computed like field "To2G Capacity" and "To3G Capacity" is only adding all field values @ "NE 2G" and "NE 3G" (@ table "Input") related to the IDs recorded @ table "Output" field "DepID_Current".

@ CascadedIDs you will see my connectivity so in my form it will show the cascaded IDs with respect to the NE ID.

Hope you could help me on this.

br,
ivan

 

[ivandgreat]

anyone help me on this.. Thanks!