Adding new information

[scubadiver]

My database holds employee records on a weekly basis. I have a query that
totals up the number of hours for each department for each week.

What I would like to do is somehow include the total number of enquiries to
be associated with the correct week and department. From this I can calculate
productivity.

Any ideas?

cheers in advance.

 

[scubadiver]

For example,

taken from the query:

for week 23/06/2006

Admin 1500 hrs
ALS 500 hrs
CS 1300 hrs
ERT 750 hrs

From the enquiry logging system database

ALS enquiries 400
admin 3500
CS 3500
ERT 3100

What is the best way to store this information. Since weekID has duplicates
I am thinking two separate tables with a one-to-one relationship would be
best.

Or is there something I am missing?

 

[scubadiver]

What I would really like to know is whether it is possible to store summary
information from a query in a table (as redundant as it may sound!)

 

[Rick Brandt]

What I would really like to know is whether it is possible to store
summary information from a query in a table (as redundant as it may
sound!)

Make the query an append query and append the data into your table.

 

[scubadiver]

thanks for the reply.

As the database grows, I will be appending the same data plus whatever else
comes along. How do I not append the same information repeatedly.

 

[Rick Brandt]

thanks for the reply.

As the database grows, I will be appending the same data plus
whatever else comes along. How do I not append the same information
repeatedly.

You would need a field or fields that you specify as unique in the target table
either by making them the Primary Key or by creating a unique index over them.
Then your query can either run as is and the duplicates will simply fail, or you
can modify the query such that it does not include rows with values that already
exist in the target table. This could be done by including the target table in
th query with an outer join or by using a Not In(SELECT...) criteria.

 

[John Vinson]

As the database grows, I will be appending the same data plus whatever else
comes along. How do I not append the same information repeatedly.

Why append it, rather than just recalculating it as needed?

John W. Vinson[MVP]