No Records (w/ Blanks) After Joining Tables

[mistahpat]

I am new to Access and have been given a newly created DB to handle.

There is 1 main table (..DataExtract) which consists of a data dump from a server and one of the columns is for queue names. However, not every record is populated with a queue name - many are blank. But those records are still valid.

I have 1 personally created table (QueueInfo) that I wish to join to the queue column from the DataExtract table, in my query. However when I do so, NONE of the records with blank queue names appear after running the query. (They do however when I do not join my QueueInfo table to the DataExtract table.)

I need to be able to pull information on all records, regardless of queue name (even blank) while using my QueueInfo table. Not sure how to approach this.

As I said, I am new to Access and have a lot to learn. Any guidance would be most appreciated.

 

[boblarson]

Double click on the link between the two tables and change the link type from a 1 to the one (either 2 or 3) which represents this:

Select all records from DataExtract and only those records which match in QueueInfo.

So what you need is what is called an Outer Join where all records from one table are selected and the ones that match in the other and then it shows blanks where there are no matching records in the second table. Otherwise if you use an INNER JOIN (which is represented by the option 1) it only returns records that match in both tables.

 

[mistahpat]

That worked. Thank you!

 

[mistahpat]

I am using: >=#5/1/2011# to get records from May 1 to date, but after using the outerjoin, I recieve records back to March. Could this be affecting my query somehow?

 

[boblarson]

Which table is that field which you have the criteria on from? If it is from the side which is the one that has the arrow pointing towards it, you will need to use the criteria on it using anothe query with just that table and then use that sub query linked to the other table like you currently have both tables.

You can't have criteria on a field which is from a table that is part of the outer join (except to use Is Null or Is Not Null). If you do, it won't work.

 

[mistahpat]

The date criteria is on the table DataExtract, which has the records for all calls. The QueueInfo table is just there to add more information to queues for reporting, etc. I need a certain set of records from the DataExtract (which is the table with blank entries in the queue column.)

I think I'm grasping what you're saying, although implementing what you're saying I'm not so sure of. Oddly enough, I do have: Like "Prague" criteria on another field on the same table, "DataExtract" and that seems to be working.

 

[boblarson]

Post your SQL for the query.

 

[mistahpat]

SELECT PSGDataExtract.Date2 AS [Date], PSGDataExtract.Time, IntervalDim.IntervalStart, PSGDataExtract.[Call Type], IIf([Call Type]="Outbound Call",1,0) AS Outbound, PSGDataExtract.Group, QueueInfo_pat.Function, QueueInfo_pat.Department, PSGDataExtract.Queue, QueueInfo_pat.Language, PSGDataExtract.Offered, PSGDataExtract.Handled, PSGDataExtract.Abn, [Speed of Answer]*60 AS [Answer Time], IIf([Answer Time]<=30,1,0) AS Accepted, [Total Handling Time]*60 AS [Handle Time]
FROM QueueInfo_pat RIGHT JOIN (IntervalDim INNER JOIN PSGDataExtract ON IntervalDim.TimeKey = PSGDataExtract.Time) ON QueueInfo_pat.Queue1 = PSGDataExtract.Queue
GROUP BY PSGDataExtract.Date2, PSGDataExtract.Time, IntervalDim.IntervalStart, PSGDataExtract.[Call Type], PSGDataExtract.Group, QueueInfo_pat.Function, QueueInfo_pat.Department, PSGDataExtract.Queue, QueueInfo_pat.Language, PSGDataExtract.Offered, PSGDataExtract.Handled, PSGDataExtract.Abn, PSGDataExtract.[Speed of Answer], PSGDataExtract.[Total Handling Time]
HAVING (((PSGDataExtract.Group) Like "Prague")) OR (((PSGDataExtract.Group) Like "Kuala Lumpur"))
ORDER BY PSGDataExtract.Date2;

 

[boblarson]

Okay, we have a few issues that might be causing some problems. You have several fields which are Access Reserved Words and therefore need to be in square brackets in the query. Some of these could cause it not to work regardless. The reserved words used are:

Time
Group
Function
Language

Here's a good reference for making sure that Access Reserved Words are not used:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[boblarson]

If you can upload a copy of the database with some bogus data in each of the tables, I (or someone else) can try to see if we can figure out the exact problem.

 

[mistahpat]

I really do appreciate your help, so thanks again. I may have to leave here shortly so will probably have to pick this back up tomorrow morning. As far as the uploading, I'd be happy to - though, does this site allow that? I don't have a repository for myself.

 

[boblarson]

Yes, you can upload. Just be sure to run Compact and Repair first and then zip the file (you can do that just by right-clicking on the file and selecting SEND TO > COMPRESSED FOLDER if you don't have WinZip or something like it and you have WinXP and above).

 

[mistahpat]

Here is the DB.

 

[mistahpat]

Oops. I should have stated: I'm using the CallsOffrdNGCC_pat query.

 

[boblarson]

Also, you shouldn't be using LIKE in the Group Criteria because you are giving EXACT criteria. It should juset be

"Prague"
"Kuala Lumpur"

Get rid of the LIKE. Only use LIKE if you are only able to provide PART of the value (and you need to include the Wildcards like this:

Like "Kua*"

or

Like "*rag*"