Queries really can’t do everything…

que-ry [kweer-ee] noun, plural que-ries
verb, que-ried, que-rying

1. an inquiry
2. a computer language used to make queries into databases and information systems
3. A way to view a subset of information from one or more tables in a database
4. Queries return records from one or more tables according to the parameters set in that query

However, *…one or more…* does not mean include every table in your database.  (In the same way, no one table should be used to store all your fields.)  This can slow your forms and/or reports loading time down, more so if your criteria is too *strict*.

Yes, queries are a great way to return subsets of data when used properly.  When planning the your queries, think about the forms *with* subforms (Figure 1), reports *with* subreports where you can show related information.  Don’t think you have to use *one* query with *one* form or *one* query with *one* report.  The beauty of forms (and reports) is SUBforms (Figure 1) and SUBreports.  And with a Tab Control you can really maximize the use of subforms, ie:

Figure 1

Another problem with a query that encompasses so many tables is the JOINs… this has the potential to leave your query UNupdateable making data entry impossible, as well as, affect the speed in which the query returns its results.  In the query below there are only INNER JOINs, which means you will only get results where there is data available across all tables for a Project.  While most times this may produce the desired results there are times when you may want to see all Customers whether they have Projects or not OR all Projects whether they have Treatments or not.  In this case you would have to mix LEFT JOINs and RIGHT JOINs which also has the potential, once again, to leave your recordset UNupdateable.

And lastly, while you are allowed approximately 64,000 characters in an SQL statement  (The below query has 14,385 characters.)… let’s resist the urge to use them all.

