How to change this sql statement to work in Access

  • Thread starter Thread starter confused
  • Start date Start date
C

confused

I have made several attempts to re-write this sql to work in Access, I
keep getting errors. Here is the sql:

SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS) end,
[Personal] = case m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE
 
I have made several attempts to re-write this sql to work in Access, I
keep getting errors. Here is the sql:

SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS) end,
[Personal] = case m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE
Click to expand...

Ok, the problem is that you're using T-SQL and not Access SQL. The
following functions/keywords won't work:

- Substring
- Case...When...End

The good news is that you can use equivalent VBA functions (Instr/Left/
Mid/Right in VBA for Substring) and the IIF function to replace the
case (not the best solution will should get you working). Because of
the inefficiencies of using VBA functions, you may find that splitting
this query into 2+ queries makes more sense, but without knowing what
your schema looks like it's hard to say.

Hope this helps,
James
 
I have made several attempts to re-write this sql to work in Access, I
keep getting errors.  Here is the sql:
Click to expand...
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case  m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case  m.matter_code when '0A015' then SUM(t.BASE_HRS)  end,
[Personal] = case  m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case  m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case   when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case  m.matter_code when '0A017' then SUM(t.BASE_HRS)  end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
  INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
  AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE
Click to expand...

Ok, the problem is that you're using T-SQL and not Access SQL. The
following functions/keywords won't work:

- Substring
- Case...When...End

The good news is that you can use equivalent VBA functions (Instr/Left/
Mid/Right in VBA for Substring) and the IIF function to replace the
case (not the best solution will should get you working). Because of
the inefficiencies of using VBA functions, you may find that splitting
this query into 2+ queries makes more sense, but without knowing what
your schema looks like it's hard to say.

Hope this helps,
James- Hide quoted text -

- Show quoted text -
Click to expand...

Okay, I changed the substring to left, but IIF did not work; might
have wrote it wrong, or is it even possible to use IIF in the query
design?

Thanks for the help!
 
I have made several attempts to re-write this sql to work in Access, I
keep getting errors. Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS) end,
[Personal] = case m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE
Click to expand...
Click to expand...
Ok, the problem is that you're using T-SQL and not Access SQL. The
following functions/keywords won't work:
Click to expand...
- Substring
- Case...When...End
Click to expand...
The good news is that you can use equivalent VBA functions (Instr/Left/
Mid/Right in VBA for Substring) and the IIF function to replace the
case (not the best solution will should get you working). Because of
the inefficiencies of using VBA functions, you may find that splitting
this query into 2+ queries makes more sense, but without knowing what
your schema looks like it's hard to say.
Click to expand...
Hope this helps,
James- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Okay, I changed the substring to left, but IIF did not work; might
have wrote it wrong, or is it even possible to use IIF in the query
design?

Thanks for the help!
Click to expand...

You can use IIF in query design - the syntax is:

=IIF(condition, do if true, do if false)

.... so I just created a query on my db:

SELECT IIf(1=2,Count([lngLinkID]),Count([txtDescription])) AS x
FROM tblAttribGroup;

You might find it easier to write it in SQL view in the query designer
than using the GUI. For your query, the syntax would be something
like:

SELECT IIF(m.matter_code="0A016",SUM(t.BASE_HRS),NULL) as
Personal ... etc.

Hope this helps,
James
 
I have made several attempts to re-write this sql to work in Access, I
keep getting errors. Here is the sql:

SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS) end,
[Personal] = case m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE
Click to expand...

Several problems in addition to those that Minton pointed out. You can use
either IIF or - better if there are multiple branches - Switch() in place of
the CASE statement; your [Hours] = null selection won't work (it will always
return 0 because it will try to evaluate the expression as true or false, and
nothing is ever equal to NULL); and you need to use # rather than ' as a date
delimiter.



John W. Vinson JVinson *at* Wysard Of Info *dot* com
 
Another alternative would be to create another table which contains the
translations of your [matter_code] field into text (my guess is that you
already have this table; is that HBM_MATTER?).

Then create a Crosstab query where you select your Emp_No, [Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs] field as
your Value (change the "group by" under this field to SUM).

This will return the columns in alphabetical order. If you want them to
come out in the order "Sick", "Vac", "Personal", ... you will need to set the
queries "Column Headings" property (right click in the grey portion at the
top of the query grid, select properties and enter the values in the "Column
Headings property). If you do this, you need to make sure the text in the
column headings matches exactly the values in your "Matter_Name" column or
you will see the header with no values in the field.

