In this chapter, you explored how to use Access Projects to work with SQL Server databases. SQL Server is a very powerful enterprise database tool that should be used for more sophisticated applications. You can start with a free version of SQL Server called SQL Server Desktop Engine if you want to write your Access application using SQL Server to begin with. If you do so, you will not have to make extensive changes at a later date when your application has become successful and must be migrated to a more


QJ&TE VIEW ' AlphabBtitaJ L:st d Products AS

SELECT" Prod ucts.31. Qiegor es . Citegc-ryNs re AS Eip rlOO 1

FROM Qtegor'es JNNR JCfiN Products ON (Qiiegor'es.QitegcryiD= Produds.QtegoryiD} Vh'H ERE ((i Pic-ducts, D'sconf nued) =0))

Query Name Product Sdes fa: 1397 Ups zed us:ng SQL.

SELECT" Csiegcr =s, Ca tegoryNs p"-e, Frod ucts, Fro-du (tun ueit ( rncney," ürde r

Döta' s'. U n iFr (e nfi Vi 1 - D'sccuntj/100 )*lO0) A5 F reductSi es. Qtr f ditep;rtiq,Sh pcedbite) AS Si^pedQiH^t!

ROM (Gtegores :NNR JCHN Products ON [Csi=g-zres.Qtegc*iflD= Froducts.CstegoryiDJ) J-JNER JOIN [Orders ; N N ER JOIN 'Order DetaV ON (Ordere.OrcterID= Order Cets' s'.OrderlD)} ON [Preducts,Product:^ Order Cets s , Prod uciJ D} WH ERE (([Orde is. Sh pped De te) Between 1 / l,: L557 An d 12/ 31/1997'»

GROUP Bv ütegor es . CStegoryNe re. Products. Prod uctNsrce. Qtr + dste pa rtiq.Sh peed Este)

Quety Name Current Produkt List Ups zed us'ng SQL


AS RETURN (5B.ECT TOP IOC- PERCENT Picduet Lst'.Producta. Product Lst'.FrcductNir-e ROM Products AS Frc-dud Lst WH ERE (¡('Pio-duct Lst ,Dscjifnued}=0)} OR DR BV Prc-duct Lst', Fred uetNa me}

Query Name distorters a rd iupp ers b/Cty Ups zed us'ng SQL

Figure 9.37

CREATE FROCffiURE Customers end Supp ers ty Sty' AS

SELECT Cty. CtirpinyNaree. CtTisc:Nar?e. Custor-ers AS Re st'onsn'p

ROM Qjst orders UNiON SELECT öty. C&mpanyTferve, Ctntadltre. Suppiera' AS _Si4jp"eis_ ROM Suppers

ORDR EV Cty. Oj^-partyNeRe_

powerful database than Access. Access Projects allow you to create client/server applications with Access as the front end and SQL Server as the database. Access includes an Upsizing Wizard to help you transition an existing Access database to SQL Server. If you want more information on using Access Projects to work with SQL Server databases, please consult the comprehensive case study in Chapter 13.

Qu e ry N am e Sales by Year

Faied iE" LDsize. Attempted td use SQ.:

CREATE FUNCTION "Safes by Ysr' ;#Form s_Sa es_b y_Year_Di al og_datetirn e

. ®Fornns_Sai es_by_Y eai _D lalog_datetiniie


AS RETURN ¡SElECT Orders.ShippedDate, Orders.CrderlD. Crdff Subtotals'.Subtotal, frrmatiShippedDote.'vyyy} AS Year

FROM Orders INNER JOIN Order Subtotals Ol raders.Crde-ID= Orda- Subtotals".Ords ID} WHERE )

QueryNane Sales Toteiis by ount

This query is not upsiaeable because it is a DISTINCT ROW quffy.

CREATE FUNCTION 'Sales by Y ear [email protected] s_Sai es_by_Year_Di ai og_datet ¡m e


AS RETURN [SELECT Orda"s.Sh:ppedDate, Orders .CrderlD. Orda' Subtotals ".Subtotal, fcrmatiShippedDctE.'yyyV) AS Year

FROM Crders INNER JOIN Order Subtotals' ON [Orders. CrdaID = Orda- Subtotals'OrdalD) WHERE )

0 0

Post a comment