Figure 2
SELECT [Project File].[Project No], [Project File].[Item No], Customers.[SWC Cust Code], Customers.[Source of Lead], Customers.Surname, Customers.[First Name], Customers.Title, Customers.Salutation, Customers.[Business Name], Customers.[Address L1], Customers.[Address L2], Customers.[Address L3], Customers.[Address L4], Customers.Postcode, [Project File].[Project Location], [Project File].Room, [Project File].[Room Header], [Project File].Window, [Project File].[Special Product Name?], [Project File].[Special Product Name], [Curtain & Other Product Styles].[Product Name], [Project File].[No of Items], [Project File].Picture, [Project File].[Rail Width cm], [Project File].[Finished Drop], [Project File].[Return Left cm], [Project File].[Return Rt cm], [Project File].[Fabric % Discount to RRP], [Project File].[Non Fabric Mark Up], [Project File].LinX, [Project File].[Commission Off %], 100*(100/(100-[Commission Off %])-1) AS [Commission %], [Project File].[TAX Rate], [Project File].[Supplier ID], [Project File].[Fabric Name], [Project File].[Manfr Code], [Project File].[Fab Width cm], [Project File].[Patt Repeat cm], [Project File].[Min Wastage %], [Project File].[Fab RRP inc TAX], [Curtain & Other Product Styles].[Style Name], [Curtain & Other Product Styles].[Header Type], [Curtain & Other Product Styles].[Overlap cm], [Curtain & Other Product Styles].[Fullness Target], [Curtain & Other Product Styles].[Min Fullness], [Curtain & Other Product Styles].[Allow Fractional Drops], [Curtain & Other Product Styles].[Side Turn cm], [Curtain & Other Product Styles].[Seam Overlap], [Curtain & Other Product Styles].[Header Ht], [Curtain & Other Product Styles].[Hem Ht], Suppliers.[Supplier Name], Suppliers.[SWC Discount %], Linings.[Lining Width cm], Linings.[Lin Cost pm], Interlinings.[InLin Width cm], Interlinings.[InLinCost pm], [Make Up Costing].[Header m], [Make Up Costing].[Hem m], [Make Up Costing].[Side Turn m], [Make Up Costing].[Seam m], [Make Up Costing].[Make Up sqm], [Make Up Costing].[Make Up Swag], [Make Up Costing].[Make Up Drop], [Tracks & Poles].[Track Type], [Project File].[Pole Colour], [Tracks & Poles].[Track Description], [Tracks & Poles].[Cost per metre], [Tracks & Poles].[Unit Cost per Item], [Tie Backs].[Tieback Style], [Tie Backs].[TB Fabric Reqd m/pair], [Tie Backs].[TB Cost per Pair], [Project File].[No of Swags], [Project File].Railroad, [Project File].[Single Curtain], [Project File].[Stack L or R], [Project File].[No of Cushions], Int(([Rail Width cm]*[Fullness Target]+[Return Left cm]+IIf([Product Name]=”Bed Valance”,[Return Left cm],[Return Rt cm])+[Overlap cm])/[Fab Width cm]) AS [Likely Seams], [Rail Width cm]*[Fullness Target]+IIf([Single Curtain]=-1,2,IIf([Product Name]=”Blind”,2,4))*[Side Turn cm]+[Likely Seams]*[Seam Overlap]+IIf([Product Name]=”Bed Valance”,[Return Left cm]*2*[Fullness Target],[Return Left cm]+[Return Rt cm])+[Overlap cm] AS [Width Required], [Width Required]/[Fab Width cm] AS [Nominal Drops], IIf([Rail Width cm]=0,0,IIf(([Fab Width cm]*Int([Nominal Drops])-[Return Left cm]-[Return Rt cm]-[Overlap cm]-4*[Side Turn cm]-[Likely Seams]*[Seam Overlap])/[Rail Width cm]>[Min Fullness],Int([Nominal Drops]),Int([Nominal Drops])+1)) AS [Final Drops], IIf([No of Swags]=0,0,[Rail Width cm]/[No of Swags]+[Overlap cm]*([No of Swags]-1)/[No of Swags]) AS [Swag Width cm], [No of Swags]*(Int([Swag Width cm]/([Fab Width cm]-8))+1) AS [Swag Drops], IIf([No of Swags]=0,0,(110+([Swag Width cm]-60)/10)) AS [Swag Cut Drop B4 PR], IIf([Product Name]=”Bed Valance”,([Final Drops]*[Fab Width cm]-2*[Side Turn cm]-[Seam Overlap]*[Likely Seams])/([Rail Width cm]+2*[Return Left cm]),IIf([Single Curtain]=-1,([Final Drops]*[Fab Width cm]-[Return Left cm]-[Return Rt cm]-2*[Side Turn cm]-[Seam Overlap]*[Likely Seams])/[Rail Width cm],([Final Drops]*[Fab Width cm]-[Return Left cm]-[Return Rt cm]-4*[Side Turn cm]-[Seam Overlap]*[Likely Seams]-[Overlap cm])/[Rail Width cm])) AS [Actual Fullness], [Finished Drop]+[Header Ht]+[Hem Ht] AS [Cut Drop B4 PR], IIf([Patt Repeat cm]=0,[Cut Drop B4 PR],(Int([Cut Drop B4 PR]/([Patt Repeat cm])+1)*([Patt Repeat cm]))) AS [Cut Drop + PR], 0.5*Int(2*([Swag Cut Drop B4 PR]*(IIf([Swag Drops]-Int([Swag Drops])>0,Int([Swag Drops])+1,[Swag Drops])+[Patt Repeat cm])*(1+[Min Wastage %]/100))/100+1) AS [Swag Fabric Required m], (Int(2*(1+[Min Wastage %]/100)*(IIf([Product Name]=”Cushions”,[Cushion Fab Required],IIf([Railroad]=-1,([Rail Width cm]*[Fullness Target]+IIf([Product Name]=”Bed Valance”,2*[Return Left cm]*[Fullness Target],2*[Return Left cm]+2*[Return Rt cm])+2*[Side Turn cm])/100,(([Cut Drop + PR]*[Final Drops])/100)+[TB Fabric Reqd m/pair]))))+1)/2 AS [Non Swag Fabric Required m], ([Rail Width cm]+[Return Left cm]+2*[Seam Overlap])*[Fullness Target]*IIf((2*[No of Cushions]/Int([Fab Width cm]/(([Finished Drop]+[Return Left cm]+2*[Seam Overlap])*[Fullness Target])))-Int(2*[No of Cushions]/Int([Fab Width cm]/(([Finished Drop]+[Return Left cm]+2*[Seam Overlap])*[Fullness Target])))>0,Int(2*[No of Cushions]/Int([Fab Width cm]/(([Finished Drop]+[Return Left cm]+2*[Seam Overlap])*[Fullness Target])))+1,(Int(2*[No of Cushions]/Int([Fab Width cm]/(([Finished Drop]+[Return Left cm]+2*[Seam Overlap])*[Fullness Target])))))/100 AS [Cushion Fab Required], [Project File].[Special Fab Quantity?], [Project File].[Special Fab Metres], IIf([Special Fab Quantity?]=-1,[Special Fab Metres],IIf([Bought in Blind Yes/No]=-1 Or [Other Bought in Item]=-1,0,IIf([Product Name]=”Swags”,[Swag Fabric Required m],[Non Swag Fabric Required m]))) AS [Fabric Required m], [Project File].[Named Fabric from Customer], [Project File].[Special Fab Costing], [Project File].[Special Fab Cost], IIf([Special Fab Costing]=-1,[Special Fab Cost],(IIf([Named Fabric from Customer]=-1,0,([Fabric Required m]*(1-[SWC Discount %]/100)*[Fab RRP inc TAX]/(1+[TAX Rate]/100))))) AS [Fabric Cost], [Project File].[Special Fab Price], IIf([Special Fab Costing]=-1,[Special Fab Price],IIf([Named Fabric from Customer]=-1,0,([Fab RRP inc TAX]/(1+[TAX Rate]/100))*(1-[Fabric % Discount to RRP]/100)*[Fabric Required m])) AS [Fabric Price], Linings.[Lining Name], IIf([Product Name]=”Bed Valance”,([Return Left cm]*[Rail Width cm]/(100*[Lining Width cm])),0) AS [Extra Lining on Bed Valance], IIf([Lining Name]=”None”,0,(IIf([Product Name]=”Swags”,[Swag Drops]*[Swag Cut Drop B4 PR]/100,([Extra Lining on Bed Valance]+IIf([Product Name]=”Cushions”,[No of Cushions],1)*[Final Drops]*[Cut Drop B4 PR]/100)))) AS [Lining Reqd m], [Lining Reqd m]*[Lin Cost pm]*[Non Fabric Mark Up]*[LinX] AS [Lining Price], Interlinings.[InLining Name], IIf([InLining Name]=”None”,0,(IIf([Product Name]=”Swags”,[Swag Drops]*[Swag Cut Drop B4 PR]/100,([Extra Lining on Bed Valance]+IIf([Product Name]=”Cushions”,[No of Cushions],1)*[Final Drops]*[Cut Drop B4 PR]/100)))) AS [InLining Reqd m], IIf([InLining Name]=”None”,0,[InLining Reqd m]*[InLinCost pm]*[Non Fabric Mark Up]*[LinX]) AS [InLining Price], [Header m]*([Final Drops]*[Fab Width cm]-4*[Side Turn cm]-Int([Final Drops]/2-0.5)*2*[Seam Overlap])*[Non Fabric Mark Up]/100 AS [Heading Price], [Hem m]*([Final Drops]*[Fab Width cm]-4*[Side Turn cm]-Int([Final Drops]/2-0.5)*2*[Seam Overlap])*[Non Fabric Mark Up]/100 AS [Hemming Price], (IIf([Product Name]=”Swags”,[Swag Cut Drop B4 PR]*2,[Finished Drop]*(IIf([Single Curtain]=-1,2,4))))*[Side Turn m]*[Non Fabric Mark Up]/100 AS [Side Turn Price], IIf([Product Name]=”Cushions”,(IIf([Return Left cm]>0,4,2)*([Rail Width cm]+[Finished Drop])*[Seam m]),Int([Final Drops]/2-0.5)*2*[Seam m]*(IIf([Product Name]=”Swags”,[Swag Cut Drop B4 PR],[Finished Drop])))*[Non Fabric Mark Up]/100 AS [Seaming Price], IIf([Product Name]=”Cushions”,[Rail Width cm]*[Finished Drop],(IIf([Allow Fractional Drops]=-1,[Rail Width cm]*[Finished Drop],[Final Drops]*[Fab Width cm]*[Finished Drop])))*[Make Up sqm]*[Non Fabric Mark Up]/10000 AS [Square Price], [Make Up Swag]*[No of Swags]*[Non Fabric Mark Up] AS [Swag Price], [Project File].[Special Make Up Costing?], [Project File].[Special Make Up Cost], IIf([Special Make Up Costing?]=-1,[Special Make Up Cost]*[Non Fabric Mark Up],(IIf([Product Name]=”Cushions”,[No of Cushions]*([Heading Price]+[Hemming Price]+[Side Turn Price]+[Seaming Price]+[Square Price]),[Square Price]+[Heading Price]+[Hemming Price]+[Side Turn Price]+[Seaming Price]+[Swag Price]+([Make Up Drop]*[Final Drops]*[Non Fabric Mark Up])))) AS [Make Up Price], [Project File].[B&T Top], [Project File].[B&T Bottom], [Project File].[B&T Left Side], [Project File].[B&T Right Side], [Project File].[B&T Leading Edges], [Project File].[Supplier Border Materials ID], [Project File].[Border Fabric Name], [Project File].[Border Manfr Code], [Project File].[Border Fab Width cm], [Project File].[Border RRP inc TAX], [Border & Trim Styles].[Border Type], [Border & Trim Styles].[Border Style], [Project File].[Named Border Fabric from Customer], [Project File].[Borders Fabric Required m], IIf([B&T Top]=-1,([Final Drops]*[Fab Width cm]-4*[Side Turn cm]-2*[Seam Overlap])/100,0)+(IIf([B&T Bottom]=-1,((IIf([Product Name]=”Swags”,[Swag Drops],[Final Drops]))*[Fab Width cm]-4*[Side Turn cm]-2*[Seam Overlap])/100,0))+(IIf([B&T Left Side]=-1,[Finished Drop]/100,0))+(IIf([B&T Right Side]=-1,[Finished Drop]/100,0))+IIf([B&T Leading Edges]=-1,(IIf([Product Name]=”Tails”,(((([Rail Width cm]*[Fullness Target]/2)^2+([Finished Drop])^2)^0.5)*0.0235),[Finished Drop]/50)),0) AS [Border Lengths m], IIf([Product Name]=”Cushions”,IIf([B&T Top]=-1,2*([Rail Width cm]+[Finished Drop])*(IIf([Cushion double Edging]=-1,2,1))*[No of Cushions]/100,0),0) AS [Cushion Border Length], [Border & Trim Styles].[Make Up Cost per m], IIf([Product Name]=”Cushions”,[Cushion Border Length],[Border Lengths m])*[Make Up Cost per m]*[Non Fabric Mark Up] AS [Border Make Up Price], [Border & Trim Styles].[Border Height Top cm], [Border & Trim Styles].[Border Height Bottom cm], [Border & Trim Styles].[Border Width Side cm], [Border & Trim Styles].[Border Width Leading Edge cm], [Border & Trim Styles].[Border Turn Allowance cm], [Suppliers – Border Materials].[Border Manfr Name], [Suppliers – Border Materials].[SWC Discount on Borders], Round([Border RRP inc TAX]/(1+[TAX Rate]/100),2) AS [Border RRP exc TAX], IIf([B&T Top]+[B&T Bottom]+[B&T Left Side]+[B&T Right Side]+[B&T Leading Edges]=0,0,1) AS [No Borders], IIf([B&T Top]+[B&T Bottom]+[B&T Left Side]+[B&T Right Side]+[B&T Leading Edges]=0,””,(“Borders/Trims: ” & [Border Type] & ” ” & [Border Style] & ” on ” & (IIf([B&T Top]=-1,”Top Edge” & IIf([Border Height Top cm]>1,”(” & [Border Height Top cm] & ” cm),  “,”,”))) & (IIf([B&T Bottom]=-1,” Bottom Edge” & IIf([Border Height Bottom cm]>1,”(” & [Border Height Bottom cm] & ” cm), “,”,”))) & (IIf([B&T Left Side]=-1,” Left Edge” & IIf([Border Width Side cm]>1,”(” & [Border Width Side cm] & ” cm), “,”,”))) & (IIf([B&T Right Side]=-1,” Right Edge” & IIf([Border Width Side cm]>1,”(” & [Border Width Side cm] & ” cm), “,”,”))) & (IIf([B&T Leading Edges]=-1,” Leading Edges” & IIf([Border Width Leading Edge cm]>1,”(” & [Border Width Leading Edge cm] & ” cm) “,”,”))))) AS [Borders Function], IIf([B&T Top]=0,””,([Border Type] & ” ” & [Border Style] & (IIf([Cushion Double Edging]=-1,” around Top and Bottom Edges “,” on Edges “)))) AS [Cushion Edge Function], [Border & Trim Styles].[Border Fullness], [Curtain & Other Product Styles].[Cushion Double Edging], [Project File].[Special Border Fab Costing], [Project File].[Special Border Fab Cost], [Project File].[Special Border Fab Price], IIf([Special Border Fab Costing]=-1,[Special Border Fab Cost],IIf([Named Border Fabric from Customer]=-1,0,[Borders Fabric Required m]*[Border RRP inc TAX]/(1+[TAX Rate]/100)*(1-[SWC Discount on Borders]/100))) AS [Border Fabric Cost], IIf([Special Border Fab Costing]=-1,[Special Border Fab Price],IIf([Named Border Fabric from Customer]=-1,0,nz([Borders Fabric Required m]*([Border RRP inc TAX]/(1+[TAX Rate]/100))*(1-[Fabric % Discount to RRP]/100),0))) AS [Border Fabric Price], [Tracks & Poles].[Fitting Cost], [Project File].[Special Track?], [Project File].[Special Track Name], [Project File].[Special Track Cost], IIf([Special Track?]=-1,[Special Track Cost],([Unit Cost per Item]+[Cost per metre]*[Rail Width cm]/100+[Fitting Cost]))*[Non Fabric Mark Up] AS [Track & Pole Price], [TB Cost per pair]*[Non Fabric Mark Up] AS [Tie Back Price], [Lining Price]+[InLining Price]+[Make Up Price]+[Track & Pole Price]+[Tie Back Price]+[Border Make Up Price]+[Hanging Price]+([Bought in Blind COST]+[Other Bought in Item COST])*[Non Fabric Mark Up] AS [Window Price exc Fabric & TAX], [Window Price exc Fabric & TAX]+[Fabric Price]+[Border Fabric Price] AS [Window Price inc Fabric ex TAX], [Window Price inc Fabric ex TAX]*[TAX Rate]/100 AS [Window Price TAX], [Window Price inc Fabric ex TAX]+[Window Price TAX] AS [Window Price inc TAX], [Project File].[Quotation Notes], [Project File].[Extra Deposit Costs], [Project File].[Worksheet Notes], [Project File].[Hanging Cost per Item], [Hanging Cost per Item]*[Non Fabric Mark Up] AS [Hanging Price], [Project File].[Measurements Verified], [Project File].[Bought in Blind Yes/No], [Project File].[Bought in Blind COST], [Project File].[Other Bought in Item], [Project File].[Other Bought in Item COST]

