dlookup in Query

S

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;
 
S

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?

Tom Wickerath said:
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 said:
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;
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Secret Squirrel,
...how can I join two tables if there is nothing I can join them by?
Click to expand...

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
__________________________________________
 
S

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?
 
S

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.
 
S

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?
 
T

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
 
T

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
__________________________________________
 
S

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!

Tom Wickerath said:
What could be causing this?
Click to expand...

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 said:
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?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Lookup 0
Query Question 1
Update Query 6
Update Query w/ Join 2
Append Query 11
Query Date Problem 8
using dlookup in a query 2
Update Query Question 3

Top