I also think you are going to have to change the "." between the dbo and the
table name with an underscore. When you link tables to Access from SQL
server, Access replaces the "." with an "_"

Finally, as John mentioned, you will need to wrap your dates in the # symbol
rather than single quotes:

t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#

HTH
Dale
 
Another alternative would be to create another table which contains the
translations of your [matter_code] field into text (my guess is that you
already have this table; is that HBM_MATTER?).

Then create a Crosstab query where you select your Emp_No, [Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs] field as
your Value (change the "group by" under this field to SUM).  

This will return the columns in alphabetical order.  If you want them to
come out in the order "Sick", "Vac", "Personal", ... you will need to set the
queries "Column Headings" property (right click in the grey portion at the
top of the query grid, select properties and enter the values in the "Column
Headings property).  If you do this, you need to make sure the text in the
column headings matches exactly the values in your "Matter_Name" column or
you will see the header with no values in the field.

I also think you are going to have to change the "." between the dbo and the
table name with an underscore.  When you link tables to Access from SQL
server, Access replaces the "." with an "_"

Finally, as John mentioned, you will need to wrap your dates in the # symbol
rather than single quotes:

t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



confused said:
I have made several attempts to re-write this sql to work in Access, I
keep getting errors.  Here is the sql:
Click to expand...
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case  m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case  m.matter_code when '0A015' then SUM(t.BASE_HRS)  end,
[Personal] = case  m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case  m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case   when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case  m.matter_code when '0A017' then SUM(t.BASE_HRS)  end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
  INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
  AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In ('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));

This works, but not right. It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11 times
each at 8 hrs.
I will start working on the switch function.

Thanks for all the help,
 
Another alternative would be to create another table which contains the
translations of your [matter_code] field into text (my guess is that you
already have this table; is that HBM_MATTER?).
Click to expand...
Then create a Crosstab query where you select your Emp_No, [Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs] field as
your Value (change the "group by" under this field to SUM).  
Click to expand...
This will return the columns in alphabetical order.  If you want them to
come out in the order "Sick", "Vac", "Personal", ... you will need to set the
queries "Column Headings" property (right click in the grey portion at the
top of the query grid, select properties and enter the values in the "Column
Headings property).  If you do this, you need to make sure the text inthe
column headings matches exactly the values in your "Matter_Name" column or
you will see the header with no values in the field.
Click to expand...
I also think you are going to have to change the "." between the dbo andthe
table name with an underscore.  When you link tables to Access from SQL
server, Access replaces the "." with an "_"
Click to expand...
Finally, as John mentioned, you will need to wrap your dates in the # symbol
rather than single quotes:
Click to expand...
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
Click to expand...
HTH
Dale
Click to expand...
email address is invalid
Please reply to newsgroup only.
Click to expand...
confused said:
I have made several attempts to re-write this sql to work in Access, I
keep getting errors.  Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case  m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case  m.matter_code when '0A015' then SUM(t.BASE_HRS)  end,
[Personal] = case  m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case  m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case   when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case  m.matter_code when '0A017' then SUM(t.BASE_HRS)  end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
  INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
  AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In ('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));

This works, but not right.  It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11 times
each at 8 hrs.
I will start working on the switch function.

Thanks for all the help,- Hide quoted text -

- Show quoted text -
Click to expand...

Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for 88
 
Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for 88
Click to expand...

Are you sure that isn't due to something in your GROUP BY? Like 11 distinct
t.TRAN_DATE values between 11/1 and 11/15?

--
HTH,
George



Another alternative would be to create another table which contains the
translations of your [matter_code] field into text (my guess is that you
already have this table; is that HBM_MATTER?).
Click to expand...
Then create a Crosstab query where you select your Emp_No, [Emp_Name]
(don't
use [Name] as a field name as it is a reserved word in Access) as your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs] field
as
your Value (change the "group by" under this field to SUM).
Click to expand...
This will return the columns in alphabetical order. If you want them to
come out in the order "Sick", "Vac", "Personal", ... you will need to
set the
queries "Column Headings" property (right click in the grey portion at
the
top of the query grid, select properties and enter the values in the
"Column
Headings property). If you do this, you need to make sure the text in
the
column headings matches exactly the values in your "Matter_Name" column
or
you will see the header with no values in the field.
Click to expand...
I also think you are going to have to change the "." between the dbo and
the
table name with an underscore. When you link tables to Access from SQL
server, Access replaces the "." with an "_"
Click to expand...
Finally, as John mentioned, you will need to wrap your dates in the #
symbol
rather than single quotes:
Click to expand...
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
Click to expand...
HTH
Dale
Click to expand...
email address is invalid
Please reply to newsgroup only.
Click to expand...
confused said:
I have made several attempts to re-write this sql to work in Access, I
keep getting errors. Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS) end,
[Personal] = case m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In ('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));

This works, but not right. It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11 times
each at 8 hrs.
I will start working on the switch function.

Thanks for all the help,- Hide quoted text -

- Show quoted text -
Click to expand...

Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for 88
 
Try removing your Matter_Code and Tran_Date fields from the Group By clause.

Also, your IIF clauses require three parameters, not 2.

IIF(m.[Matter_Code] = '0A014', Sum([T].[Base_Hrs]), 0) as Sick

Did you try this as a CrossTab? It would be a whole lot cleaner!

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



confused said:
Another alternative would be to create another table which contains the
translations of your [matter_code] field into text (my guess is that you
already have this table; is that HBM_MATTER?).
Click to expand...
Then create a Crosstab query where you select your Emp_No, [Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs] field as
your Value (change the "group by" under this field to SUM).
Click to expand...
This will return the columns in alphabetical order. If you want them to
come out in the order "Sick", "Vac", "Personal", ... you will need to set the
queries "Column Headings" property (right click in the grey portion at the
top of the query grid, select properties and enter the values in the "Column
Headings property). If you do this, you need to make sure the text in the
column headings matches exactly the values in your "Matter_Name" column or
you will see the header with no values in the field.
Click to expand...
I also think you are going to have to change the "." between the dbo and the
table name with an underscore. When you link tables to Access from SQL
server, Access replaces the "." with an "_"
Click to expand...
Finally, as John mentioned, you will need to wrap your dates in the # symbol
rather than single quotes:
Click to expand...
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
Click to expand...
HTH
Dale
Click to expand...
email address is invalid
Please reply to newsgroup only.
Click to expand...
:
I have made several attempts to re-write this sql to work in Access, I
keep getting errors. Here is the sql:
Click to expand...
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS) end,
[Personal] = case m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In ('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));

This works, but not right. It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11 times
each at 8 hrs.
I will start working on the switch function.

Thanks for all the help,- Hide quoted text -

- Show quoted text -
Click to expand...

Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for 88
Click to expand...
 
Try removing your Matter_Code and Tran_Date fields from the Group By clause.

Also, your IIF clauses require three parameters, not 2.

IIF(m.[Matter_Code] = '0A014', Sum([T].[Base_Hrs]), 0) as Sick

Did you try this as a CrossTab?  It would be a whole lot cleaner!

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



confused said:
Another alternative would be to create another table which contains the
translations of your [matter_code] field into text (my guess is thatyou
already have this table; is that HBM_MATTER?).
Then create a Crosstab query where you select your Emp_No, [Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs] field as
your Value (change the "group by" under this field to SUM).  
This will return the columns in alphabetical order.  If you want them to
come out in the order "Sick", "Vac", "Personal", ... you will need to set the
queries "Column Headings" property (right click in the grey portion at the
top of the query grid, select properties and enter the values in the"Column
Headings property).  If you do this, you need to make sure the text in the
column headings matches exactly the values in your "Matter_Name" column or
you will see the header with no values in the field.
I also think you are going to have to change the "." between the dboand the
table name with an underscore.  When you link tables to Access from SQL
server, Access replaces the "." with an "_"
Finally, as John mentioned, you will need to wrap your dates in the # symbol
rather than single quotes:
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have made several attempts to re-write this sql to work in Access, I
keep getting errors.  Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case  m.matter_code when '0A014' then SUM(t.BASE_HRS)end,
[Vac] = case  m.matter_code when '0A015' then SUM(t.BASE_HRS)  end,
[Personal] = case  m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case  m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case   when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case  m.matter_code when '0A017' then SUM(t.BASE_HRS)  end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
  INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
  AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
- Show quoted text -
I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In ('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));
This works, but not right.  It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11 times
each at 8 hrs.
I will start working on the switch function.
Thanks for all the help,- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for  88- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Once again, I need to say thank you,
I tried almost all the suggestions; then I went back and got rid of
the having part put my where and group clause like
I did in sql analyzer and at last the data is correct.
 
Try removing your Matter_Code and Tran_Date fields from the Group By clause.
Click to expand...
Also, your IIF clauses require three parameters, not 2.
Click to expand...
IIF(m.[Matter_Code] = '0A014', Sum([T].[Base_Hrs]), 0) as Sick
Click to expand...
Did you try this as a CrossTab?  It would be a whole lot cleaner!
Click to expand...
HTH
Dale
Click to expand...
email address is invalid
Please reply to newsgroup only.
Click to expand...
confused said:
Another alternative would be to create another table which contains the
translations of your [matter_code] field into text (my guess is that you
already have this table; is that HBM_MATTER?).
Then create a Crosstab query where you select your Emp_No, [Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs] field as
your Value (change the "group by" under this field to SUM).  
This will return the columns in alphabetical order.  If you wantthem to
come out in the order "Sick", "Vac", "Personal", ... you will needto set the
queries "Column Headings" property (right click in the grey portion at the
top of the query grid, select properties and enter the values in the "Column
Headings property).  If you do this, you need to make sure the text in the
column headings matches exactly the values in your "Matter_Name" column or
you will see the header with no values in the field.
I also think you are going to have to change the "." between the dbo and the
table name with an underscore.  When you link tables to Access from SQL
server, Access replaces the "." with an "_"
Finally, as John mentioned, you will need to wrap your dates in the # symbol
rather than single quotes:
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have made several attempts to re-write this sql to work in Access, I
keep getting errors.  Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] = null,
[Sick] = case  m.matter_code when '0A014' then SUM(t.BASE_HRS) end,
[Vac] = case  m.matter_code when '0A015' then SUM(t.BASE_HRS)  end,
[Personal] = case  m.matter_code when '0A016' then SUM(t.BASE_HRS)
end,
[Holiday] = case  m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case   when m.matter_code in( '0A018','0A025','0A033') then
SUM(t.BASE_HRS)end,
[STD] = case  m.matter_code when '0A017' then SUM(t.BASE_HRS)  end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
  INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO) INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS <> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033', '0A025')
  AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