FROM [Suppliers – Border Materials] INNER JOIN (([Make Up Costing] INNER JOIN ([Curtain & Other Product Styles] INNER JOIN ([Border & Trim Styles] INNER JOIN ([Tie Backs] INNER JOIN ([Tracks & Poles] INNER JOIN (Customers INNER JOIN (Linings INNER JOIN (Interlinings INNER JOIN [Project File] ON Interlinings.ID = [Project File].[Interlining ID]) ON Linings.ID = [Project File].[Lining ID]) ON Customers.ID = [Project File].[Customer ID]) ON [Tracks & Poles].ID = [Project File].[Track Pole ID]) ON [Tie Backs].ID = [Project File].[Tie Back ID]) ON [Border & Trim Styles].ID = [Project File].[Border & Trim Styles ID]) ON [Curtain & Other Product Styles].ID = [Project File].[Style ID]) ON [Make Up Costing].ID = [Curtain & Other Product Styles].[Make up Costing ID]) INNER JOIN Suppliers ON [Project File].[Supplier ID] = Suppliers.ID) ON [Suppliers – Border Materials].ID = [Project File].[Supplier Border Materials ID]

WHERE ((([Project File].[Project No])=[Input Project Number]))

ORDER BY [Project File].[Item No], [Project File].Room, [Project File].Window;

 872 total views,  1 views today