Find unmatched records by name

[Nicholas Scarpinato]

I have two tables, one with a list of reason codes, and one with the data I
run my reports on. My data table has five fields: Description, Total Time,
Average Time, Occurances, and Agent Name. My reason code list table only has
one field, Reasons. What I want to do is add a line to my data table with
zeros for Total Time, Average Time, and Occurances for each Reason Code the
agent is missing data for. For example:

Agent: Total: Average: Occurances: Description:
Mike 1:00:00 0:20:00 5 Break
Mike 0:30:00 0:10:00 3 Personal

Reasons:
Break
Not Ready
Personal
Call Wrap Up
Status ETS
Meeting
Training


For every Reason that the user does not have data for, I want to add zero
data records for that reason to the main data table, like this:

Agent: Total: Average: Occurances: Description:
Mike 0:00:00 0:00:00 0 Status ETS
....
....
etc.

I've tried an unmatched query, but all I come up with are two records for
reason codes that no user in the table has any data for. I really don't know
how to explain this problem any better... hopefully someone can understand
what I'm trying to get at and help me figure out what I'm missing.

 

[KARL DEWEY]

Use two queries unless you know subqueries --
Agent_Reason ---
SELECT DataTable.[Agent Name], Reasons.Reason
FROM DataTable, Reasons
GROUP BY DataTable.[Agent Name], Reasons.Reason;

INSERT INTO DataTable ( [Agent Name], Description )
SELECT Agent_Reason.[Agent Name], Agent_Reason.Reason
FROM DataTable RIGHT JOIN Agent_Reason ON (DataTable.Description =
Agent_Reason.Reason) AND (DataTable.[Agent Name] = Agent_Reason.[Agent Name])
WHERE (((DataTable.Description) Is Null));

 

[Lord Kelvan]

mm question is to you actually want blank records of data in your
database or do you want it to display in a query as 0's for thoes
records

you also shouldnt store calculated data in a table this is noted as
avaragetime you can just calculate this in the query

two queries again

SELECT DISTINCT agents.agent, reasons.reasons
FROM agents, reasons;

save this as agenttoreasonlink

SELECT qryagenttoreasonlink.agent, qryagenttoreasonlink.reasons,
IIf([agents].[total] Is Null,"00:00:00",[agents].[total]) AS Expr1,
IIf([agents].[total] Is Null,"00:00:00",Format([agents].[total]/
[agents].[occurances],"Long Time")) AS average, IIf([agents].
[occurances] Is Null,"0",[agents].[occurances]) AS Expr2
FROM qryagenttoreasonlink LEFT JOIN agents ON
(qryagenttoreasonlink.reasons = agents.description) AND
(qryagenttoreasonlink.agent = agents.agent)
ORDER BY qryagenttoreasonlink.agent, qryagenttoreasonlink.reasons;

this is the one you run basically it links the records in the outter
join from agent and reason then fills it any nulls with 0's

this allows you to avoing adding blank records into the database

as a note this one also calculates the average time as this is a
calculated field and because the value in the database is dependant on
total and occurances it will cause problems if thoes fields are
changed

as another note the query will adapt if thoes values change

hope this helps

Regards
Kelvan

 

[Nicholas Scarpinato]

The Average Time is actually calculated by the system I'm exporting the data
from. What I'm building is a database that takes multiple sets of data from
three different reporting systems and combines them in one report. I could
technically calculate the average time, but for right now I'm just trying to
get the report to work properly before I fine tune it for daily use.

Also, I want the zeros in the table basically as "placeholders" so that
every agent has data for every day, even if it's zero data. Sometimes I'll
have agents who were not working on a certain day, but I need to include them
on the report. Since they weren't phsyically clocked in, they won't show up

 

[Lord Kelvan]

well then look at karls method it actually inserts records into the
database where mine only makes it appear so

Regards
Kelvan

 

[Nicholas Scarpinato]

Thank you Karl, I knew I had the right idea but I was trying to do the
unmatched query without the Agent Names... can't believe that didn't cross my
mind yesterday.

Use two queries unless you know subqueries --
Agent_Reason ---
SELECT DataTable.[Agent Name], Reasons.Reason
FROM DataTable, Reasons
GROUP BY DataTable.[Agent Name], Reasons.Reason;

INSERT INTO DataTable ( [Agent Name], Description )
SELECT Agent_Reason.[Agent Name], Agent_Reason.Reason
FROM DataTable RIGHT JOIN Agent_Reason ON (DataTable.Description =
Agent_Reason.Reason) AND (DataTable.[Agent Name] = Agent_Reason.[Agent Name])
WHERE (((DataTable.Description) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have two tables, one with a list of reason codes, and one with the data I
run my reports on. My data table has five fields: Description, Total Time,
Average Time, Occurances, and Agent Name. My reason code list table only has
one field, Reasons. What I want to do is add a line to my data table with
zeros for Total Time, Average Time, and Occurances for each Reason Code the
agent is missing data for. For example:

Agent: Total: Average: Occurances: Description:
Mike 1:00:00 0:20:00 5 Break
Mike 0:30:00 0:10:00 3 Personal

Reasons:
Break
Not Ready
Personal
Call Wrap Up
Status ETS
Meeting
Training


For every Reason that the user does not have data for, I want to add zero
data records for that reason to the main data table, like this:

Agent: Total: Average: Occurances: Description:
Mike 0:00:00 0:00:00 0 Status ETS
...
...
etc.

I've tried an unmatched query, but all I come up with are two records for
reason codes that no user in the table has any data for. I really don't know
how to explain this problem any better... hopefully someone can understand
what I'm trying to get at and help me figure out what I'm missing.