Need to count number of distinct values

[fransanchezoria]

Hi!

I have a problem that I am sure it's easy to fix but I am too much of a newbie... In my data, I have records with Family IDs, inside these families there are usually one to 10 records but I am trying to analyze my data based on Family IDs.

I created a query and selected a few fields, including Family ID. I want to count the number of different Family IDs, but instead, when I use the function Count I get the total number of records. A quick example

ID Family ID Field FirstOfAssignee Orgnamn Kategori
441 20397136 Sven Torbjoern Lagerwall Bionanosystem Lärarpersonal
442 20397136 Bionanosystem Lärarpersonal
471 20286547 Chalmers Intellectual Property Rights Ab Bionanosystem Lärarpersonal
472 20286547 Forskarpatent I Vaest Ab Bionanosystem Lärarpersonal
473 20286547 Chalmers Technology Licensing Ab Bionanosystem Lärarpersonal
474 20286547 Bionanosystem Lärarpersonal

Say that I group by Orgnamn and count Family Ids, ideally I would like the result of 2, because there are only two different Family IDs in the data. However, when I Count Family IDs I get 6 as a result, because it counts the records inside.

My question is, how to disregard the records inside the Family IDs and count only the latter?

Thanks in advance!

 

[MStef]

It is easy. Make a tblFamily, with a field FamilyID.
Make this field as a PRIMARY KEY.
Make an APPEND QUERY on your table and try to
write FamilyID in the tblFamily.
It must be a 2 records, (20397136 and 20286547).
Look at "DemoFamilyIDA2000.mdb" (attachment, zip).
Look at Table1, tblFamilyID, Query1. Run query1.
After Query1 there are 2 records in tblFamilyID.

 

[fransanchezoria]

Thanks MStef,

I ended up following your advice. I knew I could create another table but I was hoping I didn't have to do this because I like to keep the data uncluttered.

Thanks for your advice the template worked like a charm!