Duplicate Field Names

M

Melinda

I have two queries, 1st query is for overtime hours worked and 2nd query is
for overtime hours refused. My criteria is by a overtime code that the
person choose when entering the overtime for plowing snow. I joined those
two queries into another to get a sumofhours refused and sumof hours worked
for a YTD total hours of overtime. The problem is I have two field names
sumofhours and get an error "The specified field <field> could refer to more
than one table listed in the FROM clause of your SQL statement" Do I go
back to the table that I retried the hours from, do I refer to the query. I
am sure there was a way to extract that data with one query, but I was
struggling, so I made two queries and have now joined them. Below is my
SQL statement.

SELECT henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost, Sum(henryqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked,
Sum(refusedqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked1,
Sum([sumofhoursworked])+nz([sumofhoursworked]) AS totalhours
FROM henryqry LEFT JOIN refusedqry ON henryqry.Tmsemployeeno =
refusedqry.TMSEmployeeNo
GROUP BY henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost
HAVING (((henryqry.ClassNo)<>"11111"));


Thanks Melinda
 
J

John Spencer

You need to fully qualify the names in the select clause. Specifically the
TotalHours columns. You have two fields named SumOfHoursWorked and there is
no way to know which one you are referring to without adding the name of the
query to the name of the field.
..
SELECT henryqry.Tmsemployeeno
, henryqry.EmployeeName, henryqry.ClassNo
, henryqry.OutPost
, Sum(henryqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked
, Sum(refusedqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked1


, Sum([Henryqry].[sumofhoursworked])+nz([RefusedQry].[sumofhoursworked]) AS
totalhours


FROM henryqry LEFT JOIN refusedqry ON
henryqry.Tmsemployeeno = refusedqry.TMSEmployeeNo
GROUP BY henryqry.Tmsemployeeno, henryqry.EmployeeName
, henryqry.ClassNo, henryqry.OutPost
HAVING (((henryqry.ClassNo)<>"11111"));


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
B

Baz

Correction, got the brackets slightly wrong:

Sum((henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked)) AS
totalhours

Baz said:
Sum(henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked) AS
totalhours

Melinda said:
I have two queries, 1st query is for overtime hours worked and 2nd query is
for overtime hours refused. My criteria is by a overtime code that the
person choose when entering the overtime for plowing snow. I joined those
two queries into another to get a sumofhours refused and sumof hours worked
for a YTD total hours of overtime. The problem is I have two field names
sumofhours and get an error "The specified field <field> could refer to more
than one table listed in the FROM clause of your SQL statement" Do I go
back to the table that I retried the hours from, do I refer to the
query.
I
am sure there was a way to extract that data with one query, but I was
struggling, so I made two queries and have now joined them. Below is my
SQL statement.

SELECT henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost, Sum(henryqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked,
Sum(refusedqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked1,
Sum([sumofhoursworked])+nz([sumofhoursworked]) AS totalhours
FROM henryqry LEFT JOIN refusedqry ON henryqry.Tmsemployeeno =
refusedqry.TMSEmployeeNo
GROUP BY henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost
HAVING (((henryqry.ClassNo)<>"11111"));


Thanks Melinda
 
M

Melinda

Bingo--worked like a charm. I am fairly new to all of this so I do have one
more question so that I will understand why I just did what I did. I had
to remove the "[" brackets because it wasn't the name of the field is that
correct? Thanks for the quick response. I appreciate it.

Baz said:
Correction, got the brackets slightly wrong:

Sum((henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked)) AS
totalhours

Baz said:
Sum(henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked) AS
totalhours

Melinda said:
I have two queries, 1st query is for overtime hours worked and 2nd query is
for overtime hours refused. My criteria is by a overtime code that the
person choose when entering the overtime for plowing snow. I joined those
two queries into another to get a sumofhours refused and sumof hours worked
for a YTD total hours of overtime. The problem is I have two field names
sumofhours and get an error "The specified field <field> could refer to more
than one table listed in the FROM clause of your SQL statement" Do I go
back to the table that I retried the hours from, do I refer to the
query.
I
am sure there was a way to extract that data with one query, but I was
struggling, so I made two queries and have now joined them. Below is my
SQL statement.

SELECT henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost, Sum(henryqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked,
Sum(refusedqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked1,
Sum([sumofhoursworked])+nz([sumofhoursworked]) AS totalhours
FROM henryqry LEFT JOIN refusedqry ON henryqry.Tmsemployeeno =
refusedqry.TMSEmployeeNo
GROUP BY henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost
HAVING (((henryqry.ClassNo)<>"11111"));


Thanks Melinda
 
B

Baz

Square brackets around a field name are only necessary if the name doesn't
meet the naming rules e.g. it has a space in it. However, including the
brackets will not do any harm.

Melinda said:
Bingo--worked like a charm. I am fairly new to all of this so I do have one
more question so that I will understand why I just did what I did. I had
to remove the "[" brackets because it wasn't the name of the field is that
correct? Thanks for the quick response. I appreciate it.

Baz said:
Correction, got the brackets slightly wrong:

Sum((henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked)) AS
totalhours

Baz said:
Sum(henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked) AS
totalhours

I have two queries, 1st query is for overtime hours worked and 2nd query
is
for overtime hours refused. My criteria is by a overtime code that the
person choose when entering the overtime for plowing snow. I joined those
two queries into another to get a sumofhours refused and sumof hours
worked
for a YTD total hours of overtime. The problem is I have two field
names
sumofhours and get an error "The specified field <field> could refer to
more
than one table listed in the FROM clause of your SQL statement" Do
I
go
back to the table that I retried the hours from, do I refer to the query.
I
am sure there was a way to extract that data with one query, but I was
struggling, so I made two queries and have now joined them. Below
is
my
SQL statement.

SELECT henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost, Sum(henryqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked,
Sum(refusedqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked1,
Sum([sumofhoursworked])+nz([sumofhoursworked]) AS totalhours
FROM henryqry LEFT JOIN refusedqry ON henryqry.Tmsemployeeno =
refusedqry.TMSEmployeeNo
GROUP BY henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost
HAVING (((henryqry.ClassNo)<>"11111"));


Thanks Melinda
 
M

Melinda

Thanks again, just trying to understand why I do what I did. Thanks

Baz said:
Square brackets around a field name are only necessary if the name doesn't
meet the naming rules e.g. it has a space in it. However, including the
brackets will not do any harm.

Melinda said:
Bingo--worked like a charm. I am fairly new to all of this so I do have one
more question so that I will understand why I just did what I did. I had
to remove the "[" brackets because it wasn't the name of the field is that
correct? Thanks for the quick response. I appreciate it.

Baz said:
Correction, got the brackets slightly wrong:

Sum((henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked)) AS
totalhours

Sum(henryquery.sumofhoursworked)+nz(refusedquery.sumofhoursworked) AS
totalhours

I have two queries, 1st query is for overtime hours worked and 2nd query
is
for overtime hours refused. My criteria is by a overtime code that the
person choose when entering the overtime for plowing snow. I joined
those
two queries into another to get a sumofhours refused and sumof hours
worked
for a YTD total hours of overtime. The problem is I have two field
names
sumofhours and get an error "The specified field <field> could refer to
more
than one table listed in the FROM clause of your SQL statement" Do I
go
back to the table that I retried the hours from, do I refer to the
query.
I
am sure there was a way to extract that data with one query, but I was
struggling, so I made two queries and have now joined them. Below is
my
SQL statement.

SELECT henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo,
henryqry.OutPost, Sum(henryqry.SumOfHoursWorked) AS
SumOfSumOfHoursWorked,
Sum(refusedqry.SumOfHoursWorked) AS SumOfSumOfHoursWorked1,
Sum([sumofhoursworked])+nz([sumofhoursworked]) AS totalhours
FROM henryqry LEFT JOIN refusedqry ON henryqry.Tmsemployeeno =
refusedqry.TMSEmployeeNo
GROUP BY henryqry.Tmsemployeeno, henryqry.EmployeeName,
henryqry.ClassNo,
henryqry.OutPost
HAVING (((henryqry.ClassNo)<>"11111"));


Thanks Melinda
 

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

Top