- Show quoted text -
I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In ('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));
This works, but not right.  It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11 times
each at 8 hrs.
I will start working on the switch function.
Thanks for all the help,- Hide quoted text -
- Show quoted text -
Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for  88- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Once again, I need to say thank you,
I tried almost all the suggestions; then I went back and got rid of
the having part put my where and group clause like
I did in sql analyzer and at last the data is correct.- Hide quoted text -

- Show quoted text -
Click to expand...

The query give me one row per person per matter_code:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, 0 AS Hours, IIf([m].[MATTER_CODE]='0A014',
(Sum(t.BASE_HRS))) AS Sick, IIf([m].[MATTER_CODE]='0A015',
(Sum(t.BASE_HRS))) AS Vac, IIf([m].[MATTER_CODE]='0A016',
(Sum(t.BASE_HRS))) AS Personal, IIf([m].[MATTER_CODE]='0A013',
(Sum(t.BASE_HRS))) AS Holiday, IIf([m].[MATTER_CODE] In
('0A018','0A025','0A033'),(Sum(t.BASE_HRS))) AS Other, IIf([m].
[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD into
TBL_HRHrsPerEmpbyCat
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, m.MATTER_CODE, p.EMPLOYEE_CODE
UNION SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No,
(n.LAST_NAME+', '+n.FIRST_NAME) AS Name, Sum(t.BASE_HRS) AS Hours,
Null AS sick, Null AS vac, Null AS personal, Null AS Holiday, Null AS
other, Null AS std
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) Not In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, p.EMPLOYEE_CODE
ORDER BY Name;


How can I get it to have just one row per person?
 
The query give me one row per person per matter_code:
How can I get it to have just one row per person?
Click to expand...

1) The first part of your UNION query has the following:
GROUP BY n.LAST_NAME, n.FIRST_NAME, m.MATTER_CODE, p.EMPLOYEE_CODE
Click to expand...

If you don't want to see one record per MatterCode, then you should remove
it from the GROUP BY of the first query:
GROUP BY n.LAST_NAME, n.FIRST_NAME, p.EMPLOYEE_CODE
Click to expand...


2) This is a UNION query. Any employee that appears in both datasets being
UNIONed will have 2 records in the final result. That's just how UNION
queries work. If you want to see those 2 rows combined into one row, then
you need to create a 2nd query that Sums the results of the UNION, grouped
on Name.

