Access to SQL Query...? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Access to SQL Query...?

Author

Topic

Firemaple
Starting Member

14 Posts

Posted - 2007-06-28 : 15:17:47

I have a query in access that is very long and includes several imbedded queries(below, may not be helpful). I am converting this DB into a SQL DB, but this query has me stumped. I don't see a way to imbed queries like Access does. I am fairly familiar with Stored Procedures, but what i can tell, you can't use them in the same manner. Any suggestions would be great!

Thanks

------------QUERY--------
mployees.[USW Number], QryHDEmployees.[Analyst First Name], QryHDEmployees.[Analyst Last Name], QryHDEmployees.Supervisor, QryATSReport.Date, QryATSReport.[USW Num], QryATSReport.MinuteVarianceStored, QryProdReport.Open, QryProdReport.Closed, QryProdReport.Escalated, QryAUXReport.StaffTime, QryAUXReport.AUXTime, QryAUXReport.AUX0, QryAUXReport.TrainingTime, QryAUXReport.MeetingTime, QryAUXReport.ProjectTime, QryAUXReport.AUX8, QryAUXReport.AUX9, QryAHTReport.TalkTime, QryAHTReport.WorkTime, QryAHTReport.TotalCalls, QryQAReport.RecordCount, QryQAReport.qaScore, QryCustSatReport.RecordCount, QryCustSatReport.csScore, Nz([RtnTckCount],0) AS RtnTckCnt
FROM ((((((QryHDEmployees LEFT JOIN QryAHTReport ON (QryHDEmployees.[USW Number] = QryAHTReport.HTUserName) AND (QryHDEmployees.AllDates = QryAHTReport.HTEffDate)) LEFT JOIN QryProdReport ON (QryHDEmployees.[USW Number] = QryProdReport.ProdUserName) AND (QryHDEmployees.AllDates = QryProdReport.ProdDate)) LEFT JOIN QryAUXReport ON (QryHDEmployees.[USW Number] = QryAUXReport.AUXUserName) AND (QryHDEmployees.AllDates = QryAUXReport.AUXEffDate)) LEFT JOIN QryQAReport ON (QryHDEmployees.[USW Number] = QryQAReport.AgentID) AND (QryHDEmployees.AllDates = QryQAReport.AllDates)) LEFT JOIN QryATSReport ON (QryHDEmployees.[USW Number] = QryATSReport.[USW Num]) AND (QryHDEmployees.AllDates = QryATSReport.Date)) LEFT JOIN QryReturnedTickets ON (QryHDEmployees.[USW Number] = QryReturnedTickets.[USW Num]) AND (QryHDEmployees.AllDates = QryReturnedTickets.Date)) LEFT JOIN QryCustSatReport ON (QryHDEmployees.[USW Number] = QryCustSatReport.[USW Num]) AND (QryHDEmployees.AllDates = QryCustSatReport.AllDates)
WHERE (((QryHDEmployees.AllDates) Between [Forms]![frmScoreCard]![txtStartDate] And [Forms]![frmScoreCard]![txtEndDate]))
ORDER BY QryHDEmployees.[Analyst Last Name];
-----------------------------

pootle_flump

1064 Posts

Posted - 2007-06-29 : 05:33:12

The only thing with access is it adds shed loads of redundent (in standard SQL terms) of brackets into the from clause (and elsewhere for that matter). Remove them all from the from clause. nz equivelent is COALESCE. You will have to remove the where clause too.

HTH

Firemaple
Starting Member

14 Posts

Posted - 2007-06-29 : 17:41:43

thanks for the response... However I'm not sure I understand... my problem is that within the query above it contains embedded queries, that are defined separately. i.e. "QryHDEmployees" is a separate query as follows:

------------
SELECT tblAnalyst.[USW Number], tblAnalyst.[Analyst First Name], tblAnalyst.[Analyst Last Name], tblAnalyst.Supervisor, tblAnalyst.Supervisor_USW, TblDates.AllDates
FROM TblDates, tblAnalyst
WHERE (((tblAnalyst.Position)="HDL1") AND ((tblAnalyst.DEPT)="OKCHELPDESK"))
ORDER BY tblAnalyst.[Analyst Last Name], TblDates.AllDates;
-------------

pootle_flump

1064 Posts

Posted - 2007-06-30 : 07:24:54

Just do exactly what you did in access. BTW - saved queries in access are called views in SQL Server (and standard SQL for that matter).

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-07-01 : 04:55:40

quote:
Originally posted by pootle_flump

Just do exactly what you did in access. BTW - saved queries in access are called views in SQL Server (and standard SQL for that matter).

But apparently they can have an ORDER BY in Acceess, thats not allowed in SQL views.

quote:

SELECT tblAnalyst.[USW Number], tblAnalyst.[Analyst First Name], tblAnalyst.[Analyst Last Name]
      ,tblAnalyst.Supervisor, tblAnalyst.Supervisor_USW, TblDates.AllDates
FROM TblDates, tblAnalyst
WHERE (((tblAnalyst.Position)="HDL1") AND ((tblAnalyst.DEPT)="OKCHELPDESK"))
ORDER BY tblAnalyst.[Analyst Last Name], TblDates.AllDates;

Create it in SQL like this:

CREATE VIEW QryHDEmployees AS
SELECT tblAnalyst.[USW Number], tblAnalyst.[Analyst First Name], tblAnalyst.[Analyst Last Name]
      ,tblAnalyst.Supervisor, tblAnalyst.Supervisor_USW, TblDates.AllDates
FROM tblAnalyst
CROSS JOIN TblDates
WHERE (((tblAnalyst.Position)='HDL1') AND ((tblAnalyst.DEPT)='OKCHELPDESK'))

(and yes, the old style implicite joins also works in SQL server, but they hurt my eyes)

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

pootle_flump

1064 Posts

Posted - 2007-07-01 : 14:31:06

quote:
Originally posted by PSamsig
But apparently they can have an ORDER BY in Acceess, thats not allowed in SQL views.

Only since 2005. SQL Server Views and Access queries have differences just like T-SQL and Access SQL but for most purposes they can be considered analogous.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources