sorta Access/sorta TSQL?

[John G]

I built an ASP.NET page for an access DB I created. I have a query that does
exactly what I need it to do in Access but when I try and use it to populate
a datagrid I get a parse error at the '<' symbol. Could anyone perhaps point
out where I may have gone astray? This is the working Access query:
PARAMETERS MyWorkDay DateTime;
SELECT tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last] AS
TechName,
Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00')) AS TotalHours
FROM tblTechs LEFT JOIN tblStages ON tblTechs.VDash = tblStages.Tech
WHERE (((tblTechs.Active)=True) AND
((IIf(Format([Stop],'mm/dd/yy')<>'',Format([Stop],'mm/dd/yy'),Format([MyWorkDay],'mm/dd/yy')))=Format([MyWorkDay],'mm/dd/yy')))
GROUP BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last]
HAVING
(((Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00')))=0))
ORDER BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last];

I of course drop the PARAMETER statement in the query builder in visual
studio as I pull that from a calendar control in my asp page. Essentially
what I need this to do (and it does in Access) is give me a list of all the
Techs who didn't enter hours for whichever date I specify. outputting:

Team TechName TotalHours
NULL John Doe 0
NULL Jane Doe 0

TIA,
John Hem

 

[Tom Ellison]

Dear John:

There are features common to the Access Jet database that are going to give
errors if you submit the same query to SQL Server.

The PARAMETERS construct is one such feature. SQL Server (or MSDE) will not
prompt for parameters. You may need to prompt from within the application
and then construct the SQL string to include the user input. Your post
indicates you have done something like this already.

You have used Access functions, such as IIf, Format and DateDiff. You may
need to find equivalent SQL Server/MSDE functions and rewrite these
accordingly.

My recommendation is to build this query a piece at a time using Query
Analyzer and test it there, adding complexity as you go. If you do not have
QA, it is available in SQL Server Developer Edition for little cost. If you
are not familiar with this, please take my recommendation seriously.

Tom Ellison

 

[John G]

Thank you Tom for your response. I have modified my query using QA to return
everything but what I need, which is the set of records for techs that have
not entered thier time in.

SELECT tblStages.Team, tblTechs.[First] & ' ' & tblTechs.[Last] AS
TechName, SUM(Val(Format(DateDiff('n', tblStages.Start, tblStages.Stop) / 60,
'Fixed')))
AS Total
FROM (tblTechs LEFT OUTER JOIN
tblStages ON tblTechs.VDash = tblStages.Tech)
WHERE (Format(tblStages.Stop, 'MM/dd/yy') = Format(?, 'MM/dd/yy'))
GROUP BY tblTechs.[First] & ' ' & tblTechs.[Last], tblStages.Team
ORDER BY tblStages.Team, tblTechs.[First] & ' ' & tblTechs.[Last]

What I need is to list all of the techs in tblTechs that do not have
corresponding records in tblStages for a particular date (the '?' in my where
statement provides the parameter for this date). Before, in Access, as you
notice below I used IIF in access to test for an empty string and changed it
to the parameter date if one was there. I understand that with SQL I need to
use either CASE or perhaps SWITCH but all the variations I have attempted
have been unsuccessful. Perhaps my syntax is incorrect or simply illogical.
At any rate, if you could perhaps point me in the right direction I would
greatly appreciate it. I'm sure this is simply a matter of modifying the
WHERE statment to test for a zero leng string (ZLS) as NULL has not produced
any results.

Thank you

Dear John:

There are features common to the Access Jet database that are going to give
errors if you submit the same query to SQL Server.

The PARAMETERS construct is one such feature. SQL Server (or MSDE) will not
prompt for parameters. You may need to prompt from within the application
and then construct the SQL string to include the user input. Your post
indicates you have done something like this already.

You have used Access functions, such as IIf, Format and DateDiff. You may
need to find equivalent SQL Server/MSDE functions and rewrite these
accordingly.

My recommendation is to build this query a piece at a time using Query
Analyzer and test it there, adding complexity as you go. If you do not have
QA, it is available in SQL Server Developer Edition for little cost. If you
are not familiar with this, please take my recommendation seriously.

Tom Ellison

I built an ASP.NET page for an access DB I created. I have a query that
does
exactly what I need it to do in Access but when I try and use it to
populate
a datagrid I get a parse error at the '<' symbol. Could anyone perhaps
point
out where I may have gone astray? This is the working Access query:
PARAMETERS MyWorkDay DateTime;
SELECT tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last] AS
TechName,
Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00'))
AS TotalHours
FROM tblTechs LEFT JOIN tblStages ON tblTechs.VDash = tblStages.Tech
WHERE (((tblTechs.Active)=True) AND
((IIf(Format([Stop],'mm/dd/yy')<>'',Format([Stop],'mm/dd/yy'),Format([MyWorkDay],'mm/dd/yy')))=Format([MyWorkDay],'mm/dd/yy')))
GROUP BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last]
HAVING
(((Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00')))=0))
ORDER BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last];

I of course drop the PARAMETER statement in the query builder in visual
studio as I pull that from a calendar control in my asp page. Essentially
what I need this to do (and it does in Access) is give me a list of all
the
Techs who didn't enter hours for whichever date I specify. outputting:

Team TechName TotalHours
NULL John Doe 0
NULL Jane Doe 0

TIA,
John Hem

 

[Tom Ellison]

Dear John:

Looks like significant progress!

Now, if you want to see all the stages not recorded for each tech, you may
want to start with the cross product of all techs with all stages. For this
to work, you would need to have a table containing one row for each possible
stage. Then, you can filter out all those rows that do exist in tblStages.

Assuming you have a table of stages called tblStageList, the query would
build like this:

SELECT *
FROM tblTechs T, tblStageList SL
LEFT OUTER JOIN tblStages S ON S.Stage = SL.Stage AND S.Tech = T.Tech
WHERE S.Stage IS NULL

I do not know on which column(s) these tables are actually related. I have
just made guesses on how this might be done. Substitute your actual column
name(s) above.

How does this work? The cross product:

FROM tblTechs T, tblStageList SL

produces all possible combinations of every Tech with every listed Stage.

The LEFT OUTER JOIN then preserves all these combinations along with each
recorded stage for each tech. Whenever a stage is not present for a tech,
all the columns returned from tblStages for that row will be NULL. Of
course, you could have some column in this table that could be NULL some of
the time. But, the column on which you have JOINed would not be null,
unless no row was found. This is how it does the job of showing "missing
stages" for each tech.

To this, you can easily add the other features you need.

Tom Ellison

Thank you Tom for your response. I have modified my query using QA to
return
everything but what I need, which is the set of records for techs that
have
not entered thier time in.

SELECT tblStages.Team, tblTechs.[First] & ' ' & tblTechs.[Last] AS
TechName, SUM(Val(Format(DateDiff('n', tblStages.Start, tblStages.Stop) /
60,
'Fixed')))
AS Total
FROM (tblTechs LEFT OUTER JOIN
tblStages ON tblTechs.VDash = tblStages.Tech)
WHERE (Format(tblStages.Stop, 'MM/dd/yy') = Format(?, 'MM/dd/yy'))
GROUP BY tblTechs.[First] & ' ' & tblTechs.[Last], tblStages.Team
ORDER BY tblStages.Team, tblTechs.[First] & ' ' & tblTechs.[Last]

What I need is to list all of the techs in tblTechs that do not have
corresponding records in tblStages for a particular date (the '?' in my
where
statement provides the parameter for this date). Before, in Access, as you
notice below I used IIF in access to test for an empty string and changed
it
to the parameter date if one was there. I understand that with SQL I need
to
use either CASE or perhaps SWITCH but all the variations I have attempted
have been unsuccessful. Perhaps my syntax is incorrect or simply
illogical.
At any rate, if you could perhaps point me in the right direction I would
greatly appreciate it. I'm sure this is simply a matter of modifying the
WHERE statment to test for a zero leng string (ZLS) as NULL has not
produced
any results.

Thank you

Dear John:

There are features common to the Access Jet database that are going to
give
errors if you submit the same query to SQL Server.

The PARAMETERS construct is one such feature. SQL Server (or MSDE) will
not
prompt for parameters. You may need to prompt from within the
application
and then construct the SQL string to include the user input. Your post
indicates you have done something like this already.

You have used Access functions, such as IIf, Format and DateDiff. You
may
need to find equivalent SQL Server/MSDE functions and rewrite these
accordingly.

My recommendation is to build this query a piece at a time using Query
Analyzer and test it there, adding complexity as you go. If you do not
have
QA, it is available in SQL Server Developer Edition for little cost. If
you
are not familiar with this, please take my recommendation seriously.

Tom Ellison

I built an ASP.NET page for an access DB I created. I have a query that
does
exactly what I need it to do in Access but when I try and use it to
populate
a datagrid I get a parse error at the '<' symbol. Could anyone perhaps
point
out where I may have gone astray? This is the working Access query:
PARAMETERS MyWorkDay DateTime;
SELECT tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last] AS
TechName,
Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00'))
AS TotalHours
FROM tblTechs LEFT JOIN tblStages ON tblTechs.VDash = tblStages.Tech
WHERE (((tblTechs.Active)=True) AND
((IIf(Format([Stop],'mm/dd/yy')<>'',Format([Stop],'mm/dd/yy'),Format([MyWorkDay],'mm/dd/yy')))=Format([MyWorkDay],'mm/dd/yy')))
GROUP BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last]
HAVING
(((Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00')))=0))
ORDER BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last];

I of course drop the PARAMETER statement in the query builder in visual
studio as I pull that from a calendar control in my asp page.
Essentially
what I need this to do (and it does in Access) is give me a list of all
the
Techs who didn't enter hours for whichever date I specify. outputting:

Team TechName TotalHours
NULL John Doe 0
NULL Jane Doe 0

TIA,
John Hem

 

[John G]

Thanks for pointing me in the right direction Tom. Been out for a few days
and below is what I put together that produced what I was looking for.

SELECT T.Team, tblTechs.[First] & ' ' & tblTechs.[Last] AS TechName,
SUM(Val(Format(DateDiff('n', T.Start, T.Stop) / 60, 'Fixed'))) AS Total
FROM (tblTechs LEFT OUTER JOIN
(SELECT ID, Ticket, Team, Stage, Tech, Start,
Stop, Notes
FROM tblStages
WHERE (Format(Stop, 'MM/dd/yy') = Format(?,
'MM/dd/yy'))) AS T ON tblTechs.VDash = T.Tech)
GROUP BY T.Team, tblTechs.[First] & ' ' & tblTechs.[Last]
HAVING (SUM(Val(Format(DateDiff('n', T.Start, T.Stop) / 60, 'Fixed')))
= 0)

There's probably a way to clean it up but it works for me...thanks again!

Dear John:

Looks like significant progress!

Now, if you want to see all the stages not recorded for each tech, you may
want to start with the cross product of all techs with all stages. For this
to work, you would need to have a table containing one row for each possible
stage. Then, you can filter out all those rows that do exist in tblStages.

Assuming you have a table of stages called tblStageList, the query would
build like this:

SELECT *
FROM tblTechs T, tblStageList SL
LEFT OUTER JOIN tblStages S ON S.Stage = SL.Stage AND S.Tech = T.Tech
WHERE S.Stage IS NULL

I do not know on which column(s) these tables are actually related. I have
just made guesses on how this might be done. Substitute your actual column
name(s) above.

How does this work? The cross product:

FROM tblTechs T, tblStageList SL

produces all possible combinations of every Tech with every listed Stage.

The LEFT OUTER JOIN then preserves all these combinations along with each
recorded stage for each tech. Whenever a stage is not present for a tech,
all the columns returned from tblStages for that row will be NULL. Of
course, you could have some column in this table that could be NULL some of
the time. But, the column on which you have JOINed would not be null,
unless no row was found. This is how it does the job of showing "missing
stages" for each tech.

To this, you can easily add the other features you need.

Tom Ellison

Thank you Tom for your response. I have modified my query using QA to
return
everything but what I need, which is the set of records for techs that
have
not entered thier time in.

SELECT tblStages.Team, tblTechs.[First] & ' ' & tblTechs.[Last] AS
TechName, SUM(Val(Format(DateDiff('n', tblStages.Start, tblStages.Stop) /
60,
'Fixed')))
AS Total
FROM (tblTechs LEFT OUTER JOIN
tblStages ON tblTechs.VDash = tblStages.Tech)
WHERE (Format(tblStages.Stop, 'MM/dd/yy') = Format(?, 'MM/dd/yy'))
GROUP BY tblTechs.[First] & ' ' & tblTechs.[Last], tblStages.Team
ORDER BY tblStages.Team, tblTechs.[First] & ' ' & tblTechs.[Last]

What I need is to list all of the techs in tblTechs that do not have
corresponding records in tblStages for a particular date (the '?' in my
where
statement provides the parameter for this date). Before, in Access, as you
notice below I used IIF in access to test for an empty string and changed
it
to the parameter date if one was there. I understand that with SQL I need
to
use either CASE or perhaps SWITCH but all the variations I have attempted
have been unsuccessful. Perhaps my syntax is incorrect or simply
illogical.
At any rate, if you could perhaps point me in the right direction I would
greatly appreciate it. I'm sure this is simply a matter of modifying the
WHERE statment to test for a zero leng string (ZLS) as NULL has not
produced
any results.

Thank you

Dear John:

There are features common to the Access Jet database that are going to
give
errors if you submit the same query to SQL Server.

The PARAMETERS construct is one such feature. SQL Server (or MSDE) will
not
prompt for parameters. You may need to prompt from within the
application
and then construct the SQL string to include the user input. Your post
indicates you have done something like this already.

You have used Access functions, such as IIf, Format and DateDiff. You
may
need to find equivalent SQL Server/MSDE functions and rewrite these
accordingly.

My recommendation is to build this query a piece at a time using Query
Analyzer and test it there, adding complexity as you go. If you do not
have
QA, it is available in SQL Server Developer Edition for little cost. If
you
are not familiar with this, please take my recommendation seriously.

Tom Ellison


I built an ASP.NET page for an access DB I created. I have a query that
does
exactly what I need it to do in Access but when I try and use it to
populate
a datagrid I get a parse error at the '<' symbol. Could anyone perhaps
point
out where I may have gone astray? This is the working Access query:
PARAMETERS MyWorkDay DateTime;
SELECT tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last] AS
TechName,
Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00'))
AS TotalHours
FROM tblTechs LEFT JOIN tblStages ON tblTechs.VDash = tblStages.Tech
WHERE (((tblTechs.Active)=True) AND
((IIf(Format([Stop],'mm/dd/yy')<>'',Format([Stop],'mm/dd/yy'),Format([MyWorkDay],'mm/dd/yy')))=Format([MyWorkDay],'mm/dd/yy')))
GROUP BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last]
HAVING
(((Sum(IIf(Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed')<>'',Format(((DateDiff('n',[Start],[Stop])/60)),'Fixed'),'0.00')))=0))
ORDER BY tblStages.Team, [tblTechs].[First] & ' ' & [tblTechs].[Last];

I of course drop the PARAMETER statement in the query builder in visual
studio as I pull that from a calendar control in my asp page.
Essentially
what I need this to do (and it does in Access) is give me a list of all
the
Techs who didn't enter hours for whichever date I specify. outputting:

Team TechName TotalHours
NULL John Doe 0
NULL Jane Doe 0

TIA,
John Hem