Outer join Results


I have a query with a Left Outer Join. The query returns a #DELETED value in the returned recordset if their is no corresponding record. This is what happens when I run the query stand alone.

When I run the query using recordsetopen into an RS and try to read each record I eventually get 'Record deleted' error message and it falls over.

Is there a way of placing a piece of substitute text in the offending field so that it doesn't believe a record has been deleted? As part of the query itself perhaps.

I've tried to detect the error using ISEERROR but it doesn't seem to detect the bad field

Cheers, Thanks in advance


I would like to execute the SQL statement below in vba code written for MS Access, and I want the integer values of the ID fields to be compared in the JOIN ON criteria. However, I get an error when using CInt(). I've also tried Val(), CAST(), and Convert().
Set RecordSet = "SELECT A.id, B.id FROM A LEFT OUTER JOIN B ON CInt(A.id)=CInt(B.id)"

the entire id contains only numbers. For example A.id=0123, and B.id=00123 . I want to compare the value integer of the two so that A.id=B.id is true.


Hi All

I have been running a query to find out the name and addresses of some of our customers but some data keeps coming in to the results that i dont want to see.

The data is in the Name column so I have built a second table and put the names of the items i do not wish to see in the second table using a left outer join and selecting null or not null. This works great if i know all the combinations of the name such as smith i.e d smith david smith, sam smith dd smith etc.

I would like to enter Smith into the table and have my query return everything except with the word smith in.

I can code this into the query with not like *smith* but that means everytime i want to go into it i would have to enter the query not the front end

Anyone any ideas?



I´m trying to create an append query that I need to append only new records. This is done on an hourly basis, which means that the date should be an inner join and the hour should be an outer join, right? However, access can´t handle this so I need help.

If I do an append at 12 today, the query should select todays records from hours 0 to 11 for append. Next, when I do an append at 3 pm, the query should select todays records (inner join) but only those hours not already in the table (ie 12, 13, 14 etc (outer join)).

The date and the hour are in separate fields. I need to have it like that.

I can´t use PK:s because then access messes up my running sequence, which I also need.

Please don´t tell me I have to create a querydef each time.


I have 3 tables:
T1:Client Info- Fields: FamilyID, other fields related to client info

two other tables are linked to the Client Info tableT2:Payment_Adjustment- Fields: FamilyID, Amount, other fields related to payments.T3:Charges -Fields: FamilyID, FTFee, PTFee, FTDaysCharged, PTDaysCharged, other fields related to charges.

They are all linked by FamilyID via multiple Payment_Adjustment and Charges table to every one Client Info table.

In a Query, I am trying to combine the tables to get a Client_Balance by using this formula:ClientBalance: Sum(([Charges]![FTFee]*[Charges]![FTDaysCharged])+([Charges]![PTFee]*[Charges]![PTDaysCharged])-[Payment_Adjustment]![Amount])

I don't have any data in the database yet, so I don't know if that is causing the problem, or if it is the formula, or I am just trying to do too much at once. Every time I try to run the query as a test I get the following error: The SQL statement could not be executed because it contains ambiguous outer joins.

I have spent two days trying to solve this with no luck. Thanks in advance for any advice you have.

To quickly sum up what I am trying to do, I have Query 1 and Query 2.

Query 1 has fields A and B, which correspond with Query 2 fields X and Y.
Query 1 also has field C which contains the values I want to access.

What I want is to create a field Z in Query 2, which displays the value C where A=X and B=Y

Whenever I try to create a join between Queries 1 and 2, or create an expression for field Z which contains an SQL statement to select C from Query 1 where A=X and B=Y, I get the 'ambiguous outer join' error.

I have been battling with this problem for hours, and would be incredibly grateful if someone could enlighten me as to what I am doing wrong. I want to build a query that displays identifying records from one query, excluding those that are in another query. Internet research and the unmatched query wizard suggested that the solution would be to use a left outer join, with the query on the right having a criterion of "Is Null". This query (shown below) returned a data mismatch error, although the root tables of both are text format.

	SELECT qryFBatchMakeup.BatchNumber
FROM qryFBatchMakeup 
LEFT JOIN qryReprocessedBatches
ON qryFBatchMakeup.BatchNumber = qryReprocessedBatches.BatchNumber 
WHERE (((qryReprocessedBatches.BatchNumber) Is Null));

To try and find out the cause, I removed the filter criterion and added the field 'qryReprocessedBatches.BatchNumber'.The records that were in qryFBatchMakeup but not qryReprocessedBatches were displayed as '#Error#', although I cannot find any logical reason why this isn't null. Anyone have any ideas why this is not working or know a way I could investigate the nature of the errors?

I need to join a table (qryDCFForecasts) to a subquery (z). The subquery includes all possible criteria combinations for the following two fields: "fldPkg", "fldDCF"

