Count Functions

[Filmnoir]

Hi there,

I have a table which contains a list of fields that are skills against employees names.



Each skill has the value of B, I or E.

I need a query function to count each B, I & E in each skill set and then to create a table to store them to use in a report. In this created table I need to be able to query to produce percentages to then use in the report.

Any pointers would be appreciated?

[Filmnoir]

Still struggling on this one, any ideas?

[orange]

Please tell us the names of your tables and the fields each table contains.
Perhaps you could show us a jpg of your tables and relationships.

[Filmnoir]

Here is what I have so far...

[rpeare]

Your report appears to be doing exactly what you want, though you've only put in three field's worth of data. Why would you want to create a table when you're doing what you want with a report. Or are you not getting the results you want? (if you aren't I'm misunderstanding your question)

[Filmnoir]

The problem is that their are so many skills over 120 for each employee so the replicating is taking ages in the report. Also as they are not stored in a table (Just a report) I can't interrogate them i.e. pull out all skills with less than say 30% at expert level.

[rpeare]

In your report you are summing the data over all contract employees. If that's what you really want you can do this in a query with a little setup.

Let me say first and foremost that your data structure is inviting problems in the long run. You should not be storing the person's name in each of your skills table, you should be storing their unique identifier (from your consultant name table). Let's say that you misspell someone's name when you originally enter them and give them a few tests, their name will be stored incorrectly and when you change to the correct spelling all those child records will be lost because there is no longer anyone matching that name in any of the skills tables records you added.

Secondly all of your tables, *ALL* of them should have a unique identifier, some of your tables do not have one and any time you change a description or skill name or anything about a text value you are going to lose all your relationships. I think it's critical that you have a unique identifier in each table and when you need to reference information on a different table use use the foreign PK (for instance your skills lists tables)

Third you are using reserved words for field names (NAME) and using spaces in your object names (field names, table names, form names, etc). This is going to cause a lot of problems as you move forward I would suggest biting the bullet now and anywhere you have a space put in an underscore _ and stay away from special characters (#,&, etc)

Lastly, you're not using a normalized table structure which makes what you want to do a bit clumsy. I'm enclosing a copy of your database back to you if you look at the queries I built I had to normalize your data first through the union query then create a crosstab query for the data. It's a simple example of what you can do with your current structure.

[Filmnoir]

Thanks Rpeare, points taken I'll work on these now.

How exactly does this Union SQL work?

[rpeare]

damned if I know how they work, they just do!

I typically use them when I am tracking a specific person across different counties, each county having a different table but the same data fields. So in my union queries I pick the person I want an union all the fields so I can see their entire treatment history across counties.

In your case, because your data is not normalized, the union query helps put it into a normalized structure so you can then perform your queries more efficiently.

I know people do all kinds of crazy things with them to (which I haven't had occaison to experiment with) like adding subtotals to queries rather than doing it on a report