--
HTH,
George


Try removing your Matter_Code and Tran_Date fields from the Group By
clause.
Click to expand...
Also, your IIF clauses require three parameters, not 2.
Click to expand...
IIF(m.[Matter_Code] = '0A014', Sum([T].[Base_Hrs]), 0) as Sick
Click to expand...
Did you try this as a CrossTab? It would be a whole lot cleaner!
Click to expand...
HTH
Dale
Click to expand...
email address is invalid
Please reply to newsgroup only.
Click to expand...
confused said:
Another alternative would be to create another table which
contains the
translations of your [matter_code] field into text (my guess is
that you
already have this table; is that HBM_MATTER?).
Then create a Crosstab query where you select your Emp_No,
[Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as
your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs]
field as
your Value (change the "group by" under this field to SUM).
This will return the columns in alphabetical order. If you want
them to
come out in the order "Sick", "Vac", "Personal", ... you will need
to set the
queries "Column Headings" property (right click in the grey
portion at the
top of the query grid, select properties and enter the values in
the "Column
Headings property). If you do this, you need to make sure the text
in the
column headings matches exactly the values in your "Matter_Name"
column or
you will see the header with no values in the field.
I also think you are going to have to change the "." between the
dbo and the
table name with an underscore. When you link tables to Access from
SQL
server, Access replaces the "." with an "_"
Finally, as John mentioned, you will need to wrap your dates in
the # symbol
rather than single quotes:
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have made several attempts to re-write this sql to work in
Access, I
keep getting errors. Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] =
null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS)
end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS)
end,
[Personal] = case m.matter_code when '0A016' then
SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033')
then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO)
INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS
<> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033',
'0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
- Show quoted text -
I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No,
n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In
('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON
p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025'))
AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));
This works, but not right. It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11
times
each at 8 hrs.
I will start working on the switch function.
Thanks for all the help,- Hide quoted text -
- Show quoted text -
Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for 88- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Once again, I need to say thank you,
I tried almost all the suggestions; then I went back and got rid of
the having part put my where and group clause like
I did in sql analyzer and at last the data is correct.- Hide quoted text -