Both (qryDCFForecasts) and the subquery (z) include both fields.

qryDCFForecasts does not include all possible combinations of fldPkg and fldDCF. However I still need to return records for all combinations. Therefore I am attempting to Outer Join qryDCFForecasts with subquery (z) - which lists all possible combinations. However I need to use both as criteria in the join.

In the below code, the WHERE statement below is filtering out Nulls from qryDCFForecasts. Do you have suggestions on how to include Null records from qryDCFForecasts?

Quote: SELECT z.fldPkg, z.fldDCF, qryDCFForecasts.fldValue
(SELECT zz.fldPkg, yz.fldDCF
(SELECT DISTINCT fldDCF FROM tblBudget) as yz,
(SELECT DISTINCT qryForecast.fldPkg FROM qryForecast) as zz
) AS z
qryDCFForecasts ON z.fldDCF=qryDCFForecasts.fldDCF
WHERE z.fldPkg=qryDCFForecasts.fldPkg; Thank you for the help.

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first create a separate query that performs the join and then include that query in your SQL statement.

SQL Statement:

TRANSFORM Sum(tbl_Attendance.Credits) AS SumOfCredits
SELECT tbl_Attendance.SNum, tbl_Students.LName, tbl_Students.FName, tbl_Students.Campus, tbl_Students.Email, tbl_Students.FGen, tbl_CertificatesLog.Notes AS Expr1, tbl_CertificatesLog.Bronze, tbl_CertificatesLog.Silver, tbl_CertificatesLog.Gold, tbl_CertificatesLog.Platinum, Sum(tbl_Attendance.Credits) AS [Total Of Credits], Sum(tbl_WS_LearningOutcomes.LO1) AS SumOfLO1, Sum(tbl_WS_LearningOutcomes.LO2) AS SumOfLO2, Sum(tbl_WS_LearningOutcomes.LO3) AS SumOfLO3, Sum(tbl_WS_LearningOutcomes.LO4) AS SumOfLO4, Sum(tbl_WS_LearningOutcomes.LO5) AS SumOfLO5, Sum(tbl_WS_LearningOutcomes.LO6) AS SumOfLO6
FROM tbl_CertificatesLog LEFT JOIN (tbl_WS_LearningOutcomes INNER JOIN (tbl_WS INNER JOIN (tbl_Students INNER JOIN tbl_Attendance ON tbl_Students.SNUM = tbl_Attendance.SNUM) ON tbl_WS.Wcode = tbl_Attendance.WCode) ON tbl_WS_LearningOutcomes.Wcode = tbl_WS.Wcode) ON tbl_CertificatesLog.SNUM = tbl_Students.SNUM
GROUP BY tbl_Attendance.SNum, tbl_Students.LName, tbl_Students.FName, tbl_Students.Campus, tbl_Students.Email, tbl_Students.FGen, tbl_CertificatesLog.Notes, tbl_CertificatesLog.Bronze, tbl_CertificatesLog.Silver, tbl_CertificatesLog.Gold, tbl_CertificatesLog.Platinum
ORDER BY tbl_Attendance.SNum
PIVOT tbl_Attendance.Type;


Access 2010, Windows 7


I am relatively new to SQL and understand I need to create a query but what do I need to query and how do I include that query in the statement?

Any input will be greatly appreciated.

Thank you.


Forgive me if this is dumb!

If there a way to get a join to give the results two tables without a commonality.

For example - I have a tables with data for each month. If I bring into a query two tables and want to get all the data with all store numbers. So a store might only be in one table not both.

My boss thought this would be an outer join. I said no. That is not possible because the tables need to be linked and then you will only get an outer join of one table. Have I missed something?

I hope this makes sense to someone and can give me an explanation.

Thanks . . .


I have a form based on multiple tables, with one to many relationship.
In the form query, I have an outer join relationship between the tables.

In my one side tables, my primary keys are Autonumber.

On my form, my joining fields are coming from my foreign keys.
As soon as I start typing something into the forms, my foreign keys are automatically filled. Sometimes, those fields should be blank.

How do I stop the auto filling?

The form images are attached below.
The tables are: P02, NDAs, PSAs, SOWs, Proposals
On the form, the ID fields are the foreign keys (coming from P02 Table). Mostly, the other fields are coming from other tables (NDAs, PSAs, SOWs, Proposals).


Hey folks,

So access doesn't take outer joins I take it.

I have three tables.

tblGuidelines which contains the primary key pedGuide

tblCourseRecords which contains a list of courses with the primary key courseRecordID


tblCourseRecordPedMarking which is related to tblCourseRecords through a junction table and has both courseRecord and pedGuide as primary Keys.

I am trying to build a query which lists all pedGuide contained in tblGuidelines that are do not currently exist for the particular courseRecordID on tblCourseRecordPedMarking.

