Access Queries

Google+ Pinterest LinkedIn Tumblr +

1) Phone Pattern UPDATE tablenameSet newphone = left ([phone], 3) & “-” &  mid ([phone], 4, 3) & “-” &  right ([phone], 4);to Phone Pattern

2) Phone PatternUPDATE tablenameSet newphone = “(” &  left ([phone], 3) & “)”  & ” ”  &  mid ([phone], 4, 3) & “-” &  right ([phone], 4); 

3) to delete the field

update sheet1set newphone =” ”

4) to Create a New Tablecreate table Anand(Serial varchar, fname varchar, lname varchar, company varchar, Address varchar, city varchar, State varchar, Zip varchar, phone varchar, fax varchar, Web varchar, Email varchar)

5) to Club the Filesinsert into tablenameselect *from tablename;

6) to Update Verification Resultupdate tablenameset verificationdate = “10/30/2008 3:14:04 AM”where “tablename email <>”””

7) to Make PropercaseUpdate TablenameSet firstname  =  strconv (firstname,3);

8) to Filter the Records

select *from Tablenamewhere firstname like “*john*” and state like “*NY*”;

9) to take Multiple Company countSELECT (companyname), count(*)FROM TablenameGROUP BY companyname;

10)  = Equal  <> Not equal  > Greater than  < Less than  >= Greater than or equal  <= Less than or equal   BETWEEN : Between an inclusive range  LIKE : Search for a pattern  IN : If you know the exact value you want to return for at least one of the columns 

11) to pull out related datas from a tableSELECT * FROM TablenameWHERE City=’Dallas’AND State=’TX’AND Contactname=’David’AND Title=’President’

12) to pull out related datas from a tableSELECT * FROM TablenameWHERE contactname=’Bob’OR  contactname=’Ola’

SELECT * FROM Tablename WHEREFirstName=’Robert’AND (LastName=’Wale’ OR LastName=’Ola’)

13) to arrange the database orderly

SELECT * FROM TablenameORDER BY FirstName

14) to Update Address While CleansingUPDATE TablenameSET Address=’11 Washington Ave’, City=’New york’

15) to Update Address While CleansingUpdate TablenameSet Address=’947 PARK AVE NW’, City=’Springfield’, State=’NY’Where Companyname= ‘1ST FLOOR DESIGNS’

16) to Delete Particular thingDELETE FROM TablenameWHERE LastName=’Tjessem’ AND FirstName=’Jakob’

17) ConsistencySELECT * FROM TablenameWHERE Email LIKE ‘*/*’

18) WildCards% Wildcard

SELECT * FROM TablenameWHERE City LIKE ‘sa%’ 

19) IN Command

SELECT * FROM AnandWHERE LastName IN (‘Davis’,’Peterson’) 

20) To Take Company Count

SELECT ([companyname]), count(*)FROM Oct30_5474_M17_ClubbedGROUP BY [companyname];

Share.

About Author

Leave A Reply