Doesn't exist instead of Is Null??

[kvar]

I have a query that is using my 3 data containing tables. It is selecting the stats that i need from each table using the criteria that the date in the table is last month. they are related by name.
(To get last months average of each stat)

For whatever reason, not every person has a stat in every category (lazy supervisors I guess), but I need it to return what data it does have, the rest will just be null.

But, it doesn't work that way. I tried putting Is Null in the Or row for each of the statistcs, but that doesn't work.
I believe because in the table there is no "Name" "Date" and a zero in the Stat's field. If there was nothing there, then there was nothing to import. So basically, that persons name, with a date within the range, just doesn't exist.

So what else can I use instead of Is Null that would at least return the other values that do exist?

 

[pbaldy]

Hard to say without seeing the tables, but my guess would be that you need to edit the join between them. In SQL view, it would likely be changing INNER to LEFT. In design view, right click the join line to edit the join and choose the appropriate "Include all records from...".

 

[kvar]

Thank you for the tip.
I tried changing them to both left and right, just to test, and neither way returns any result.
Here is the SQL as it is now:
SELECT AgentListingtbl.Agent, AgentListingtbl.Supervisor, Avg(AgentCallDatatbl.ACW) AS AvgOfACW, Avg(AgentCallDatatbl.PercentHeld) AS AvgOfPercentHeld, Avg(AgentCallDatatbl.AvgHold) AS AvgOfAvgHold, AgentQADatatbl.QAScore, AgentStatDatatbl.ADHPercent
FROM ((AgentListingtbl LEFT JOIN AgentCallDatatbl ON AgentListingtbl.Agent = AgentCallDatatbl.AgentName) LEFT JOIN AgentQADatatbl ON AgentListingtbl.Agent = AgentQADatatbl.AgentName) LEFT JOIN AgentStatDatatbl ON AgentListingtbl.Agent = AgentStatDatatbl.AgentName
WHERE (((DatePart("m",Date())-1)=DatePart("m",[AgentCallDatatbl]![Date])) AND ((DatePart("m",Date())-1)=DatePart("m",[AgentQADatatbl]![Date])) AND ((DatePart("m",Date())-1)=DatePart("m",[AgentStatDatatbl]![Date])))
GROUP BY AgentListingtbl.Agent, AgentListingtbl.Supervisor, AgentQADatatbl.QAScore, AgentStatDatatbl.ADHPercent
HAVING (((AgentListingtbl.Agent)=Environ("username")));

 

[wilpeter]

I assume that the criteria limits the output to those Agents having a previous month's record in each table. Each failure to match reduces the output by that one Agent, regardless if it has records in two of the other tables. Right?

 

[wilpeter]

Just thinking out loud, but could you run three queries (not Total type) each to extract those records from a Datatable that are the previous month's records; then combine them with a union query (or build a table from them); and finally perform an Avg query from the result, tossing in the Supervisor field on a Left Join? That way, unmatched records are omitted without infecting other tables.

 

[wilpeter]

I can get this far...
Of course, it meant adding four blank fields to other tables to make them consistent and including those blank fields in the three queries.

 

[boblarson]

I can get this far...
Of course, it meant adding four blank fields to other tables to make them consistent and including those blank fields in the three queries.

Actually you didn't need to add those fields to those tables. You can add them in the Union Query. For example, if Table 1 has 3 fields, Table 2 has 1 field and Table 3 has 2 fields:

SELECT Field1, Field2, Field3 FROM TABLE1
UNION
SELECT Field1, Null As Field2, Null As Field3 FROM TABLE2
UNION
SELECT Field1, Field2, Null As Field3 FROM TABLE3

 

[wilpeter]

Super! I never use SQL unless I have to, so I missed that one. Still, I've left the poster with a half-answered problem.

 

[kvar]

I missed a lot in here over the course of the evening!
Thank you, thank you, for everyone's help.

The query criteria does not limit to only those that have data in any or all of the tables. I wasn't really sure on how to word that in the criteria, but that by itself may take care of the issue.....if anyone has an idea on what to use for the criteria?

I did try using 3 seperate queries, one for each table, then joining them in a final query. The problem is, I need the average for last week, month-to-date, last month, year-to-date, last year, and for all 3 quarters of the year. That would be a LOT of queries!
I can do it that way, if i have to, I just figured there must be a more efficient way.

I'm going to take a look at the example you posted now, and see if that helps. Will update you on where i stand in a bit!

Thank you again.

 

[wilpeter]

Kvar: Welcome back. We kept ourselves amused during your absence. I was curious why (since you have three fields of values in the AgentCallDatatbl) you don't include in that table the QAScore and the ADHPercent fields. I'm sure there's a good reason, but it would simplify your querying.

 

[kvar]

I did consider it, and tested it.
My problem is, I have 3 different reports that the data is coming from weekly (3 tables), and there are different dates for all of the data, so i end up with a REALLY long table, where there is a lot of 0's in every row (if the data came from the Call Data report, there would be 2 empty cells, etc).
It was my thinking that over time it would just get ridiculously long, especially since I'm supposed to have stats for over a year.

Maybe not. And it would make filling the fields on my form a LOT simpler if it were all in one table! I'm really stuck on how to do that.
If i moved everything to one table, then couldn't I just calculate the averages right in the fields on the form? no need for any of the queries?
something like a datasource of Avg[fieldname]!

Where [Date] is whatever??

(How's that for some SQL coding??....lol)