I attempted to use a LEFT OUTER JOIN to do this but access does not recognize the join. I then attempted to use the query builder, but the three join types allowed do not seem to provide what I need.

SELECT tblGuidelines.pedGuide, tblGuidelines.pedTitle FROM tblGuidelines LEFT OUTER JOIN tblCourseRecordPedMarking ON
tblGuidelines.pedGuide WHERE tblGuidelines.pedCategory ="Marking" AND tblCourseRecordPedMarking.coureRecordID = 1 AND
tblCourseRecordPedMarking.pedGuide IS null ;

Any ideas how I can achieve this?

I have created a query using two recordsources with an outer join on them. I'm pulling two fields from each source and then I've created another field for each source that performs a calculation on the other two columns. Everything works good except when there aren't matching records. The source that doesn't provide all it's records has a #ERROR message in the calculated field. Anyone know how to change that so it's blank?

How do i write a query for full outer join in MS-Access ?

(Right now i am using a union of left join from table t1 to t2 and remaining values of t2 to solve it but it is slow and inelegant)

Hi All,
this is the function I am using:
Function analopdays(t As Date, f As Date)

Dim actstart As Date
Dim actend As Date

If IsNull(f) And IsNull(t) Then
analopdays = 0
ElseIf IsNull(f) Or IsNull(t) Then
analopdays = 0
ElseIf ((f < (Forms![FrmQbf]!Text0)) And (t > (Forms![FrmQbf]!Text0))) Then
actend = t
actstart = Forms![FrmQbf]!Text0
analopdays = actend - actstart
ElseIf ((f < (Forms![FrmQbf]!Text2)) And (t > (Forms![FrmQbf]!Text2))) Then
actstart = f
actend = Forms![FrmQbf]!Text2
analopdays = actend - actstart
Else 't and f fall with the date range.
actstart = f
actend = t
analopdays = actend - actstart
End If

End Function

Forms![FrmQbf]!Text0 is the start of the date range and Forms![FrmQbf]!Text2 is the end of the date range. If I use this function in a query and then use that query to perform an outer join with a table, then for all table records that have no corresponding query record, i get #Error. I dont know what I am missing??


here's the db structure on which I need to query (I didn't bother writing out the Person table):




By doing an outer join from Groups to Records, I can see when there are no records for a particular group but I need to go one more level. I need to show something when there are no records for a particular Level. Any ideas?


I'm having a strange problem specific to one user's computer. All users are using the same Access 97 database. I have several queries that match up some data that may have 36 or fewer records with the output of a subquery, which has exactly 36 records. I do this with an outer join to ensure that the output of this query has exactly 36 records, with each record in the correct place. I am relying on NULL being placed in the fields for records that don't match up. The output of these queries is exported to an Excel spreadsheet, which does further processing on the data and requires the data to be in a specific location.

This works fine for all but one person's computer. On his machine, the output of these queries appears as if an inner join was used instead. I don't get the full 36 records. I only get the ones that match.

This tool has been in use for about 6 months with no problems. The person having the problem just started working here.

Has anyone ever encountered anything like this? I can't really post the database because it contains confidential information, but I can try to be more specific if needed.

I have a crosstab with years as the row headings. I want all years in the table to show up, despite whether or not the criteria I specify discludes any certain year (that year would return 0 values). The problem is I can't seem to get this to work, even with an outer join to a query of all possible years.


1996-2005 are all the possible years. However, when certain criteria are specified, the query does not display 1996 because there is no data for 1996 with those specifications. What would be a reason an outer join isn't forcing it to display 1996?

I have several subreports based on this range of years, and if they don't all display all years, they don't align correctly.

Thanks for any help.

Attached is a pdf of the query window showing the relationships and table structure; (sorry for the quality) the linkage is also permanent at the relationship window. I created a form (columnar) of Rooms; loaded a subform (columnar) of the projects; and then loaded the students (tabular) as a subform on the projects subform. The data entry is flawless; tabs through each field and form to form in sequence.

After entering several rooms data I tested it at the query level by loading the three tables: rooms, projects, and students, and the permanentely established linkage with junctions came in automatically. I thought I was home free--but when I run the query, I get zero records.

When I attempt various joins, thinking this will yeild all records from the many tables and their match, I get "ambiguous outer joins" and it says to run a separate query and add it to the SQL Statement?

Thanks for any help,

Almost funtional in Ann Arbor........

Oh, and thanks Pat Hartman for the tip on linkage -- although I may have screwed it up anyway.

Hello All,

Beginner level question!

I am working on a schema that might need outer joins for some of the queries and thereby reports I will need to produce. So, I wanted to get an idea from the pros about the following strategy:

How about leaving all the joins in the Relationships to begin with as Inner Join and change them to Outer Join as required (for a given Query. I believe this is possible)? What are the pros and cons of this approach.

Thank you

Not finding an answer? Try a Google search.