- Show quoted text -
Click to expand...

The query give me one row per person per matter_code:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, 0 AS Hours, IIf([m].[MATTER_CODE]='0A014',
(Sum(t.BASE_HRS))) AS Sick, IIf([m].[MATTER_CODE]='0A015',
(Sum(t.BASE_HRS))) AS Vac, IIf([m].[MATTER_CODE]='0A016',
(Sum(t.BASE_HRS))) AS Personal, IIf([m].[MATTER_CODE]='0A013',
(Sum(t.BASE_HRS))) AS Holiday, IIf([m].[MATTER_CODE] In
('0A018','0A025','0A033'),(Sum(t.BASE_HRS))) AS Other, IIf([m].
[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD into
TBL_HRHrsPerEmpbyCat
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, m.MATTER_CODE, p.EMPLOYEE_CODE
UNION SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No,
(n.LAST_NAME+', '+n.FIRST_NAME) AS Name, Sum(t.BASE_HRS) AS Hours,
Null AS sick, Null AS vac, Null AS personal, Null AS Holiday, Null AS
other, Null AS std
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) Not In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, p.EMPLOYEE_CODE
ORDER BY Name;


How can I get it to have just one row per person?
 
The query give me one row per person per matter_code:
How can I get it to have just one row per person?
Click to expand...

1) The first part of your UNION query has the following:
GROUP BY n.LAST_NAME, n.FIRST_NAME, m.MATTER_CODE, p.EMPLOYEE_CODE
Click to expand...

If you don't want to see one record per MatterCode, then you should remove
it from the GROUP BY of the first query:
GROUP BY n.LAST_NAME, n.FIRST_NAME, p.EMPLOYEE_CODE
Click to expand...

2) This is a UNION query. Any employee that appears in both datasets being
UNIONed will have 2 records in the final result. That's just how UNION
queries work. If you want to see those 2 rows combined into one row, then
you need to create a 2nd query that Sums the results of the UNION, grouped
on Name.

--
HTH,
George


