Refreshing Make Table Queries and Append Queries

J

Jimmy G

Is there a way to automatically refresh a make table query and several append
queries? Currently, I have a table that indicates one or more agents
associated with specific contacts. I want to count the number of contacts
every agent is associated with. I created a make table with the number of
contacts for the first agent, and then appended a table with the number of
contacts for each additional agent. The problem is that the number of
contacts change on a daily basis and I need the created tables to be
refreshed, ideally automatically. I've tried to create a macro using a
TrransferDatabase, but I'm having difficulty getting the database since it's
pointing back to itself. Any suggestions?
 
M

Micah Chaney

Why are you utilizing Make Tables and Append Tables as opposed to a regular
Select Query? I may need you to be a little more specific but try this:

Create a regular Select Query based of the original Table. Bring down your
Agents and Contact fields to the Detail section. From the File Menu | View |
Totals.
In the Totals Box for Agents, leave it at Group By, for Contacts, select
Count. Run your Query, you should have the data you want. And it will
update as the table it's based on updates.

If that doesn't help, let me know.
 
J

Jimmy G

Micah,

If I understand your approach correctly, I still don't have a way of
counting the contacts a person in each month. For example, if Katie is AGENT1
for 10 contacts, AGENT2 in 4 contacts, and AGENT3 for 5 contacts, and AGENT4
for 11 contacts, your way, I need a sum of 20 contacts for Katie. Because
there may be different agents that Katie is working with for each contact,
using COUNT in Total for GROUP BY doesn't allow me to sum these. My thinking
is that I need to make create a generic AGENT field for Katie and her
coworkers and count the number of times she works as AGENT1 (the make table
query). I then append to the generic AGENT field the number of times she
works as AGENT2 (the append query), etc. Also know that I also gathering
statistics for the type of contact Katie has (e.g., phone, letter, visit) as
well as the month in which she make the contact.

Does this make sense?
 
M

Micah Chaney

Let me see if I understand: You have...
Katie
Agent 1 -- 10 contacts
Agent 2 -- 4 contacts
Agent 3 -- 5 contacts
Agent 4 -- 11 contacts

Is that the result you want? If so then what I suggested before works, just
Group by the field that contains Katie also. so you'll see this in your
Query results window

[Person] [Agent#] [Contacts]
Katie Agent 1 10
Katie Agent 2 4
Katie Agent 3 5
Katie Agent 4 11
Michael Agent 1 3
Michael Agent 2 17 etc...

Does that make sense? It looks like that's what you're looking for. The
Query is going to group all records where the [Person] and the [Agent #] are
the same and then count the # of contacts. These are the only 3 fields
you'll need. Let me know if that helps. You're right the original
suggestion would yield the total number of contacts for all Agent 1's, Agent
2's etc...without reference to [Person]. Place the Group By on both those
fields and you should get the result you're looking for. Let me know if this
works.
 
Top