dlookup in Query

[Secret Squirrel]

I have the following query that I'm trying to lookup a value in my
tblAccruals. I'm trying to match my "YearsAccrued" with my tblAccruals and
then have it return the value in the "AccrualDays" field from the
tblAccruals. Does this look right? It's not working and its telling me I have
an ambiguous outer join.

SELECT tblEmployees.ID, qryVacationUsed.DaysCount AS VacationDaysUsed,
DateDiff("yyyy",[StartDate],Date()) AS YearsAccrued,
DLookUp([AccrualDays],[tblAccruals],[AccrualID]=[YearsAccrued]) AS Expr1
FROM tblAccruals, tblEmployees LEFT JOIN qryVacationUsed ON tblEmployees.ID
= qryVacationUsed.EmpID;

 

[Tom Wickerath]

Hi Secret Squirrel,

See this Microsoft KB article:

Explanation of "Ambiguous Outer Joins" Error Message
http://support.microsoft.com/kb/208878

(Disregard the "ACC2000" in the title--this document applies equally well to
all versions of Access).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Secret Squirrel]

Hi Tom,

I think I understand the joining methods but how can I join two tables if
there is nothing I can join them by?

Hi Secret Squirrel,

See this Microsoft KB article:

Explanation of "Ambiguous Outer Joins" Error Message
http://support.microsoft.com/kb/208878

(Disregard the "ACC2000" in the title--this document applies equally well to
all versions of Access).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have the following query that I'm trying to lookup a value in my
tblAccruals. I'm trying to match my "YearsAccrued" with my tblAccruals and
then have it return the value in the "AccrualDays" field from the
tblAccruals. Does this look right? It's not working and its telling me I have
an ambiguous outer join.

SELECT tblEmployees.ID, qryVacationUsed.DaysCount AS VacationDaysUsed,
DateDiff("yyyy",[StartDate],Date()) AS YearsAccrued,
DLookUp([AccrualDays],[tblAccruals],[AccrualID]=[YearsAccrued]) AS Expr1
FROM tblAccruals, tblEmployees LEFT JOIN qryVacationUsed ON tblEmployees.ID
= qryVacationUsed.EmpID;

 

[Tom Wickerath]

Hi Secret Squirrel,

...how can I join two tables if there is nothing I can join them by?

You can't. What happens if you remove "tblAccurals," from the FROM clause of
your SQL statement? As it is, I would expect the query to produce a Cartesian
Product result, even if you did not have the ambiguous outer join problem,
since you are not involving tblAccurals in any apparent join.

I don't know what your qryVacationUsed query looks like, but I suspect that
referencing it with your existing LEFT JOIN is the cause of the ambiguous
outer join issue. In other words, I'm thinking this error has nothing to do
with the use of the domain aggregrate function DLookup.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Secret Squirrel]

Tom,

If I remove that from the FROM clause then I get parameter boxes that are
looking for values for the fields from the tblAccruals.

Here is SQL from the qryVacationUsed:

SELECT tblVacationTime.EmpID, Sum(tblVacationTime.Time) AS DaysCount
FROM tblVacationTime
GROUP BY tblVacationTime.EmpID;

How would this query be causing the problem?

 

[Tom Wickerath]

Hi Secret Squirrel,

How would this query be causing the problem?

Okay, now that I can see the SQL statement, I agree that it is likely not
the source of your ambiguous outer join problem, since it is based on one
table only. What fields are present in tblAccurals?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Secret Squirrel]

tblAccruals:

AccrualID
AccrualYear
AccrualDays

What I'm looking to get from this is when the "AccrualYear" and
'YearsAccrued" are the same for an employee then return the "AccrualDays". I
thought this was going to be pretty simple.

 

[Secret Squirrel]

I think I'm on to something. I created a new query so there are no joins in
it. I have the tblAccrual and qryVacationDays on this query. Here's the SQL:

SELECT qryVacationDays.ID, qryVacationDays.YearsAccrued,
DLookUp([AccrualDays],"tblAccruals",[AccrualID]=[YearsAccrued]) AS Expr1
FROM qryVacationDays, tblAccruals;

It's working but for some reason it's duplicating the records. I have 5
records in my table and it's showing 50 records. But only 5 of the 50 records
actually have a value in the dlookup function. What could be causing this?

 

[tina]

did you post the original SQL statement verbatim? if so, i'm wondering if
you need to remove tblAccruals entirely, and change the DLookup(), as

SELECT tblEmployees.ID, qryVacationUsed.DaysCount AS VacationDaysUsed,
DateDiff("yyyy",[StartDate],Date()) AS YearsAccrued,
DLookUp("AccrualDays","tblAccruals","AccrualYear=" & [YearsAccrued]) AS
Expr1
FROM tblEmployees LEFT JOIN qryVacationUsed ON tblEmployees.ID
= qryVacationUsed.EmpID;

though, frankly, i'm not quite sure of the syntax in the criteria argument
of the DLookup - i may be using syntax more appropriate to creating a SQL
string for execution from VBA code, sorry.

hth

 

[Tom Wickerath]

What could be causing this?

That's most likely the Cartesian Product result that I mentioned earlier. My
guess is that you have 10 records in the other table or query, thus 5 x 10 =
50 records in your recordset, because you do not have a join involved.

If you can send me a copy of your database, I will have a look at it later
on. I'm getting ready to leave the house right now, and I expect to be away
for a couple of hours.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Secret Squirrel]

I did find a way to make it work. If I put "Not Is Null" in the criteria of
the dlookup then it will only show the 5 records like I want it to. I'll
email you a copy for you to look at later. Thanks for all your help Tom!
Appreciate it!

What could be causing this?

That's most likely the Cartesian Product result that I mentioned earlier. My
guess is that you have 10 records in the other table or query, thus 5 x 10 =
50 records in your recordset, because you do not have a join involved.

If you can send me a copy of your database, I will have a look at it later
on. I'm getting ready to leave the house right now, and I expect to be away
for a couple of hours.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I think I'm on to something. I created a new query so there are no joins in
it. I have the tblAccrual and qryVacationDays on this query. Here's the SQL:

SELECT qryVacationDays.ID, qryVacationDays.YearsAccrued,
DLookUp([AccrualDays],"tblAccruals",[AccrualID]=[YearsAccrued]) AS Expr1
FROM qryVacationDays, tblAccruals;

It's working but for some reason it's duplicating the records. I have 5
records in my table and it's showing 50 records. But only 5 of the 50 records
actually have a value in the dlookup function. What could be causing this?