Count unique values per company

[TomSD]

Hello


I've a table "Check" and a table "Company". In the table Check, there are some general checks listed that need to be done when a new company is added (ie: did we get approval to add the company; did we use form X; did we do a background check). In the future, there might be some other checkes as well. A background check has to be done every year. Therefor there's an expiry field as well.

Now I'd like to generate a list of all companies for which not all checks have been done.


I was thinking to check this as follows: create a form with all companies and for each company the checks that aren't expired.

Result is like this:

--------------------------------------------
| CompanyID + Check + ExpiryDate |
--------------------------------------------
| Company 1 + Check 1 + 01/01/2017 |
| Company 1 + Check 2 + 01/01/2017 |
| Company 1 + Check 3 + 01/01/2017 |
| Company 1 + Check 4 + 01/01/2017 |
| Company 2 + Check 1 + 01/01/2017 |
| Company 2 + Check 2 + 01/01/2017 |
| Company 3 + Check 1 + 01/01/2017 |
| Company 3 + Check 2 + 01/01/2017 |
--------------------------------------------


Now, I thought I'd count the unique values in table "Checks" using dcount and compare that for each Company the number of unique checks in the above form.

I have two questions:

1. I believe this might not be the most elegant solution. Do you have a better idea?
2. How can I count the unique checks for each company?

 

[plog]

Generally if you want to find out something about your data, you run a query. If you want to format that data to a specific output, you create a report. A form is for user interface. You never mention anything about user interaction, so I don't think a Form should come into play.

What exactly do you want to do?

Generate a set of data?
Format an existing list of data?
Or allow users to interact (edit/add/delete) data?

 

[TomSD]

Hi thank you for your response!

I just found a solution to my problem by using a crosstab query.

For the sake of clarity, what I wanted to do is:

For each company, show whether a Check has been performed. No user interaction needed.

I managed to solve this as follows:

1. Create a query combining the companies and the checks performed for each company
2. Create a crosstab query based on this one. Row Heading = companyID; Column Heading = CheckDescription; Value = Last of ExpiryDate