Duplicate Field Names

[Guest]

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

 

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

 

[Baz]

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

 

[Baz]

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

 

[Guest]

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.

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

 

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

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.

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

 

[Guest]

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

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.

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.

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