Try removing your Matter_Code and Tran_Date fields from the Group By
clause.
Also, your IIF clauses require three parameters, not 2.
IIF(m.[Matter_Code] = '0A014', Sum([T].[Base_Hrs]), 0) as Sick
Did you try this as a CrossTab? It would be a whole lot cleaner!
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
Another alternative would be to create another table which
contains the
translations of your [matter_code] field into text (my guess is
that you
already have this table; is that HBM_MATTER?).
Then create a Crosstab query where you select your Emp_No,
[Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access) as
your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs]
field as
your Value (change the "group by" under this field to SUM).
This will return the columns in alphabetical order. If you want
them to
come out in the order "Sick", "Vac", "Personal", ... you will need
to set the
queries "Column Headings" property (right click in the grey
portion at the
top of the query grid, select properties and enter the values in
the "Column
Headings property). If you do this, you need to make sure the text
in the
column headings matches exactly the values in your "Matter_Name"
column or
you will see the header with no values in the field.
I also think you are going to have to change the "." between the
dbo and the
table name with an underscore. When you link tables to Access from
SQL
server, Access replaces the "." with an "_"
Finally, as John mentioned, you will need to wrap your dates in
the # symbol
rather than single quotes:
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have made several attempts to re-write this sql to work in
Access, I
keep getting errors. Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] =
null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS)
end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS)
end,
[Personal] = case m.matter_code when '0A016' then
SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033')
then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS)end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO)
INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS
<> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033',
'0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
- Show quoted text -
I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No,
n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In
('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON
p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025'))
AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));
This works, but not right. It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11
times
each at 8 hrs.
I will start working on the switch function.
Thanks for all the help,- Hide quoted text -
- Show quoted text -
Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for 88- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Once again, I need to say thank you,
I tried almost all the suggestions; then I went back and got rid of
the having part put my where and group clause like
I did in sql analyzer and at last the data is correct.- Hide quoted text-
Click to expand...
- Show quoted text -
Click to expand...

