For all who dare....................

On a data input form I have five fields called Worker1, Worker2, Worker3.............Worker5.  Worker1 will always have a name in that field.  Some times Worker1 and Worker2 fields will have a name.  On occasion Worker3 field will have a name.  And doing some reasearch I have found a name in the Worker4 field.  None in Worker5 as yet.  My coustomer has requested a report showing how many jobs each worker has done or been envolved in.  Sometimes a workers name will appear in Worker1 on a particular job and sometimes that worker will appear in the Worker2 field helping the individual who's name is in the Worker1 field.   On the report I only want to show one workers name and what that worker has done.  So if John Doe's name has appeared in any of the 5 worker fields, the report will show that, But the report will not have all five worker fields on just one field showing was he has done.  

Have I confused everybody............didn't mean to.

The road to victory is by way of defeat..............



____________________________
C.......................

Hi, I would suggest a UNION query.
Example:

SELECT RowID, Worker1 as WorkerField from mytable union SELECT RowID, Worker2 as WorkerField from mytable union SELECT RowID, Worker3 as WorkerField from mytable union SELECT RowID, Worker4 as WorkerField from mytable union SELECT RowID, Worker5 as WorkerField from mytable

This will give a list of all worker entries in a query and will not show duplicates.

Example:
RowID   Worker1   Worker2
1           Fred         Jane
2                           Fred

Results of the UNION query:

RowID  WorkerField
1          Fred
1          Jane
2          Fred

Hope this helps,
Kieron



____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Hello. I made a try in MSAccess97. I built a table with a JobName field, and Work1, Work2, Work3, Work4 and Work5 fields holding the names of workers.
The following query returns the count of the jobs where either the field Work1 or Work2 or Work3 or Work4 or Work5 hold the value "roger":

SELECT Count(JobsAlpha.JobName) AS CountOfJobName FROM JobsAlpha HAVING (((JobsAlpha.Work1)="roger")) OR (((JobsAlpha.Work2)="roger")) OR (((JobsAlpha.Work3)="roger")) OR (((JobsAlpha.Work4)="roger")) OR (((JobsAlpha.Work5)="roger"));

... and the following one returns each job in which "roger" is involved:

SELECT JobsAlpha.JobName AS CountOfJobName, JobsAlpha.Work1, JobsAlpha.Work2, JobsAlpha.Work3, JobsAlpha.Work4, JobsAlpha.Work5 FROM JobsAlpha GROUP BY JobsAlpha.JobName, JobsAlpha.Work1, JobsAlpha.Work2, JobsAlpha.Work3, JobsAlpha.Work4, JobsAlpha.Work5 HAVING (((JobsAlpha.Work1)="roger")) OR (((JobsAlpha.Work2)="roger")) OR (((JobsAlpha.Work3)="roger")) OR (((JobsAlpha.Work4)="roger")) OR (((JobsAlpha.Work5)="roger"));

These SQL instructions are pasted from MSAccess' SQL view

This is for the concept, you can customize it, of course.
If you want the db, I can post it here.
Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

For the two who answered...............much thanks..!  I tried a union query and kept getting the error message of not having the (   ) in the right place.  And in looking at both examples I can see why.  I will try both examples and let ya'll know.

Simplicity is the road to recovery..............!

____________________________
C.......................

I am using access 2003.  I took you basic query from previous post and it has given fits.  In your first query you use Count after select.  In your second example it kept given me an aggarate functin error until I put the workd Count after the the workd selection as in your first example.  That being said now its given me another error saying it doesn't have an specified function for [Worker 4].  Also I needed to put another set of  (  ).



____________________________
C.......................

Hey...ya'll its me.  Here is a copy of the modified SQL statement;

