Query Calculation Help Required Please

[dazstarr]

Hi

I am struggling with something im sure is quite simple.

I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.

What I need to do is crosscheck the calls answered by employee in both tables. For example:

Table 1 has 3 records for John. (This means 3 calls answered).

Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).

What I need is a report with both these sets of data and a new field with shows the the difference between the two.

Can someone please help me.

Many Thanks

 

[neileg]

Mmm.... Sounds like a bad design to me. Why store the number of calls when you can simply calculate it?

To answer your question, make a totals query based on table 1 and set the function to count. Then create another query. Add the first query and table 2 joining them on the employee ID. You can then select the count from the query and the count field from table 2 and subtract one from the other in a calculated field. If this calculation produces anyhting other than zero, you have a mismatch.

 

[dazstarr]

Hi

The reason why it is setup so badly is because each table is an export from two different systems. It is a nightmare!

Unfortuntely there is no employee table with an ID field....is there another way I can do this please.

Thanks for your response.

 

[Brianwarnock]

Join on employee name and hope its unique.
The 1st query will group on employee name and have a calculated field Count(*).

Brian

 

[dazstarr]

Thanks!

Thanks guys for your help!

It worked!