The query give me one row per person per matter_code:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, 0  AS Hours, IIf([m].[MATTER_CODE]='0A014',
(Sum(t.BASE_HRS))) AS Sick, IIf([m].[MATTER_CODE]='0A015',
(Sum(t.BASE_HRS))) AS Vac, IIf([m].[MATTER_CODE]='0A016',
(Sum(t.BASE_HRS))) AS Personal, IIf([m].[MATTER_CODE]='0A013',
(Sum(t.BASE_HRS))) AS Holiday, IIf([m].[MATTER_CODE] In
('0A018','0A025','0A033'),(Sum(t.BASE_HRS))) AS Other, IIf([m].
[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD  into
TBL_HRHrsPerEmpbyCat
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, m.MATTER_CODE, p.EMPLOYEE_CODE
UNION SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No,
(n.LAST_NAME+', '+n.FIRST_NAME) AS Name, Sum(t.BASE_HRS) AS Hours,
Null AS sick, Null AS vac, Null AS personal, Null AS Holiday, Null AS
other, Null AS std
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) Not In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, p.EMPLOYEE_CODE
ORDER BY Name;

How can I get it to have just one row per person?- Hide quoted text -

- Show quoted text -
Click to expand...

I can not remove the group by because I have the sum(t.base_hrs)
throughout both select statements.
I tried to have the bottom select included in the first one, that
doesn't work, everyone ends up with the same # of hours.
I can insert the first select statement into a table; is there a way
to update that table with the second select statement?
Are there recordsets in access?
 
1) The first part of your UNION query has the following:
Click to expand...
If you don't want to see one record per MatterCode, then you should remove
it from the GROUP BY of the first query:
Click to expand...
2) This is a UNION query. Any employee that appears in both datasets being
UNIONed will have 2 records in the final result. That's just how UNION
queries work. If you want to see those 2 rows combined into one row, then
you need to create a 2nd query that Sums the results of the UNION, grouped
on Name.
"confused" <[email protected]> wrote in message
Click to expand...
Try removing your Matter_Code and Tran_Date fields from the Group By
clause.
Also, your IIF clauses require three parameters, not 2.
IIF(m.[Matter_Code] = '0A014', Sum([T].[Base_Hrs]), 0) as Sick
Did you try this as a CrossTab? It would be a whole lot cleaner!
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
Another alternative would be to create another table which
contains the
translations of your [matter_code] field into text (my guess is
that you
already have this table; is that HBM_MATTER?).
Then create a Crosstab query where you select your Emp_No,
[Emp_Name] (don't
use [Name] as a field name as it is a reserved word in Access)as
your
RowHeaders, MATTER_NAME as your ColumnHeader, and the [Base_Hrs]
field as
your Value (change the "group by" under this field to SUM).
This will return the columns in alphabetical order. If you want
them to
come out in the order "Sick", "Vac", "Personal", ... you will need
to set the
queries "Column Headings" property (right click in the grey
portion at the
top of the query grid, select properties and enter the values in
the "Column
Headings property). If you do this, you need to make sure the text
in the
column headings matches exactly the values in your "Matter_Name"
column or
you will see the header with no values in the field.
I also think you are going to have to change the "." between the
dbo and the
table name with an underscore. When you link tables to Access from
SQL
server, Access replaces the "." with an "_"
Finally, as John mentioned, you will need to wrap your dates in
the # symbol
rather than single quotes:
t.TRAN_DATE BETWEEN #11/01/2007# AND #11/15/2007#
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have made several attempts to re-write this sql to work in
Access, I
keep getting errors. Here is the sql:
SELECT (substring(n.last_name, 1,3) + p.employee_code) as
Emp_No,n.LAST_NAME + ', ' + n.FIRST_NAME as [Name], [Hours] =
null,
[Sick] = case m.matter_code when '0A014' then SUM(t.BASE_HRS)
end,
[Vac] = case m.matter_code when '0A015' then SUM(t.BASE_HRS)
end,
[Personal] = case m.matter_code when '0A016' then
SUM(t.BASE_HRS)
end,
[Holiday] = case m.matter_code when '0A013' then SUM(t.BASE_HRS)
end,
[Other] = case when m.matter_code in( '0A018','0A025','0A033')
then
SUM(t.BASE_HRS)end,
[STD] = case m.matter_code when '0A017' then SUM(t.BASE_HRS) end
FROM ((dbo.TAT_TIME AS t INNER JOIN dbo.HBM_MATTER AS m ON
t.MATTER_UNO=m.MATTER_UNO)
INNER JOIN dbo.HBM_PERSNL AS p ON t.TK_EMPL_UNO=p.EMPL_UNO)
INNER
JOIN dbo.HBM_NAME AS n ON p.NAME_UNO=n.NAME_UNO
WHERE p.INACTIVE <> 'Y' AND t.WIP_STATUS <> 'X' AND t.BASE_HRS
<> 0
AND m.MATTER_CODE IN
('0A013','0A014','0A015','0A016','0A017','0A018','0A033',
'0A025')
AND t.TRAN_DATE BETWEEN '11/01/2007' AND '11/15/2007'
GROUP BY n.LAST_NAME,n.FIRST_NAME, m.MATTER_NAME,
m.MATTER_CODE,p.EMPLOYEE_CODE- Hide quoted text -
- Show quoted text -
I have modifed my sql to this:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No,
n.LAST_NAME+',
'+n.FIRST_NAME AS Name, "" AS Hours,
IIf([m].[MATTER_CODE]='0A014',(Sum(t.BASE_HRS))) AS Sick,
IIf([m].[MATTER_CODE]='0A015',(Sum(t.BASE_HRS))) AS Vac,
IIf([m].[MATTER_CODE]='0A016',(Sum(t.BASE_HRS))) AS Personal,
IIf([m].[MATTER_CODE]='0A013',(Sum(t.BASE_HRS))) AS Holiday,
IIf([m].[MATTER_CODE] In
('0A018','0A025','0A033'),(Sum(t.BASE_HRS)))
AS Other,
IIf([m].[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON
p.NAME_UNO
= n.NAME_UNO
GROUP BY (Left(n.LAST_NAME,3)+p.employee_code), n.LAST_NAME+',
'+n.FIRST_NAME, p.INACTIVE, t.WIP_STATUS, t.BASE_HRS, m.MATTER_CODE,
t.TRAN_DATE
HAVING (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025'))
AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#));
This works, but not right. It does not sum base_hrs, example one
person should show 88 for personal, instead I get that person 11
times
each at 8 hrs.
I will start working on the switch function.
Thanks for all the help,- Hide quoted text -
- Show quoted text -
Okay, switch () function returns the same as the IIF function.
11 rows @ 8 each instead of 1 row for 88- Hide quoted text -
- Show quoted text -
Once again, I need to say thank you,
I tried almost all the suggestions; then I went back and got rid of
the having part put my where and group clause like
I did in sql analyzer and at last the data is correct.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
The query give me one row per person per matter_code:
SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No, n.LAST_NAME+',
'+n.FIRST_NAME AS Name, 0  AS Hours, IIf([m].[MATTER_CODE]='0A014',
(Sum(t.BASE_HRS))) AS Sick, IIf([m].[MATTER_CODE]='0A015',
(Sum(t.BASE_HRS))) AS Vac, IIf([m].[MATTER_CODE]='0A016',
(Sum(t.BASE_HRS))) AS Personal, IIf([m].[MATTER_CODE]='0A013',
(Sum(t.BASE_HRS))) AS Holiday, IIf([m].[MATTER_CODE] In
('0A018','0A025','0A033'),(Sum(t.BASE_HRS))) AS Other, IIf([m].
[MATTER_CODE]='0A017',(Sum(t.BASE_HRS))) AS STD  into
TBL_HRHrsPerEmpbyCat
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, m.MATTER_CODE, p.EMPLOYEE_CODE
UNION SELECT (Left(n.LAST_NAME,3)+p.employee_code) AS Emp_No,
(n.LAST_NAME+', '+n.FIRST_NAME) AS Name, Sum(t.BASE_HRS) AS Hours,
Null AS sick, Null AS vac, Null AS personal, Null AS Holiday, Null AS
other, Null AS std
FROM ((dbo_TAT_TIME AS t INNER JOIN dbo_HBM_MATTER AS m ON
t.MATTER_UNO = m.MATTER_UNO) INNER JOIN dbo_HBM_PERSNL AS p ON
t.TK_EMPL_UNO = p.EMPL_UNO) INNER JOIN dbo_HBM_NAME AS n ON p.NAME_UNO
= n.NAME_UNO
WHERE (((p.INACTIVE)<>'Y') AND ((t.WIP_STATUS)<>'X') AND
((t.BASE_HRS)<>0) AND ((m.MATTER_CODE) Not In
('0A013','0A014','0A015','0A016','0A017','0A018','0A033','0A025')) AND
((t.TRAN_DATE)>=#11/1/2007# And (t.TRAN_DATE)<=#11/15/2007#))
GROUP BY n.LAST_NAME, n.FIRST_NAME, p.EMPLOYEE_CODE
ORDER BY Name;
Click to expand...
How can I get it to have just one row per person?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I can not remove the group by because I have the sum(t.base_hrs)
throughout both select statements.
I tried to have the bottom select included in the first one, that
doesn't work, everyone ends up with the same # of hours.
I can insert the first select statement into a table; is there a way
to update that table with the second select statement?
Are there recordsets in access?- Hide quoted text -

- Show quoted text -
Click to expand...

Answered my own question, yes, I can create a recordset; I can update
the table with the second select statement.
Just need to read how to use the recordset to update table for each
person
 
2) This is a UNION query. Any employee that appears in both datasets being
UNIONed will have 2 records in the final result. That's just how UNION
queries work. If you want to see those 2 rows combined into one row, then
you need to create a 2nd query that Sums the results of the UNION, grouped
on Name.
Click to expand...

Not necessarily, two records. A UNION Query without the ALL keyword
(UNION ALL) will do the same thing as a SELECT DISTINCT, so if records
from both sources are identical, there will only be one in the
recordset.
 
Not necessarily, two records. A UNION Query without the ALL keyword
(UNION ALL) will do the same thing as a SELECT DISTINCT, so if records
from both sources are identical, there will only be one in the
recordset.
Click to expand...

Fix my problem:
I used the Insert into @@Temp
sum the hour types and grouped by name.
 
You are quite right and point taken. "... that's just how Union queries
work" was a tad too emphatic. OK, wrong.


--
HTH,
George

2) This is a UNION query. Any employee that appears in both datasets being
UNIONed will have 2 records in the final result. That's just how UNION
queries work. If you want to see those 2 rows combined into one row, then
you need to create a 2nd query that Sums the results of the UNION, grouped
on Name.
Click to expand...

Not necessarily, two records. A UNION Query without the ALL keyword
(UNION ALL) will do the same thing as a SELECT DISTINCT, so if records
from both sources are identical, there will only be one in the
recordset.
 
Back
Top