SELECT [WORK ORDER REQUEST].WORKER AS CountOfWorker, [WORK ORDER REQUEST].[WORKER 2],[WORK ORDER RQUEST].[WORKER 3], [WORK ORDER REQUEST].[WORKER 4], [WORK ORDER REQUEST].[WORKER 5]
FROM [WORK ORDER REQUEST]
GROUP BY [WORK ORDER REQUEST].[WORKER 1], [WORK ORDER REQUEST].[WORKER 2], [WORKER ORDER REQUEST].[WORKER 3], [WORKER ORDER REQUEST].[WORKER 4], [WORK ORDER REQUEST].[WORKER 5]
HAVING ((([WORK ORDER REQUEST].[WORKER 1]) = "POSEY")) OR ((([WORK ORDER REQUEST].[WORKER 2]) = "POSEY")) OR ((([WORK ORDER REQUEST].[WORKER 3]) = "POSEY")) OR ((([WORK ORDER REQUEST].[WORKER 4]) = "POSEY")) OR ((([WORK ORDER REQUEST].[WORKER 5]) = "POSEY"));

It gives me the error can't find worker 4.

What Gives ??

____________________________
C.......................

Well, it seems a syntax error, but the only one I found is on
"SELECT [WORK ORDER REQUEST].WORKER ..... [WORK ORDER RQUEST].[WORKER 3], ....." (you surely meant "REQUEST").
A stupid check to do: you have a "WORKER 4" field, don't you? And its name is exactly "WORKER 4", with upper case, and the space before the "4" char, isn't it? Mind that most often syntax errors are in object names rather than in SQL keywords.


But let me talk a while about writing code.
There are some small advices good programmers often take, in order to speed up debug operations, make their code easier to read, and reduce the risk of syntax errors during typing their code.

A good technique goes by the name of Hungarian Notation (did you ever heard about it?), and works this way: when you name an object, any object, you use no spaces among words and capital chars for initials.
Let make an example: you can write object names this way, OrYouCanWriteObjectNamesThisWayInstead. The second form is in Hungarian Notation.
Hungarian Notation provides the great advantage of cutting off the spaces. In fact, when you insert spaces in an object name this can bring to further errors, not only in VB. For instance, in SQL the syntax changes when an object has spaces in its name: you have to enclose it into square brackets (of course, you already know it).

But there are some other tricks. Another one, in pure SQL, is to spare upper case chars only to SQL keywords. Of course Windows is not case sensitive, and the code will anyway work, but this trick make easier to locate syntax errors.
Example:
SELECT COUNT(*) FROM MYTABLE WHERE IDFIELD = 1 AND NAMEFIELD LIKE 'M*';
SELECT COUNT(*) FROM MyTable WHERE IDField = 1 AND NameField LIKE 'M*';
please notice in the second form it's easier to locate object names, and so check if they are correct. You can figure out how great the advantage is when working with long SQL statements, with several fields and tables involved. But to correct it, tables' structure has to be modified, so this method has to be adopted when designing the tables.

These above are not rules, your code will work anyway without following these suggestion. A VB master calls them "good practices", or "good habits". If you take the habit to write your code and design your dbs this way, it'll be far easier to locate errors. For sure it'd be easier to find what's wrong in your SQL instruction without all those spaces, brackets and capitals. I suggest you to keep it in mind with the next db you'll design.

Keep in touch.

PS: the two SQL instructions I provided were two. I never was intended to merge them, but to execute them separately. If you run a Select Count with all the fields back, you get several rows with 1 in the count field. I gave a name to each job, and thought it was useful to retrieve each job's name where the given worker is involved. In order to just get the count of worker's jobs, I didn' put the names fields back, as in fact I don't need these fields returned. Hope I'm clear.

____________________________
Real Programmer can count up to 1024 on his fingers

yronium,

Thanks for the return.  I did find the mistake and the query did work.  You are right about the Hungarian Notation.  I also refer to it as Database Normalization.  I am trying to get this product to the coustomer and the next version will have all the changes with reference to Hungarian Notation and Database Nomalization.  I have to do some table restructering also.  That being said.  Your were right on and thanks for the help.  However, there is one more thing.   When I run the query it gives me the worker name I am looking for, but if the worker I am looking for shows up in the Worker 2, 3, 4 or 5 field, the query also gives me the other worker names.  How do I work the query to just give me the worker name I am look for and not anyone else.

CHANGE.................lead follow or get out of the way !!!!!!!!

____________________________
C.......................

Hi,
I've created a sample DB to show how to get all work order records where FRED was involved, and how to return only specific information about these records.
Hope it helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

____________________________
Attached:
workorder.zip 13 KB (Downloads: 4)