Access 2003 Expression Operators

[sjohno]

I am currently learning the Totals in Access 2003, using the Group By and it's drop down menu which has all the Min,Max,Sum Count etc Expressions.

I have a table of which I need to create a query, the table contains info with Mechanics names and jobs that they have done, often a mechanics name appears more than once in the table.

Which Expression do I use to query the number of mechanics in the table? When I use the Count the query contains all of the mechanics names including the the ones whose names appear more than once!

Any help would be gratefully appreciated I look forward to any replies thank you.

Sarah

 

[Zigzag]

In the design of you report, if you right click and select "sorting and grouping" another popup should appear.

If you select "mechanics" in here and select "group header - Yes" and "Group on - each value" you should get the report you are looking for.

You will need to keep the "count" in the query.

Let me know if this works for you.

Garry

Added:-

If this is a report you are designing.

 

[Zigzag]

If this is a From then you need "mechanics" twice in your query.

Once as a count and once as "Group By"

Sorry for the confusion.

Garry

 

[sjohno]

Hi Garry

Thanks for replying, sorry I am a little confused with your help. I need to create a QUERY from the table and am using the Totals button.

This brings up the TOTAL row in the query with the GROUP BY and a drop down box with all Min,Max,Sum Count etc Expressions.

When I use the Count in the CRITERIA row the query contains all of the mechanics names including the the ones whose names appear more than once.

thank you.

Sarah

 

[boblarson]

I'm going to throw something out there and say - If your mechanics names appear more than once in the table - you have not designed your database properly. It is not normalized. You should have a table for the mechanics and assign their ID number to the job, not their name. If you did that, a quick query to the mechanics table would give you the number.

 

[sjohno]

Hi

I understand and agree with you, the mechanics that appear more than once in the table have different jobs asigned to them

i.e. David has done 3 different jobs on 3 different cars

Each mechanic has a job no assigned to them, the Job No is in the Field Name in the Table and is also the Primary Key in the Table.

thank you.

Sarah

 

[boblarson]

Hi

I understand and agree with you, the mechanics that appear more than once in the table have different jobs asigned to them

i.e. David has done 3 different jobs on 3 different cars

Each mechanic has a job no assigned to them, the Job No is in the Field Name in the Table and is also the Primary Key in the Table.

thank you.

Sarah

So did I read the question wrong when you said you wanted a count of mechanics. Was it that you wanted a count of the jobs for each mechanic?

 

[sjohno]

Hi Bob

I need to create a query from a table using the TOTALS button.

This brings up the TOTAL row in the query with the GROUP BY and a drop down box with all Min,Max,Sum Count etc Expressions.

I have to find the number of mechanics in the Table and when I use the Count in the CRITERIA row, the query contains all of the mechanics names including the the ones whose names appear more than once.

I apologise for my explanation of my challenge!

thanks

Sarah

 

[Zigzag]

In your query do you have anything other than "mechanic - Group by" and "mechanic - Count"?

Garry

 

[sjohno]

Hi Garry

The query has the set info on the left hand side:
FIELD
TABLE
TOTAL
SORT
SHOW
CRITERIA
or:

In the FIELD I have in each titles in each column.
In the TABLE it is based on the REPAIRS TABLE.
In the TOTAL I have the GROUP BY with the drop down arrow which gives the following summary codes:

SUM,AVG,MIN,MAX,COUNT,STDEV,VAR,FIRST,LAST,EXPRESSION,WHERE

Hope this helps.

thanks

Sarah

 

[Zigzag]

You should try it with only two fields.

Field mechanic mechanic
table REPAIRS TABLE REPAIRS TABLE
Total Goup By Count
Sort
Show
Criteria

Garry

 

[sjohno]

Hi Garry

Thanks I'll give it a go, appreciate all your help.

regards

Sarah