Wednesday, December 30, 2009
Monday, December 7, 2009
Saturday, November 28, 2009
Wednesday, November 25, 2009
Tuesday, November 24, 2009
Monday, November 23, 2009
Lesson 6: Data handling task : Workbook 2
Catering Text Box
Copy and paste the following text in note pad and save it as catering .
"Title","FirstName","LastName","ContractID","Address1","Address2","PostCode","TelNo","Event","Date","Time","Numbers","PricePerPerson","Paid","OptExtras","CostExtras"
"Miss","Joan","Baxendale","BU233","22 Bolton Road","Whitehall","ZK11 8RE","0161 444 67543","Buffet",12/12/2003,12:30:00,30,10.25,0,"D",3.5
"Mr","Simon","Holmes","BU355","89 Holden Road","Avondale","ZK1 7TY","0161 332 10298","Buffet",07/11/2003,13:30:00,25,12.55,0,"W",25
"Mr","Nazir","Washid","DO344","51 Ashton Avenue","Avondale","ZK12 9QW","0161 333 58493","Dinner",06/06/2004,21:00:00,8,15,1,"GT",20
"Mr","Bob","Forrest","LO334","77 Everton Road","Sett","ZK4 11DK","0161 441 67453","Lunch",20/12/2003,13:00:00,10,12.5,1,,
"Mrs","Jasmine","Patel","BU323","21 Peel Street","Duckworth","ZK6 5JK","0161 418 76543","Buffet",18/05/2003,22:00:00,33,10.25,0,"WT",30
"Mr","Lee","Tseng","LO336","7 Pole Lane","Sett","ZK4 2UY","0161 412 78965","Lunch",7/11/2003,13:30:00,18,10,1,"T",7
"Mr","Philip","Eaton","LO338","112 Sunnyhurst Road","Avondale","ZK1 6DH","0161 437 22775","Lunch",15/12/2003,13:00:00,16,10,1,,
"Miss","Iva","Buwczkowski","BU325","18A Peel Gardens","Duckworth","ZK5 9WQ","0161 402 77732","Buffet",10/03/2004,14:00:00,35,10.25,0,"WT",30
"Ms","Sylvia","Cullen","BI355","61 Fellbank","Moorside","ZK6 5JP","0161 425 98761","Birthday",06/06/2004,21:00:00,22,11.75,0,"GD",12
"Mr","Karl","Bourne","DO356","617 Lostock Towers","Daren","ZK2 5JP","0161 480 88873","Dinner",03/12/2003,19:30:00,8,15,1,,
"Mr","Cormac","McArdle","DO361","5 Haringey Road","Daren","ZK2 9JL","0161 427 55533","Dinner",06/08/2004,20:30:00,6,15,0,,
"Mrs","Kath","Roscoe","BI344","34 Robert Street","Hindle","ZK3 7PS","0161 437 11222","Birthday",11/09/2003,18:30:00,28,11.75,1,"GDT",19
"Mr","Chris","Walsh","BI345","77 Punstock Road","Whitehall","ZK11 3ER","0161 482 11448","Birthday",25/10/2003,15:30:00,12,12.75,1,"GD",20
"Ms","Pauline","Armstrong","BI348","5 Chester Avenue","Whitehall","ZK11 4QQ","0161 313 26543","Birthday",02/02/2004,15:30:00,10,12.75,1,"G",10
"Miss","Kerry","Jarvis","LO104","17 Old Barn Hill","Duckworth","ZK5 7HD","0161 487 22444","Lunch",16/12/2003,13:30:00,8,12.5,1,,
"Mr","Mark","Rhodes","BI218","31 Everton Street","Moorside","ZK6 7PT","0161 283 54677","Birthday",24/10/2004,19:00:00,28,6.75,0,"GD",12
"Mr","Cliff","Webb","BR113","33 Hindle Street","Sett","ZK4 8PQ","0161 402 12222","Brunch",18/12/2003,10:30:00,30,9.8,1,"T",7
"Mrs","Rita","Takang","BI219","11 Harwood Street","Hindle","ZK3 7HG","0161 403 32111","Birthday",23/08/2004,18:30:00,24,6.75,0,"GD",12
"Miss","Tracy","Burton","AO127","66 Crown Way","Sett","ZK4 8HG","0161 403 87111","Anniversary",17/07/2004,19:00:00,6,12.75,0,"D",5
"Mrs","Diane","Rutter","BR112","86 Gillbank Place","Hindle","ZK3 9TY","0161 483 31233","Brunch",09/02/2004,10:30:00,30,9.8,1,"T",7
"Miss","Vici","Lorenzo","LO103","22 Curzon Green","Hindle","ZK3 5PQ","0161 483 88991","Lunch",12/01/2004,13:30:00,10,12.50,1,,
"Mrs","Kerris","Forrester","BI215","10 Sundial Lane","Duckworth","ZK5 1YU","0161 422 99887","Birthday",29/06/2004,12:30:00,20,6.75,0,"GD",12
"Mr","Barry","Wardley","BU372","5 Borough Road","Garden Village","ZK6 7RT","0161 283 22334","Buffet",29/11/2003,18:00:00,50,10.25,1,"TW",27
"Mr","John","Stubbs","BU371","14 Rostrevor Avenue","Daren","ZK2 5PS","0161 227 91501","Buffet",15/07/2004,13:00:00,44,10.25,0,"WT",30
"Mr","Alan","Chapman","DO213","42 Westwood Road","Whitehall","ZK1 8GH","0161 423 55446","Dinner",14/02/2004,20:00:00,10,15,1,"D",5
"Ms","Lisa","Jones","BI216","6 Chestnut Avenue","Sett","ZK4 6TH","0161 221 44775","Birthday",06/06/2004,14:30:00,22,6.75,0,"GD",12
"Ms","Marilyn","Gray","DO215","6 Kestrel Close","Daren","ZK2 7RO","0161 992 48561","Dinner",24/06/2004,20:30:00,6,15,0,"D",5
"Miss","Nerys","Ruddock","LO102","54 Collingtree Avenue","Duckworth","ZK5 8UY","0161 222 89731","Lunch",04/03/2004,14:00:00,14,10,1,"T",7
"Mr","Alexander","Church","LO106","6 Selkirk Drive","Garden Village","ZK8 7FC","0161 480 77114","Lunch",16/04/2004,13:00:00,12,10,0,,
"Mr","Alan","Singleton","BU373","92 Queensway","Moorside","ZK6 4RT","0161 483 22991","Buffet",06/02/2004,15:00:00,20,10.25,1,"T",7
"Mrs","Joan","Stubbs","LO110","4 The Fairway","Hindle","ZK3 5PS","0161 227 91501","Lunch",16/05/2004,13:30:00,12,10,0,,
"Miss","Reni","Nyurat","AO124","187 Burton Road","Sett","ZK4 8BN","0161 222 11568","Anniversary",02/09/2004,19:30:00,6,12.75,0,"D",5
"Mr","Gordon","Maxfield","BI217","68 Buxton Road","Sett","ZK4 1PS","0161 852 92388","Birthday",09/11/2004,16:00:00,20,11.75,0,"GD",12
"Mr","James","Lisle-Duncan","BU369","30 Highfield Drive","Duckworth","ZK5 8LL","0161 982 49787","Buffet",07/05/2004,14:00:00,40,10.25,0,"WT",30
"Mr","Walter","Evans","BR101","89 Railway Road","Garden Village","ZK8 9UY","0161 283 33441","Brunch",22/05/2004,11:00:00,33,9.8,0,"WT",30
"Mr","Damien","Hadcroft","LO105","108 Riddings Road","Duckworth","ZK5 6TJ","0161 159 71004","Lunch",11/09/2004,12:30:00,16,12.5,0,"T",7
"Mrs","Alison","Rokeby","AO123","18 Cheviot Street","Daren","ZK2 7DW","0161 987 44423","Anniversary",12/10/2004,20:30:00,10,12.75,0,"D",5
"Mrs","Bernice","Altoft","DO212","7 Church Street","Hindle","ZK3 7PS","0161 921 75542","Dinner",03/02/2004,20:00:00,8,15,1,,
"Mr","Leonard","Park","DO220","263 Branson Street","Hindle","ZK3 3MN","0161 280 66773","Dinner",30/03/2004,20:30:00,4,15,1,,
"Mr","Michael","Fletcher","AO126","8 Manor Towers","Sunnyhurst","ZK7 5SP","0161 576 24917","Anniversary",08/08/2004,20:00:00,4,12.75,0,"D",5
"Mr","Kurt","Van Ryan","AO129","33 Burnside Drive","Daren","ZK2 3WE","0161 224 88776","Anniversary",11/01/2004,19:30:00,8,12.75,1,"D",5
"Mr","James","Duttine","BI212","12 Peabody Street","Duckworth","ZK5 7BV","0161 287 99227","Birthday",30/04/2004,14:30:00,18,6.75,0,"GD",12
"Mr","Kieran","O'Hare","BR111","3 Hazeldine Crescent","Garden Village","ZK8 5QQ","0161 748 92333","Brunch",28/01/2004,11:00:00,18,9.8,1,,
"Miss","Petra","Summers","BI301","36A Mallory Crescent","Sunnyhurst","ZK7 4LK","0161 159 76324","Birthday",05/01/2004,20:00:00,22,6.75,1,"D",5
"Ms","Claudia","Brooke","BI211","7 The Oval","Garden Village","ZK8 6JS","0161 921 84985","Birthday",02/06/2004,14:00:00,20,6.75,0,"GD",12
"Mrs","Jean","Walsh","DO225","99 Marsland Road","Garden Village","ZK8 4SC","0161 921 55867","Dinner",10/01/2004,20:30:00,10,15,1,"T",7
"Miss","Susan","Chapman","BU366","54 Westway","Sett","ZK4 8GH","0161 423 55446","Buffet",21/03/2004,19:00:00,25,10.25,1,"TW",24
"Mrs","Patricia","Jarvis","BR110","71 Sundial Road","Sunnyhurst","ZK7 7HD","0161 487 22444","Brunch",10/07/2004,10:30:00,12,9.8,0,,
Copy and paste the following text in note pad and save it as catering .
"Title","FirstName","LastName","ContractID","Address1","Address2","PostCode","TelNo","Event","Date","Time","Numbers","PricePerPerson","Paid","OptExtras","CostExtras"
"Miss","Joan","Baxendale","BU233","22 Bolton Road","Whitehall","ZK11 8RE","0161 444 67543","Buffet",12/12/2003,12:30:00,30,10.25,0,"D",3.5
"Mr","Simon","Holmes","BU355","89 Holden Road","Avondale","ZK1 7TY","0161 332 10298","Buffet",07/11/2003,13:30:00,25,12.55,0,"W",25
"Mr","Nazir","Washid","DO344","51 Ashton Avenue","Avondale","ZK12 9QW","0161 333 58493","Dinner",06/06/2004,21:00:00,8,15,1,"GT",20
"Mr","Bob","Forrest","LO334","77 Everton Road","Sett","ZK4 11DK","0161 441 67453","Lunch",20/12/2003,13:00:00,10,12.5,1,,
"Mrs","Jasmine","Patel","BU323","21 Peel Street","Duckworth","ZK6 5JK","0161 418 76543","Buffet",18/05/2003,22:00:00,33,10.25,0,"WT",30
"Mr","Lee","Tseng","LO336","7 Pole Lane","Sett","ZK4 2UY","0161 412 78965","Lunch",7/11/2003,13:30:00,18,10,1,"T",7
"Mr","Philip","Eaton","LO338","112 Sunnyhurst Road","Avondale","ZK1 6DH","0161 437 22775","Lunch",15/12/2003,13:00:00,16,10,1,,
"Miss","Iva","Buwczkowski","BU325","18A Peel Gardens","Duckworth","ZK5 9WQ","0161 402 77732","Buffet",10/03/2004,14:00:00,35,10.25,0,"WT",30
"Ms","Sylvia","Cullen","BI355","61 Fellbank","Moorside","ZK6 5JP","0161 425 98761","Birthday",06/06/2004,21:00:00,22,11.75,0,"GD",12
"Mr","Karl","Bourne","DO356","617 Lostock Towers","Daren","ZK2 5JP","0161 480 88873","Dinner",03/12/2003,19:30:00,8,15,1,,
"Mr","Cormac","McArdle","DO361","5 Haringey Road","Daren","ZK2 9JL","0161 427 55533","Dinner",06/08/2004,20:30:00,6,15,0,,
"Mrs","Kath","Roscoe","BI344","34 Robert Street","Hindle","ZK3 7PS","0161 437 11222","Birthday",11/09/2003,18:30:00,28,11.75,1,"GDT",19
"Mr","Chris","Walsh","BI345","77 Punstock Road","Whitehall","ZK11 3ER","0161 482 11448","Birthday",25/10/2003,15:30:00,12,12.75,1,"GD",20
"Ms","Pauline","Armstrong","BI348","5 Chester Avenue","Whitehall","ZK11 4QQ","0161 313 26543","Birthday",02/02/2004,15:30:00,10,12.75,1,"G",10
"Miss","Kerry","Jarvis","LO104","17 Old Barn Hill","Duckworth","ZK5 7HD","0161 487 22444","Lunch",16/12/2003,13:30:00,8,12.5,1,,
"Mr","Mark","Rhodes","BI218","31 Everton Street","Moorside","ZK6 7PT","0161 283 54677","Birthday",24/10/2004,19:00:00,28,6.75,0,"GD",12
"Mr","Cliff","Webb","BR113","33 Hindle Street","Sett","ZK4 8PQ","0161 402 12222","Brunch",18/12/2003,10:30:00,30,9.8,1,"T",7
"Mrs","Rita","Takang","BI219","11 Harwood Street","Hindle","ZK3 7HG","0161 403 32111","Birthday",23/08/2004,18:30:00,24,6.75,0,"GD",12
"Miss","Tracy","Burton","AO127","66 Crown Way","Sett","ZK4 8HG","0161 403 87111","Anniversary",17/07/2004,19:00:00,6,12.75,0,"D",5
"Mrs","Diane","Rutter","BR112","86 Gillbank Place","Hindle","ZK3 9TY","0161 483 31233","Brunch",09/02/2004,10:30:00,30,9.8,1,"T",7
"Miss","Vici","Lorenzo","LO103","22 Curzon Green","Hindle","ZK3 5PQ","0161 483 88991","Lunch",12/01/2004,13:30:00,10,12.50,1,,
"Mrs","Kerris","Forrester","BI215","10 Sundial Lane","Duckworth","ZK5 1YU","0161 422 99887","Birthday",29/06/2004,12:30:00,20,6.75,0,"GD",12
"Mr","Barry","Wardley","BU372","5 Borough Road","Garden Village","ZK6 7RT","0161 283 22334","Buffet",29/11/2003,18:00:00,50,10.25,1,"TW",27
"Mr","John","Stubbs","BU371","14 Rostrevor Avenue","Daren","ZK2 5PS","0161 227 91501","Buffet",15/07/2004,13:00:00,44,10.25,0,"WT",30
"Mr","Alan","Chapman","DO213","42 Westwood Road","Whitehall","ZK1 8GH","0161 423 55446","Dinner",14/02/2004,20:00:00,10,15,1,"D",5
"Ms","Lisa","Jones","BI216","6 Chestnut Avenue","Sett","ZK4 6TH","0161 221 44775","Birthday",06/06/2004,14:30:00,22,6.75,0,"GD",12
"Ms","Marilyn","Gray","DO215","6 Kestrel Close","Daren","ZK2 7RO","0161 992 48561","Dinner",24/06/2004,20:30:00,6,15,0,"D",5
"Miss","Nerys","Ruddock","LO102","54 Collingtree Avenue","Duckworth","ZK5 8UY","0161 222 89731","Lunch",04/03/2004,14:00:00,14,10,1,"T",7
"Mr","Alexander","Church","LO106","6 Selkirk Drive","Garden Village","ZK8 7FC","0161 480 77114","Lunch",16/04/2004,13:00:00,12,10,0,,
"Mr","Alan","Singleton","BU373","92 Queensway","Moorside","ZK6 4RT","0161 483 22991","Buffet",06/02/2004,15:00:00,20,10.25,1,"T",7
"Mrs","Joan","Stubbs","LO110","4 The Fairway","Hindle","ZK3 5PS","0161 227 91501","Lunch",16/05/2004,13:30:00,12,10,0,,
"Miss","Reni","Nyurat","AO124","187 Burton Road","Sett","ZK4 8BN","0161 222 11568","Anniversary",02/09/2004,19:30:00,6,12.75,0,"D",5
"Mr","Gordon","Maxfield","BI217","68 Buxton Road","Sett","ZK4 1PS","0161 852 92388","Birthday",09/11/2004,16:00:00,20,11.75,0,"GD",12
"Mr","James","Lisle-Duncan","BU369","30 Highfield Drive","Duckworth","ZK5 8LL","0161 982 49787","Buffet",07/05/2004,14:00:00,40,10.25,0,"WT",30
"Mr","Walter","Evans","BR101","89 Railway Road","Garden Village","ZK8 9UY","0161 283 33441","Brunch",22/05/2004,11:00:00,33,9.8,0,"WT",30
"Mr","Damien","Hadcroft","LO105","108 Riddings Road","Duckworth","ZK5 6TJ","0161 159 71004","Lunch",11/09/2004,12:30:00,16,12.5,0,"T",7
"Mrs","Alison","Rokeby","AO123","18 Cheviot Street","Daren","ZK2 7DW","0161 987 44423","Anniversary",12/10/2004,20:30:00,10,12.75,0,"D",5
"Mrs","Bernice","Altoft","DO212","7 Church Street","Hindle","ZK3 7PS","0161 921 75542","Dinner",03/02/2004,20:00:00,8,15,1,,
"Mr","Leonard","Park","DO220","263 Branson Street","Hindle","ZK3 3MN","0161 280 66773","Dinner",30/03/2004,20:30:00,4,15,1,,
"Mr","Michael","Fletcher","AO126","8 Manor Towers","Sunnyhurst","ZK7 5SP","0161 576 24917","Anniversary",08/08/2004,20:00:00,4,12.75,0,"D",5
"Mr","Kurt","Van Ryan","AO129","33 Burnside Drive","Daren","ZK2 3WE","0161 224 88776","Anniversary",11/01/2004,19:30:00,8,12.75,1,"D",5
"Mr","James","Duttine","BI212","12 Peabody Street","Duckworth","ZK5 7BV","0161 287 99227","Birthday",30/04/2004,14:30:00,18,6.75,0,"GD",12
"Mr","Kieran","O'Hare","BR111","3 Hazeldine Crescent","Garden Village","ZK8 5QQ","0161 748 92333","Brunch",28/01/2004,11:00:00,18,9.8,1,,
"Miss","Petra","Summers","BI301","36A Mallory Crescent","Sunnyhurst","ZK7 4LK","0161 159 76324","Birthday",05/01/2004,20:00:00,22,6.75,1,"D",5
"Ms","Claudia","Brooke","BI211","7 The Oval","Garden Village","ZK8 6JS","0161 921 84985","Birthday",02/06/2004,14:00:00,20,6.75,0,"GD",12
"Mrs","Jean","Walsh","DO225","99 Marsland Road","Garden Village","ZK8 4SC","0161 921 55867","Dinner",10/01/2004,20:30:00,10,15,1,"T",7
"Miss","Susan","Chapman","BU366","54 Westway","Sett","ZK4 8GH","0161 423 55446","Buffet",21/03/2004,19:00:00,25,10.25,1,"TW",24
"Mrs","Patricia","Jarvis","BR110","71 Sundial Road","Sunnyhurst","ZK7 7HD","0161 487 22444","Brunch",10/07/2004,10:30:00,12,9.8,0,,
Sunday, November 22, 2009
Lesson 5: Data handling task : Workbook 1
Data Handling Task : Work Book 1
Copy and paste the following text in note pad and save it as club players
"MemberID","Title","FirstName","LastName","Address1","Address2","Address3","DateOfBirth","Gender","MemberType"
1021,"Mr","Frank","Streeter","78 Fratton Way","Perth","BD9 6ZZ",13/08/1990,"M","J"
1022,"Ms","Heena","Shah","127 Lonner Street","Molden","BD3 6AZ",17/07/1979,"F","L"
1023,"Mr","William","Ponsonby","26 Railway Street","Highcliffe","BD6 2HF",16/12/1978,"M","M"
1024,"Mr","Mark","Pratney","45 Stoddard Way","Wendhoven","DR3 4GH",02/03/1971,"M","M"
1025,"Mrs","Sally","Kent","34 Way Street","Dratton","DR4 8JK",24/09/1978,"F","L"
1026,"Mr","Thomas","Pranney","56 Franconia Way","Molden","BD3 5TH",05/06/1967,"M","M"
1027,"Mr","David","Pratton","721 Merther Road","Highcliffe","BD6 8LS",09/07/1978,"M","M"
1028,"Mr","David","Pratter","34 Marks Way","Molden","BD3 6GC",04/06/1958,"M","M"
1029,"Miss","Sarah","Fertgin","113 Leighton Avenue","Molden","BD3 2DS",18/03/1963,"F","L"
1030,"Mrs","Josie","Jones","46 Stratton Road","Wendhoven","DR3 5VC",29/06/1974,"F","L"
1031,"Miss","Sadie","Jones","47 Station Street","Ayton Village","DF1 3MK",14/08/1972,"F","L"
1032,"Mr","Robert","Newell","44 High Street","Westering","BD3 5YP",28/03/1954,"M","M"
1033,"Mr","Stanley","Newell","913 High Street","Highcliffe","BD6 7TV",13/12/1990,"M","J"
1034,"Miss","Sally","Jones","16 The Street","Wendhoven","DR3 4HG",02/05/1965,"F","L"
1035,"Mrs","Sarah","Slowcombe","4 Highground","Ayton Village","DF1 3FB",14/11/1982,"F","L"
1036,"Miss","Joan","Katton","3 Derwent Heights","Highcliffe","BD6 9LX",07/09/1971,"F","L"
1037,"Mr","Sidney","Slanter","56 Forth Way","Highcliffe","BD6 3DF",01/10/1986,"M","M"
1038,"Miss","Christine","Pommel","92 Stratford Road","Molden","BD3 6HG",26/01/1971,"F","L"
1039,"Mr","James","Bently","91 Porton Road","Molden","BD3 7UJ",18/10/1990,"M","J"
1040,"Mr","Robert","Dace","5 Ramthorns Close","Churchly","BD3 5TR",05/07/1982,"M","M"
2034,"Mr","Peter","Smith","30 High Street","Wendhoven","DR3 4HK",01/03/1989,"M","J"
2987,"Miss","Susan","Jones","15 Durham Road","Molden","BD3 5TD",17/04/1990,"F","J"
7484,"Mr","David","Breen","1 Grange Road","Highcliffe","BD6 2FT",25/07/1990,"M","M"
9345,"Mrs","Charlotte","Benson","5 Main Street","Molden","BD3 3NH",15/04/1990,"F","L"
3815,"Mr","Anthony","Lumsden","11 Newton Street","Waterfield","YO14 8BD",19/10/1980,"M","M"
7659,"Miss","Debbie","Conlin","21 West Road","Molden","BD3 8DV",03/07/1974,"F","L"
3496,"Mr","Steve","Penn","13 Newton Street","Highcliffe","BD6 6YH",29/08/1990,"M","J"
6956,"Mr","Michael","Gettins","The Croft","Ayton Village","DF1 4XH",08/09/1985,"M","M"
5279,"Mr","Martin","Gale","45 Bexhill Road","Wendhoven","DR3 7HD",09/02/1984,"M","M"
9235,"Mr","James","Gainford","132 Beck Road","Highcliffe","BD6 5DF",23/10/1990,"M","J"
3814,"Mr","John","Betts","14 Church Road","Wendhoven","DR3 9HB",12/07/1967,"M","M"
3261,"Mrs","Joan","Henson","21 Fairley Way","Ayton Village","DF1 3DP",14/06/1986,"F","L"
3321,"Mr","Ravinder","Patel","13 Westover Avenue","Molden","DB1 4FG",07/08/1982,"M","M"
4962,"Mr","Jason","Atkins","19 Westover Avenue","Molden","DB1 4FG",01/01/1990,"M","J"
5329,"Miss","Joan","Wilkins","25 Newton Street","Highcliffe","BD6 4DF",01/01/1991,"F","J"
5187,"Mr","Mike","Fellows","19 Hill Street","Ayton Village","DF5 2SH",31/12/1990,"M","J"
4295,"Mr","Howard","Everton","13 Beech Road","Wendhoven","DR3 3GH",13/07/1975,"M","M"
1214,"Mrs","Carolyn","Mills","41 Gatehouse Lane","Molden","DB1 5JF",27/06/1979,"F","L"
1829,"Mr","David","Hall","4 Bexhill Road","Wendhoven","DR3 6JK",15/07/1983,"M","M"
2262,"Mr","Malcolm","Adams","5 West Road","Molden","BD3 3FD",19/03/1991,"M","J"
3192,"Mr","Richard","Holmes","7 Grange Road","Highcliffe","BD6 9SD",01/01/1992,"M","J"
5608,"Mrs","Rosemary","North","15 Fairley Way","Ayton Village","DF1 5TF",05/04/1990,"F","L"
5271,"Mr","Grant","Yelton","17 Ferndale Road","Highcliffe","BD6 3CF",06/02/1983,"M","M"
6002,"Miss","Jennifer","St John","2 Church Road","Wendhoven","DR3 2HJ",28/07/1985,"F","L"
6943,"Miss","Kelly","Unwin","10 Main Street","Molden","BD3 4RT",01/01/1990,"F","J"
7291,"Mrs","Kathy","Beddoes","9 High Street","Wendhoven","DR3 7HG",19/06/1985,"F","L"
7895,"Mr","Colin","Wilson","9 Durham Road","Molden","BD3 2FD",20/07/1989,"M","M"
Copy and paste the following text in note pad and save it as Ice cream stock
"ProductID","ProductName","Category","MinLevel","MaxLevel","Re-orderLevel","UnitSize","Supplier","TradePrice","RetailPrice","VanAStock","VanBStock","VanCStock"
"FA01","Jumbo Truffle Milk","Adult Lines",2,48,24,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA02","Jumbo Truffle White","Adult Lines",2,48,24,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA03","Monster Choc Bar","Adult Lines",24,96,48,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA04","Crispy Mint","Adult Lines",2,48,24,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA05","Vanilla 1 litre","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA06","Chocolate 1 litre","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA07","Raspberry 1 litre","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,1
"FA08","Italian 1 litre toffee","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA09","Italian 1 litre rum & raisin","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA10","Chocolate Treats","Take Home",3,8,4,4,"Fabrizio Bros",7.43,2.34,1,0,1
"IC01","Heavenly Vanilla/Choc","Ice Cream Cones",20,40,20,20,"Ices 'R' Us",3.00,0.20,1,0,0
"IC02","Heavenly Choc 'n' Nut","Ice Cream Cones",6,32,6,6,"Ices 'R' Us",8.39,2.80,1,0,0
"IC03","Heavenly Strawberry","Ice Cream Cones",6,32,6,6,"Ices 'R' Us",8.39,2.80,1,0,0
"IC04","Nougat Ice","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,1,1,0
"IC05","Chocolate Treats","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,0,1,0
"IC06","Toffee Split","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,0,1,0
"IC07","Bubbly Mint","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,0,1,0
"IC08","Whirly Wafer","Ice Cream Snacks",2,64,32,32,"Ices 'R' Us",3.63,0.28,0,1,0
"RO01","Mint Miracle","Desserts",3,8,4,4,"Roberto & Sons",4.50,2.20,1,0,0
"RO02","Chunky Choc","Desserts",3,8,4,4,"Roberto & Sons",4.50,2.20,1,0,1
"RO03","Vanilla Treat","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,0
"RO04","Toffee Treat","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,0
"RO05","Mint Treat","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,1
"RO06","Ice Surprise","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,1
"SH01","Lemon Splice","Refreshment Lines",24,72,36,24,"Shelley & Co",4.94,0.32,0,1,0
"SH02","Orange Splice","Refreshment Lines",24,72,48,12,"Shelley & Co",4.94,0.62,0,1,0
"SH03","Orange Sizzler","Refreshment Lines",24,72,36,24,"Shelley & Co",9.44,0.67,0,1,1
"SH04","Purple Pops","Children's Lines",20,80,40,40,"Shelley & Co",8.00,0.36,1,1,0
"SH05","Ace","Children's Lines",24,96,48,24,"Shelley & Co",4.80,0.36,1,1,0
"SH06","Frosty","Children's Lines",24,70,35,35,"Shelley & Co",7.00,0.35,1,1,0
"SH07","Tootie Fruitie","Children's Lines",6,64,32,32,"Shelley & Co",6.40,0.56,1,1,0
"SH08","Super Cool","Children's Lines",6,64,32,32,"Shelley & Co",6.40,0.56,1,1,1
"SH09","Rocket","Children's Lines",12,72,36,12,"Shelley & Co",3.60,0.56,1,1,1
"SH10","Milk Lolly","Children's Lines",12,72,36,12,"Shelley & Co",3.60,0.56,1,1,1
"SH11","Fruit Lolly","Children's Lines",12,72,36,12,"Shelley & Co",3.60,0.56,1,1,1
"SH12","Pink Pig","Children's Lines",6,64,32,32,"Shelley & Co",9.53,0.76,1,1,1
"SH13","Blue Baboon","Children's Lines",20,80,40,40,"Shelley & Co",12.00,0.46,1,1,1
"SH14","UFO","Children's Lines",12,36,24,12,"Shelley & Co",3.80,0.58,1,1,0
"WA01","Chocolate Shell","Cones & Wafers",100,520,260,260,"Wardleys",4.00,0.05,1,1,1
"WA02","Duo Cones","Cones & Wafers",100,520,260,260,"Wardleys",9.62,0.10,1,0,1
"WA03","Vanilla tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,0
"WA04","Chocolate tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,0
"WA05","Cherry tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,1
"WA06","Toffee tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,0
"WA07","Italian 5 litre","Leisure Catering",2,14,8,4,"Wardleys",20.00,7.50,0,0,1
"WA08","Super Soft 5 litre","Leisure Catering",2,14,8,4,"Wardleys",20.00,7.50,0,0,1
"WA09","Vanilla 10 litre","Leisure Catering",2,6,4,1,"Wardleys",7.20,8.75,0,0,1
"WA10","Chocolate 10 litre","Leisure Catering",2,6,4,1,"Wardleys",7.20,8.75,0,0,1
"WA11","Packet Wafers (10 pack)","Cones & Wafers",24,96,48,48,"Wardleys",8.80,0.24,1,1,0
"WA12","Large Round Cones","Cones & Wafers",100,780,390,390,"Wardleys",8.83,0.04,1,0,1
Use the above .txt files to complete the follwing task :
Click on the images to enlarge the image .
Copy and paste the following text in note pad and save it as club players
"MemberID","Title","FirstName","LastName","Address1","Address2","Address3","DateOfBirth","Gender","MemberType"
1021,"Mr","Frank","Streeter","78 Fratton Way","Perth","BD9 6ZZ",13/08/1990,"M","J"
1022,"Ms","Heena","Shah","127 Lonner Street","Molden","BD3 6AZ",17/07/1979,"F","L"
1023,"Mr","William","Ponsonby","26 Railway Street","Highcliffe","BD6 2HF",16/12/1978,"M","M"
1024,"Mr","Mark","Pratney","45 Stoddard Way","Wendhoven","DR3 4GH",02/03/1971,"M","M"
1025,"Mrs","Sally","Kent","34 Way Street","Dratton","DR4 8JK",24/09/1978,"F","L"
1026,"Mr","Thomas","Pranney","56 Franconia Way","Molden","BD3 5TH",05/06/1967,"M","M"
1027,"Mr","David","Pratton","721 Merther Road","Highcliffe","BD6 8LS",09/07/1978,"M","M"
1028,"Mr","David","Pratter","34 Marks Way","Molden","BD3 6GC",04/06/1958,"M","M"
1029,"Miss","Sarah","Fertgin","113 Leighton Avenue","Molden","BD3 2DS",18/03/1963,"F","L"
1030,"Mrs","Josie","Jones","46 Stratton Road","Wendhoven","DR3 5VC",29/06/1974,"F","L"
1031,"Miss","Sadie","Jones","47 Station Street","Ayton Village","DF1 3MK",14/08/1972,"F","L"
1032,"Mr","Robert","Newell","44 High Street","Westering","BD3 5YP",28/03/1954,"M","M"
1033,"Mr","Stanley","Newell","913 High Street","Highcliffe","BD6 7TV",13/12/1990,"M","J"
1034,"Miss","Sally","Jones","16 The Street","Wendhoven","DR3 4HG",02/05/1965,"F","L"
1035,"Mrs","Sarah","Slowcombe","4 Highground","Ayton Village","DF1 3FB",14/11/1982,"F","L"
1036,"Miss","Joan","Katton","3 Derwent Heights","Highcliffe","BD6 9LX",07/09/1971,"F","L"
1037,"Mr","Sidney","Slanter","56 Forth Way","Highcliffe","BD6 3DF",01/10/1986,"M","M"
1038,"Miss","Christine","Pommel","92 Stratford Road","Molden","BD3 6HG",26/01/1971,"F","L"
1039,"Mr","James","Bently","91 Porton Road","Molden","BD3 7UJ",18/10/1990,"M","J"
1040,"Mr","Robert","Dace","5 Ramthorns Close","Churchly","BD3 5TR",05/07/1982,"M","M"
2034,"Mr","Peter","Smith","30 High Street","Wendhoven","DR3 4HK",01/03/1989,"M","J"
2987,"Miss","Susan","Jones","15 Durham Road","Molden","BD3 5TD",17/04/1990,"F","J"
7484,"Mr","David","Breen","1 Grange Road","Highcliffe","BD6 2FT",25/07/1990,"M","M"
9345,"Mrs","Charlotte","Benson","5 Main Street","Molden","BD3 3NH",15/04/1990,"F","L"
3815,"Mr","Anthony","Lumsden","11 Newton Street","Waterfield","YO14 8BD",19/10/1980,"M","M"
7659,"Miss","Debbie","Conlin","21 West Road","Molden","BD3 8DV",03/07/1974,"F","L"
3496,"Mr","Steve","Penn","13 Newton Street","Highcliffe","BD6 6YH",29/08/1990,"M","J"
6956,"Mr","Michael","Gettins","The Croft","Ayton Village","DF1 4XH",08/09/1985,"M","M"
5279,"Mr","Martin","Gale","45 Bexhill Road","Wendhoven","DR3 7HD",09/02/1984,"M","M"
9235,"Mr","James","Gainford","132 Beck Road","Highcliffe","BD6 5DF",23/10/1990,"M","J"
3814,"Mr","John","Betts","14 Church Road","Wendhoven","DR3 9HB",12/07/1967,"M","M"
3261,"Mrs","Joan","Henson","21 Fairley Way","Ayton Village","DF1 3DP",14/06/1986,"F","L"
3321,"Mr","Ravinder","Patel","13 Westover Avenue","Molden","DB1 4FG",07/08/1982,"M","M"
4962,"Mr","Jason","Atkins","19 Westover Avenue","Molden","DB1 4FG",01/01/1990,"M","J"
5329,"Miss","Joan","Wilkins","25 Newton Street","Highcliffe","BD6 4DF",01/01/1991,"F","J"
5187,"Mr","Mike","Fellows","19 Hill Street","Ayton Village","DF5 2SH",31/12/1990,"M","J"
4295,"Mr","Howard","Everton","13 Beech Road","Wendhoven","DR3 3GH",13/07/1975,"M","M"
1214,"Mrs","Carolyn","Mills","41 Gatehouse Lane","Molden","DB1 5JF",27/06/1979,"F","L"
1829,"Mr","David","Hall","4 Bexhill Road","Wendhoven","DR3 6JK",15/07/1983,"M","M"
2262,"Mr","Malcolm","Adams","5 West Road","Molden","BD3 3FD",19/03/1991,"M","J"
3192,"Mr","Richard","Holmes","7 Grange Road","Highcliffe","BD6 9SD",01/01/1992,"M","J"
5608,"Mrs","Rosemary","North","15 Fairley Way","Ayton Village","DF1 5TF",05/04/1990,"F","L"
5271,"Mr","Grant","Yelton","17 Ferndale Road","Highcliffe","BD6 3CF",06/02/1983,"M","M"
6002,"Miss","Jennifer","St John","2 Church Road","Wendhoven","DR3 2HJ",28/07/1985,"F","L"
6943,"Miss","Kelly","Unwin","10 Main Street","Molden","BD3 4RT",01/01/1990,"F","J"
7291,"Mrs","Kathy","Beddoes","9 High Street","Wendhoven","DR3 7HG",19/06/1985,"F","L"
7895,"Mr","Colin","Wilson","9 Durham Road","Molden","BD3 2FD",20/07/1989,"M","M"
Copy and paste the following text in note pad and save it as Ice cream stock
"ProductID","ProductName","Category","MinLevel","MaxLevel","Re-orderLevel","UnitSize","Supplier","TradePrice","RetailPrice","VanAStock","VanBStock","VanCStock"
"FA01","Jumbo Truffle Milk","Adult Lines",2,48,24,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA02","Jumbo Truffle White","Adult Lines",2,48,24,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA03","Monster Choc Bar","Adult Lines",24,96,48,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA04","Crispy Mint","Adult Lines",2,48,24,24,"Fabrizio Bros",4.73,0.24,1,0,1
"FA05","Vanilla 1 litre","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA06","Chocolate 1 litre","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA07","Raspberry 1 litre","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,1
"FA08","Italian 1 litre toffee","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA09","Italian 1 litre rum & raisin","Take Home",3,8,4,4,"Fabrizio Bros",5.80,2.40,1,0,0
"FA10","Chocolate Treats","Take Home",3,8,4,4,"Fabrizio Bros",7.43,2.34,1,0,1
"IC01","Heavenly Vanilla/Choc","Ice Cream Cones",20,40,20,20,"Ices 'R' Us",3.00,0.20,1,0,0
"IC02","Heavenly Choc 'n' Nut","Ice Cream Cones",6,32,6,6,"Ices 'R' Us",8.39,2.80,1,0,0
"IC03","Heavenly Strawberry","Ice Cream Cones",6,32,6,6,"Ices 'R' Us",8.39,2.80,1,0,0
"IC04","Nougat Ice","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,1,1,0
"IC05","Chocolate Treats","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,0,1,0
"IC06","Toffee Split","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,0,1,0
"IC07","Bubbly Mint","Ice Cream Snacks",2,48,24,24,"Ices 'R' Us",4.80,0.50,0,1,0
"IC08","Whirly Wafer","Ice Cream Snacks",2,64,32,32,"Ices 'R' Us",3.63,0.28,0,1,0
"RO01","Mint Miracle","Desserts",3,8,4,4,"Roberto & Sons",4.50,2.20,1,0,0
"RO02","Chunky Choc","Desserts",3,8,4,4,"Roberto & Sons",4.50,2.20,1,0,1
"RO03","Vanilla Treat","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,0
"RO04","Toffee Treat","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,0
"RO05","Mint Treat","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,1
"RO06","Ice Surprise","Desserts",3,8,4,4,"Roberto & Sons",7.04,2.74,1,0,1
"SH01","Lemon Splice","Refreshment Lines",24,72,36,24,"Shelley & Co",4.94,0.32,0,1,0
"SH02","Orange Splice","Refreshment Lines",24,72,48,12,"Shelley & Co",4.94,0.62,0,1,0
"SH03","Orange Sizzler","Refreshment Lines",24,72,36,24,"Shelley & Co",9.44,0.67,0,1,1
"SH04","Purple Pops","Children's Lines",20,80,40,40,"Shelley & Co",8.00,0.36,1,1,0
"SH05","Ace","Children's Lines",24,96,48,24,"Shelley & Co",4.80,0.36,1,1,0
"SH06","Frosty","Children's Lines",24,70,35,35,"Shelley & Co",7.00,0.35,1,1,0
"SH07","Tootie Fruitie","Children's Lines",6,64,32,32,"Shelley & Co",6.40,0.56,1,1,0
"SH08","Super Cool","Children's Lines",6,64,32,32,"Shelley & Co",6.40,0.56,1,1,1
"SH09","Rocket","Children's Lines",12,72,36,12,"Shelley & Co",3.60,0.56,1,1,1
"SH10","Milk Lolly","Children's Lines",12,72,36,12,"Shelley & Co",3.60,0.56,1,1,1
"SH11","Fruit Lolly","Children's Lines",12,72,36,12,"Shelley & Co",3.60,0.56,1,1,1
"SH12","Pink Pig","Children's Lines",6,64,32,32,"Shelley & Co",9.53,0.76,1,1,1
"SH13","Blue Baboon","Children's Lines",20,80,40,40,"Shelley & Co",12.00,0.46,1,1,1
"SH14","UFO","Children's Lines",12,36,24,12,"Shelley & Co",3.80,0.58,1,1,0
"WA01","Chocolate Shell","Cones & Wafers",100,520,260,260,"Wardleys",4.00,0.05,1,1,1
"WA02","Duo Cones","Cones & Wafers",100,520,260,260,"Wardleys",9.62,0.10,1,0,1
"WA03","Vanilla tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,0
"WA04","Chocolate tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,0
"WA05","Cherry tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,1
"WA06","Toffee tub","Ice Cream Snacks",2,36,12,24,"Wardleys",8.65,1.00,0,1,0
"WA07","Italian 5 litre","Leisure Catering",2,14,8,4,"Wardleys",20.00,7.50,0,0,1
"WA08","Super Soft 5 litre","Leisure Catering",2,14,8,4,"Wardleys",20.00,7.50,0,0,1
"WA09","Vanilla 10 litre","Leisure Catering",2,6,4,1,"Wardleys",7.20,8.75,0,0,1
"WA10","Chocolate 10 litre","Leisure Catering",2,6,4,1,"Wardleys",7.20,8.75,0,0,1
"WA11","Packet Wafers (10 pack)","Cones & Wafers",24,96,48,48,"Wardleys",8.80,0.24,1,1,0
"WA12","Large Round Cones","Cones & Wafers",100,780,390,390,"Wardleys",8.83,0.04,1,0,1
Use the above .txt files to complete the follwing task :
Click on the images to enlarge the image .
Lesson 4 : Car Database
CAR DATABASE QUESTIONS
Create the Car Database as shown below
Design View
Use the car database to find the answers to the following questions
Use the table design view to find the following information
1 Which field is the primary key?
2 Which field has the data type ‘number’?
3 Which field has the data type ‘currency’?
4 Which field will only allow you to put yes/no into?
5 Which field will only allow 8 characters to be typed?
Use the table view to find the following information
6 How many records are in the database?
7 Sort the database on ‘price’ in ascending order. Which is the cheapest car?
8 Sort the database on ‘price’ in descending order. Which make would be the most expensive car?
9 Sort the database on ‘year’ in ascending order. Which is the oldest car?
10 Sort the database on ‘Make’ in descending order. Which make would be displayed first?
Create a query to answer the following questions
11 How many ‘Ford’ cars are in the database?
12 How many ‘Ford Ka’ are in the database?
13 How many different ‘Rover’ models are in the database?
14 How many cars were registered in 1999?
15 How many cars do NOT have tax?
16 How many cars cost less than £1000?
17 How many cars cost more than £4000?
18 How many cars cost less than £2000 AND do not have tax?
19 How many Red cars are there?
20 How many Vauxhall cars are White
21 How many Red cars cost more than £5000?
22 How many Silver cars were there in 1999?
23 What colour is the Renault Clio 2002?
24 What is the make of two cars that are Black and built in 1997?
25 How many cars are there between 2000 and 2003?
26 How many cars for 1999 are there which are NOT red?
27 How many cars are there in 1996 which are NOT silver and NOT a 406?
Create the Car Database as shown below
Design View
Car Records
Use the car database to find the answers to the following questions
Use the table design view to find the following information
1 Which field is the primary key?
2 Which field has the data type ‘number’?
3 Which field has the data type ‘currency’?
4 Which field will only allow you to put yes/no into?
5 Which field will only allow 8 characters to be typed?
Use the table view to find the following information
6 How many records are in the database?
7 Sort the database on ‘price’ in ascending order. Which is the cheapest car?
8 Sort the database on ‘price’ in descending order. Which make would be the most expensive car?
9 Sort the database on ‘year’ in ascending order. Which is the oldest car?
10 Sort the database on ‘Make’ in descending order. Which make would be displayed first?
Create a query to answer the following questions
11 How many ‘Ford’ cars are in the database?
12 How many ‘Ford Ka’ are in the database?
13 How many different ‘Rover’ models are in the database?
14 How many cars were registered in 1999?
15 How many cars do NOT have tax?
16 How many cars cost less than £1000?
17 How many cars cost more than £4000?
18 How many cars cost less than £2000 AND do not have tax?
19 How many Red cars are there?
20 How many Vauxhall cars are White
21 How many Red cars cost more than £5000?
22 How many Silver cars were there in 1999?
23 What colour is the Renault Clio 2002?
24 What is the make of two cars that are Black and built in 1997?
25 How many cars are there between 2000 and 2003?
26 How many cars for 1999 are there which are NOT red?
27 How many cars are there in 1996 which are NOT silver and NOT a 406?
Lesson 3 : Skiing Database Extra
Skiing Database Extra Questions
1. Add the following record to the database:
It is in FRANCE, the resort is VAL THORNES, the snow depth on the lower slopes of 80cm and on the upper slopes 350cm, the temperature is 0 degrees, the runs are ICY and the piste is HARD.
2. The information for the JACKSON HOLE resort in the UNITED STATES is no longer required. Delete the record.
3. Some corrections need to be made to the original data:
a. The lower slopes at Wengen, Switzerland should be 25 not 10cm
b. The information relating to the runs at Bormio, Italy should be icy not patchy.
4. Sort the file into alphabetical order of RESORT.
5. Sort the file into descending order of TEMPERATURE
Using Searches
6. Search for all records showing GOOD conditions on the runs.
7. Search for all records showing temperatures over 4 degrees.
8. Search for all records for FRENCH resorts with snow on the upper slopes of 350cm or more.
9. Sort one other field of your choice.
10. It is now your chance to choose a skiing holiday! Decide what your criteria are (at least two), e.g. I want to go to Italy, to a ski resort with slick runs.
1. Add the following record to the database:
It is in FRANCE, the resort is VAL THORNES, the snow depth on the lower slopes of 80cm and on the upper slopes 350cm, the temperature is 0 degrees, the runs are ICY and the piste is HARD.
2. The information for the JACKSON HOLE resort in the UNITED STATES is no longer required. Delete the record.
3. Some corrections need to be made to the original data:
a. The lower slopes at Wengen, Switzerland should be 25 not 10cm
b. The information relating to the runs at Bormio, Italy should be icy not patchy.
4. Sort the file into alphabetical order of RESORT.
5. Sort the file into descending order of TEMPERATURE
Using Searches
6. Search for all records showing GOOD conditions on the runs.
7. Search for all records showing temperatures over 4 degrees.
8. Search for all records for FRENCH resorts with snow on the upper slopes of 350cm or more.
9. Sort one other field of your choice.
10. It is now your chance to choose a skiing holiday! Decide what your criteria are (at least two), e.g. I want to go to Italy, to a ski resort with slick runs.
Key stage 4 - ICT Resources
Welcome to the ICT link. This page is designed to act as a portal to enable you to find the information you need. If you are looking for links to worksheets , past papers ……. please visit the pages listed on the right.
Unit 11 : Databases : Worksheet 1
Creating a database about Skiing Holidays
Your task is to create a database. You will need to follow these instructions carefully.
1. Open Microsoft Access
2. Select Blank Access Database then OK
3. Save the blank database in your Database folder and call it Skiing.
4. Select Create table in Design View
5. Type in the following field names in the correct order:
6. Once you have typed in all of the fields, close the table and save it. Call it Skiing.
7. Click ‘Yes’ when the following box appears:
8. Open your table called Skiing by double clicking on it and type in the data from the other side of this worksheet.
Your task is to create a database. You will need to follow these instructions carefully.
1. Open Microsoft Access
2. Select Blank Access Database then OK
3. Save the blank database in your Database folder and call it Skiing.
4. Select Create table in Design View
5. Type in the following field names in the correct order:
6. Once you have typed in all of the fields, close the table and save it. Call it Skiing.
7. Click ‘Yes’ when the following box appears:
8. Open your table called Skiing by double clicking on it and type in the data from the other side of this worksheet.
Saturday, November 21, 2009
Databases
Some presentaions :
Step 1 : Import Text Files (done by Wibunsak and bilal -Year 11 B2)
Step 2 :datatype(done by Wibunsak and bilal -Year 11 B2)
Step 3: Formatting Fields (done by Wibunsak and bilal -Year 11 B2)
Databases
Databases are one of the most powerful tools that people use to help them handle large amounts of information.
A database is made up of one or more related files (or tables in Microsoft Access).
A file is made up of one or more related records.
Records are made up of one or more related fields.
A field is made up of a group of related characters. It is a piece of information about something.
Tables:
Design tables
Set Format for a table field
Apply & use an input mask for a table field
Understand the purpose of indexes
Use data validation for a table field and for a record
Document a table - Relationships, Documenter report, Object Dependencies
Queries:
Create a Select query in Query Design View
Use AND and OR criteria in a query
Use the Like operator
Use totals in a query
Concatenate text fields
Calculate values
Create a parameter query
Create a Make-Table query
Create an Append query
Create a Delete query
Create an Update query
Create a Crosstab query
Document a query - Documenter report, Object Dependencies
Compact and repair a database
Table Design
You want to design your tables so that:
It is easy to enter and update information.
Data is consistent throughout.
There is no wasted disk space.
The database responds quickly to searches.
How does one do that??
General Principles for Table Design:
One item of data goes into only one field in one table.
A table's fields must be logically related to each other.
The order of the fields is not important.
Choose entries from a list or table whenever possible.
Use field validation to make sure that the data is in the right form.
Field size fits the expected size of the data.
Create indexes for fields that you will be searching on often.
Good table design requires some logic and thinking ahead. Are you wasting any disk space? Will the same value, like a company's name, be spelled the same in all records? If the company's name changes, can you update it in just one place? Will your design allow for fast searches? What if there are thousands of records?
Goals for Table Design
Save disk space
Save time and effort
Avoid data errors and inconsistencies
Keep database functions fast
How to accomplish these goals? You will need to follow a few rules and then think ahead about the future needs of your database.
How do you want to see the data in your fields? Do you want dates to show like 1/4/2006 or January 4, 2006? Do you want a phone number to look like 123-555-6789 or 1235556789? Does a text field need to look like abcd or ABCD?
You can make choices like these in the Table Design View. There are two properties that you can use to control how a field's values show, Format and Input Mask.
Inherited: The Format and Input Mask properties also apply to the field when it is part of a query, form, or report. You can make a different choice in the Design View for the query, form, or report.
Using both: If you define both the Format and Input Mask properties for a field, be sure that they do not conflict with each other! You will see the Input Mask only when you are entering or editing data. The Format is applied only after the record is saved
Format Property
Format: Controls how the value is displayed, but does not affect what is actually stored in the database.
The formatting is applied only after the value has been saved.
What are your choices? It depends on the field's type. Many types have a drop list in the Format property box.
--------------------------------------------------------------------------------
Input Mask Property
Input Mask: Controls how you enter or edit the data.
You can use certain symbols to restrict what kinds of characters are allowed and to show how many are allowed. You can save or not save literal characters like hyphens, spaces, parentheses, and periods as part of the value.
Input Masks are used mostly with Text and Date/Time fields, but can also be used in Number or Currency fields.
After the data is saved, the Format property controls what you see. Think carefully before setting both Format and Input Mask properties for the same field.
Parts of an Input Mask: An input mask has 3 sections, separated by a semi-colon (;)
; ;
Example: !999(999)000-0000;0;_ for a phone number with country code and area code.
Part 1: Mask itself, like !999(999)000-0000 in the example above for a phone number.
Once you click in the field itself, you would see: (___)___-____
! = Accept typing starting from the right instead of from the left
0 = You must enter a digit for each 0 in the mask.
9 = You can enter a digit in where you see a 9 in the mask, but you do not have to.
Part 2: Save literal characters in the mask? (like the parentheses and hyphen in the example)
0 = Yes, store characters with the data.
1 = No, do NOT store characters with the data.
Part 3: Placeholder: The character to use to show how many characters to enter.
The example above uses an underscore. You can use any character as a placeholder. To not show a placeholder at all, use "" in the third position.
Input Mask Wizard: The wizard offers masks for common fields like US phone numbers, postal codes, social security numbers. You open this wizard from the button with three dots , which appears when you select the Input Mask box for the field.
In the wizard's Try It box you can enter an example value to see if this mask will work well for the values you expect. To use a mask, you must know what to expect!!
Customize the list: The Edit List button opens another dialog that shows the definition of the selected mask. You can enter a new Description and change the mask. Clicking the Close button adds it to the list.
Symbols for custom Input Masks
Step-by-Step: Formatting Fields
--------------------------------------------------------------------------------
What you will learn: to create a table with Table Wizard
to set the Format property for a field
to enter data using an input mask
to edit data in a field with an Input Mask
to create a custom Input Mask for a field
--------------------------------------------------------------------------------
Start with: , Projects database open.
The story so far:
World Travel Inc. wants to include in their database some information about various projects and which staff members are involved with which projects. You designed two tables in the last lesson but now see that you need a Staff table also.
You will create a Staff table using the Table Wizard and then look at Format and Input Mask properties.
Create Staff Table with Table Wizard
If necessary, switch to the Database Window in your Projects database.
Select Tables and click the New button. The New Table dialog appears.
Click on Table Wizard and then on OK.
The Table Wizard dialog appears with a number of pre-designed tables for common situations.
Select Business, if necessary.
Select Employees, from the Sample tables list. A list of fields from this sample table appears in the center.
You do not need all of these fields. You can rename them or add others after creating the initial table.
Using the list below, click a field in the center list of the Table Wizard. Then click the > button to add it to the list on the right, the fields for your new table. The list in the center does not change.
EmployeeID
SocialSecurityNumber
NationalEmplNumber
FirstName
MiddleName
LastName
Title
EmailName
Extension
Address
City
StateOrProvince
Region
PostalCode
Country/Region
HomePhone
BirthDate
SpouseName
EmrcyContactName
EmrcyContactPhone
Notes
Click on Next.
The next step in the wizard asks you about the table's name and primary key .
Change the table's name to Staff.
Choose to set the primary key yourself.
Click on Next.
The next step in the wizard sets the primary key.
Select the field EmployeeID as your primary.
Choose to use consecutive numbers that MS Access automatically assigns as each record is created.
Click on Next.
The next step tries to identify what tables the new one might be related to.
Access cannot see any relationships. You will be making changes, however that will make the new Staff table related to the ProjectStaff table.
Click on the button Relationships in the dialog.
A new window appears with some choices.
Choose the middle item, One record in the Staff table will match many records in the ProjectStaff table.
Click on OK to close the Relationships dialog.
The Table Wizard window now shows that Staff is related to ProjectStaff .
Click on Next.
The next step asks if you are ready to enter data or do you want to modify the design.
Select to Modify the table design.
Click on Finish.
Your new table opens in Design View.
--------------------------------------------------------------------------------
Enter Data
To see how field properties work, you first need a record!
Switch to Datasheet View for the table Staff.
Enter the following as the first record, exactly as shown:
(Leave the other fields blank for now.)
First Name Luis
Middle Name P
Last Name Perez
Title President
Email Name LuisPerez
Extension ViajesMundiales.net
Address Montevideo 6553
City Buenos Aires
Postal Code 1123
Country/Region Argentina
Home Phone +54 (1) 555-1234
Spouse Name Rosa Perez
Emrgcy Contact Name Rosa Perez
Emrgcy Contact Phone +54 (1) 555-1234
Beware of Undo! The Undo command will wipe out the whole record if it has not been saved! After saving, Undo removes all of the changes that you made since saving the record.
To save a record:
Just change to a different record.
Menu: select Records
Save Record.
Keyboard use the key combo SHIFT + ENTER.
Toolbar: click the Save button .
Before leaving a field, pressing the ESC key will return a field to its previous value.
Note: The person's name is in three fields. The email address is in 2 fields.
Note: The phone numbers in Argentina have a different form than in the USA. It's a good thing that the wizard did not apply a Format or Input Mask property for the phone numbers. Depending on the countries that you need to work with, you might want to split each phone number up into parts, like with the email address and name fields.
Widen columns: Drag the right edge of the heading to the right for any fields that are too narrow to show all of the characters.
Click the Save button to save the record.
--------------------------------------------------------------------------------
Format Property: < (forcing lower case)
Since this table will have records involving several countries, you cannot easily apply formatting to postal codes or phone numbers or even addresses. You can use formatting to make sure that the email address parts are all lower case.
Switch to Design View for the table Staff.
Select the field EmailName.
In the Format box, type < .
Repeat for the field Extension.
Save the table.
Switch back to Datasheet View.
Both of the fields are now in all lower case letters.
(Did you widen the Extension field to see all of the characters?)
Experiment by typing various combinations of lower case and upper case letters in these fields and then changing to a different field. The value is shown in lower case only after you leave the field or save the record.
The Format Property does not change what is stored in the field, only the way it is shown.
--------------------------------------------------------------------------------
Using an Input Mask: Enter Value (Date)
The Table Wizard set both the Format and Input Mask Properties for the field, Birthdate. Sometimes these can conflict with each other, or at least be confusing.
Now you will enter a date into the Birthdate field to see how an input mask works. After you save the date, the Format property displays the date in Short Date format.
Remember: Using the Undo command will wipe out the whole record if it has not been saved!
Click in the Birthdate field.
A mask appears - blanks and slash characters. The cursor is a thick black bar, showing where the next character will go.
Problem: Mask does not appear: Did you click in the field or did you use TAB or arrow keys to get into the field? Sometimes moving with the arrow keys or TAB key does not make the mask appear right away.
Solution: Start typing anyway. Once you start typing the mask appears.
In the Birthdate field type February 14, 1954.
Does your typing show at all?? The letters do not! Letters are not allowed by the input mask: 99/99/00;0
Explanation of the mask 99/99/00;0
Where you see 9 in the mask, you can type a digit or a space.
Where you see a 0, you must type a digit, and not a space.
BUT, if you want to type just one digit for a month or day, it must be in place of the second 9, not the first one.
So... you can type: space 5 space 4 6 4 and Access will show the date 5/4/64. But if you type 5 space 4 space 6 4, then Access will show an error message and refuse your date!
Details on custom Input Masks
Problem: February 14, 1954 does show in the field
If you delete a value from a field, you may be able to type values that do not match the input mask... but the value will not be accepted.
If you save the value by moving to a different field or saving the record, then a message appears that says that the value is not appropriate for the input mask.
Solution: Click on OK and then delete the date. Try again.
Click in the Birthdate field and type 2141954.
Before you leave the field to save this value, the mask displays it as 21/14/95 and ignores the final 4 altogether! What is the 21st month, anyway!?
Press the TAB key to move out of the field and save the value.
A message appears that says your value is not valid. Your date is not a real date and Access recognized that when it tried to save it.
Select the date and press the Delete key to erase the value.
In the Birthdate field type 2/14/1954, including the slashes, starting from the left. Watch the mask as you type!
Whoops. These numbers do not fit the mask either! Typing the slash moved the cursor to the next section of the mask, but only the first two digits of the year fit in the mask.
Press the TAB key to move to the next cell and save the value.
The date is rewritten! Access did not remember the 54 and thought you meant 2019 for the year. Not good!! Even though this one is a legitimate date, its the wrong date.
Let's try again.
The mask requires 2 digits for the month and 2 for the day and 2 for the year.
In the Birthdate field type 021454.
Finally! The date is the one we wanted.
Press the right arrow key to move to the next field.
The field is reformatted, without the leading zero, in Short Date format. This is the Format Property at work!
In this case the difference is subtle, but it causes a big confusion if you need to edit the date.
--------------------------------------------------------------------------------
Using an Input Mask: Edit Value (Date)
When the Format Property formats a value differently from the input mask, trying to make changes can be frustrating! You must enter your values as the mask requires. If the mask does not show, you may have a hard time figuring out what is wrong.
Drag across the 2 in the Birthdate to select it.
Type a 3 and then click in another field to save the change.
A message appears that says your value is not appropriate for the input mask.
Even though the date is being displayed as 2/14/54, to edit it, you must match the mask, not what you see!! Confusing!!!
Try again, replacing the 2 with 03 AND replace 1954 with 54.
TAB or arrow or click on another field to save the change.
The date is changed to 3/14/54.
This is all VERY annoying... and crazy-making if you do not know what is going on!
--------------------------------------------------------------------------------
Create a Custom Input Mask
Access provides a wizard for creating some common input masks plus you can create a custom mask using symbols. Your mask can demand a certain number of characters or allow for fewer. Your mask can require only numbers or only letters letters or it can allow any kind of character.
Now you will see the effect of an input mask when the data does not match.
Input Mask Vs. Existing Data: Creating an input mask does not cause an error if data that is already in the table does not match the mask. Only when you enter or edit data will an error occur.
Switch to the Design view of the Staff table.
Click on the field StateOrProvince and then in the box for the property Input Mask.
Type AA;;@ in the box as the mask definition.
The A means that you are required to enter a letter in this position, instead of a number or another character. There are no literal characters in this mask, so the second section of the definition is blank. The @ symbol will be the placeholder. This is easier to see than an underscore, but more confusing to a new user.
Caption: The value in the Caption property will be the column heading (State/Province) instead of the name of the field (StateOrProvince). With a caption you can use characters that are not allowed in the name of a field, like the slash /character.
Save the table,
Switch back to the Datasheet view.
In the first record, click in the left side of the field State/Province, but not on the divider line. The first placeholder @ is selected.
Is it obvious to you that you are to type two characters only? Probably.
Is it obvious that they must be letters and not numbers or other characters? Probably not!
Problem: Whole cell was selected instead.
What happened: You caught the border of the cell when you clicked.
Solution: Click out of the cell to deselect it and try again.
Problem: The second placeholder was selected instead.
Solution: Press the left arrow key to move left one character.
Problem: No @ symbols show.
Solution: Click out of the cell to deselect it and try again.
ype Tenn, which is a common abbreviation for the state of Tennessee, USA.
Only the first two characters were kept. No error message!
Delete the "Te"
Type tn , which is the actual postal abbreviation for Tennessee, and press TAB to exit the cell.
The two letters remain in lower case even though the mask used a capital A. That capital A does not tell the mask anything about the capitalization of what you type. It means that a letter or digit is required in this position. A lower case a would mean that a letter or digit is acceptable but is not required.
(Yes, I know that the address in this record is in Argentina. We are experimenting here!)
--------------------------------------------------------------------------------
Add Format
Switch back to Table Design view.
In the Format box for the field StateOrProvince, type > to force upper case for this field.
Save the table.
Switch back to Datasheet view.
The text is now in upper case, all capital letters, like the postal service wants.
Of course since there is no state TN in Argentina, we have a little problem to deal with! Unhappily, there is no neat way to use an input mask for this field if you will have addresses from different countries. The same issue keeps us from using an input mask for telephone numbers and postal codes also. The joys of international business!
Switch back to Design view and delete the Format and the Input Mask for the field StateOrProvince.
Database Fields
Fields can be one of a range of different data types, including: Text, Integer, Real number, Yes/No (Boolean), Date, Time, Sound, and Video.
Key fields allow computers to uniquely identify each record. They contain a unique value for each record. The records in a file are usually sorted by the value of the key field, for example, the ProductID in a stock file.
Each field can only hold one data type at any time. e.g. a field for a date will always, and only, hold a date. Fields can be either fixed or variable in length.
Fixed length fields will always use a certain number of characters for each record. Variable length fields only use the minimum number of characters necessary for each record.
Fixed length fields are faster to search through, but variable length fields use less storage space.
--------------------------------------------------------------------------------
Coding Databases
A well-designed database will code data that appears many times, rather than entering full terms.
e.g. in a database that records the gender of a person, it would be sensible to code as "M" or "F" rather than typing "Male" or "Female" each time.
This also allows you to enter data much faster, as well as allowing you to easily validate the input.
--------------------------------------------------------------------------------
Searching Databases
Databases can be searched by creating a query, of which there are two types: simple and complex.
Simple Query
A simple query uses one test on a single field to find records in a database. They are made up of three parts: a field name, an operator, and a value.
Examples of operators are equal to (=), more than (>), less than (<), and not equal to (<>).
An example of a simple query is:
Colour = Red
Complex Query
Complex queries use more than one test, or look at more than one field. They are made up of two or more simple queries joined together by logical operators, such as AND, OR and NOT.
An example of a complex query is:
Colour = Red OR Colour = Blue
--------------------------------------------------------------------------------
Database Summary
Databases consist of 1 or more files (or tables). Files are made up of records.
Records consist of 1 or more fields, basically information about something.
Key fields make a record unique, e.g. a customer number.
Fields contain different data types including Text, Integer, Real number, Yes/No (Boolean), Date, Time, Sound, and Video
Fields can be fixed or variable length. Fixed length fields search faster; variable fields take up less disk space.
Coding data speeds up data entry and helps avoid mistakes. e.g. instead of entering child or adult, code as c and a in the appropriate field.
Databases are searched with simple or complex queries. Simple queries search on a single field. Complex queries can search more than one field on a combination of terms.
--------------------------------------------------------------------------------
Step 1 : Import Text Files (done by Wibunsak and bilal -Year 11 B2)
Step 2 :datatype(done by Wibunsak and bilal -Year 11 B2)
Step 3: Formatting Fields (done by Wibunsak and bilal -Year 11 B2)
Databases
Databases are one of the most powerful tools that people use to help them handle large amounts of information.
A database is made up of one or more related files (or tables in Microsoft Access).
A file is made up of one or more related records.
Records are made up of one or more related fields.
A field is made up of a group of related characters. It is a piece of information about something.
Tables:
Design tables
Set Format for a table field
Apply & use an input mask for a table field
Understand the purpose of indexes
Use data validation for a table field and for a record
Document a table - Relationships, Documenter report, Object Dependencies
Queries:
Create a Select query in Query Design View
Use AND and OR criteria in a query
Use the Like operator
Use totals in a query
Concatenate text fields
Calculate values
Create a parameter query
Create a Make-Table query
Create an Append query
Create a Delete query
Create an Update query
Create a Crosstab query
Document a query - Documenter report, Object Dependencies
Compact and repair a database
Table Design
You want to design your tables so that:
It is easy to enter and update information.
Data is consistent throughout.
There is no wasted disk space.
The database responds quickly to searches.
How does one do that??
General Principles for Table Design:
One item of data goes into only one field in one table.
A table's fields must be logically related to each other.
The order of the fields is not important.
Choose entries from a list or table whenever possible.
Use field validation to make sure that the data is in the right form.
Field size fits the expected size of the data.
Create indexes for fields that you will be searching on often.
Good table design requires some logic and thinking ahead. Are you wasting any disk space? Will the same value, like a company's name, be spelled the same in all records? If the company's name changes, can you update it in just one place? Will your design allow for fast searches? What if there are thousands of records?
Goals for Table Design
Save disk space
Save time and effort
Avoid data errors and inconsistencies
Keep database functions fast
How to accomplish these goals? You will need to follow a few rules and then think ahead about the future needs of your database.
How do you want to see the data in your fields? Do you want dates to show like 1/4/2006 or January 4, 2006? Do you want a phone number to look like 123-555-6789 or 1235556789? Does a text field need to look like abcd or ABCD?
You can make choices like these in the Table Design View. There are two properties that you can use to control how a field's values show, Format and Input Mask.
Inherited: The Format and Input Mask properties also apply to the field when it is part of a query, form, or report. You can make a different choice in the Design View for the query, form, or report.
Using both: If you define both the Format and Input Mask properties for a field, be sure that they do not conflict with each other! You will see the Input Mask only when you are entering or editing data. The Format is applied only after the record is saved
Format Property
Format: Controls how the value is displayed, but does not affect what is actually stored in the database.
The formatting is applied only after the value has been saved.
What are your choices? It depends on the field's type. Many types have a drop list in the Format property box.
--------------------------------------------------------------------------------
Input Mask Property
Input Mask: Controls how you enter or edit the data.
You can use certain symbols to restrict what kinds of characters are allowed and to show how many are allowed. You can save or not save literal characters like hyphens, spaces, parentheses, and periods as part of the value.
Input Masks are used mostly with Text and Date/Time fields, but can also be used in Number or Currency fields.
After the data is saved, the Format property controls what you see. Think carefully before setting both Format and Input Mask properties for the same field.
Parts of an Input Mask: An input mask has 3 sections, separated by a semi-colon (;)
Example: !999(999)000-0000;0;_ for a phone number with country code and area code.
Part 1: Mask itself, like !999(999)000-0000 in the example above for a phone number.
Once you click in the field itself, you would see: (___)___-____
! = Accept typing starting from the right instead of from the left
0 = You must enter a digit for each 0 in the mask.
9 = You can enter a digit in where you see a 9 in the mask, but you do not have to.
Part 2: Save literal characters in the mask? (like the parentheses and hyphen in the example)
0 = Yes, store characters with the data.
1 = No, do NOT store characters with the data.
Part 3: Placeholder: The character to use to show how many characters to enter.
The example above uses an underscore. You can use any character as a placeholder. To not show a placeholder at all, use "" in the third position.
Input Mask Wizard: The wizard offers masks for common fields like US phone numbers, postal codes, social security numbers. You open this wizard from the button with three dots , which appears when you select the Input Mask box for the field.
In the wizard's Try It box you can enter an example value to see if this mask will work well for the values you expect. To use a mask, you must know what to expect!!
Customize the list: The Edit List button opens another dialog that shows the definition of the selected mask. You can enter a new Description and change the mask. Clicking the Close button adds it to the list.
Symbols for custom Input Masks
Step-by-Step: Formatting Fields
--------------------------------------------------------------------------------
What you will learn: to create a table with Table Wizard
to set the Format property for a field
to enter data using an input mask
to edit data in a field with an Input Mask
to create a custom Input Mask for a field
--------------------------------------------------------------------------------
Start with: , Projects database open.
The story so far:
World Travel Inc. wants to include in their database some information about various projects and which staff members are involved with which projects. You designed two tables in the last lesson but now see that you need a Staff table also.
You will create a Staff table using the Table Wizard and then look at Format and Input Mask properties.
Create Staff Table with Table Wizard
If necessary, switch to the Database Window in your Projects database.
Select Tables and click the New button. The New Table dialog appears.
Click on Table Wizard and then on OK.
The Table Wizard dialog appears with a number of pre-designed tables for common situations.
Select Business, if necessary.
Select Employees, from the Sample tables list. A list of fields from this sample table appears in the center.
You do not need all of these fields. You can rename them or add others after creating the initial table.
Using the list below, click a field in the center list of the Table Wizard. Then click the > button to add it to the list on the right, the fields for your new table. The list in the center does not change.
EmployeeID
SocialSecurityNumber
NationalEmplNumber
FirstName
MiddleName
LastName
Title
EmailName
Extension
Address
City
StateOrProvince
Region
PostalCode
Country/Region
HomePhone
BirthDate
SpouseName
EmrcyContactName
EmrcyContactPhone
Notes
Click on Next.
The next step in the wizard asks you about the table's name and primary key .
Change the table's name to Staff.
Choose to set the primary key yourself.
Click on Next.
The next step in the wizard sets the primary key.
Select the field EmployeeID as your primary.
Choose to use consecutive numbers that MS Access automatically assigns as each record is created.
Click on Next.
The next step tries to identify what tables the new one might be related to.
Access cannot see any relationships. You will be making changes, however that will make the new Staff table related to the ProjectStaff table.
Click on the button Relationships in the dialog.
A new window appears with some choices.
Choose the middle item, One record in the Staff table will match many records in the ProjectStaff table.
Click on OK to close the Relationships dialog.
The Table Wizard window now shows that Staff is related to ProjectStaff .
Click on Next.
The next step asks if you are ready to enter data or do you want to modify the design.
Select to Modify the table design.
Click on Finish.
Your new table opens in Design View.
--------------------------------------------------------------------------------
Enter Data
To see how field properties work, you first need a record!
Switch to Datasheet View for the table Staff.
Enter the following as the first record, exactly as shown:
(Leave the other fields blank for now.)
First Name Luis
Middle Name P
Last Name Perez
Title President
Email Name LuisPerez
Extension ViajesMundiales.net
Address Montevideo 6553
City Buenos Aires
Postal Code 1123
Country/Region Argentina
Home Phone +54 (1) 555-1234
Spouse Name Rosa Perez
Emrgcy Contact Name Rosa Perez
Emrgcy Contact Phone +54 (1) 555-1234
Beware of Undo! The Undo command will wipe out the whole record if it has not been saved! After saving, Undo removes all of the changes that you made since saving the record.
To save a record:
Just change to a different record.
Menu: select Records
Save Record.
Keyboard use the key combo SHIFT + ENTER.
Toolbar: click the Save button .
Before leaving a field, pressing the ESC key will return a field to its previous value.
Note: The person's name is in three fields. The email address is in 2 fields.
Note: The phone numbers in Argentina have a different form than in the USA. It's a good thing that the wizard did not apply a Format or Input Mask property for the phone numbers. Depending on the countries that you need to work with, you might want to split each phone number up into parts, like with the email address and name fields.
Widen columns: Drag the right edge of the heading to the right for any fields that are too narrow to show all of the characters.
Click the Save button to save the record.
--------------------------------------------------------------------------------
Format Property: < (forcing lower case)
Since this table will have records involving several countries, you cannot easily apply formatting to postal codes or phone numbers or even addresses. You can use formatting to make sure that the email address parts are all lower case.
Switch to Design View for the table Staff.
Select the field EmailName.
In the Format box, type < .
Repeat for the field Extension.
Save the table.
Switch back to Datasheet View.
Both of the fields are now in all lower case letters.
(Did you widen the Extension field to see all of the characters?)
Experiment by typing various combinations of lower case and upper case letters in these fields and then changing to a different field. The value is shown in lower case only after you leave the field or save the record.
The Format Property does not change what is stored in the field, only the way it is shown.
--------------------------------------------------------------------------------
Using an Input Mask: Enter Value (Date)
The Table Wizard set both the Format and Input Mask Properties for the field, Birthdate. Sometimes these can conflict with each other, or at least be confusing.
Now you will enter a date into the Birthdate field to see how an input mask works. After you save the date, the Format property displays the date in Short Date format.
Remember: Using the Undo command will wipe out the whole record if it has not been saved!
Click in the Birthdate field.
A mask appears - blanks and slash characters. The cursor is a thick black bar, showing where the next character will go.
Problem: Mask does not appear: Did you click in the field or did you use TAB or arrow keys to get into the field? Sometimes moving with the arrow keys or TAB key does not make the mask appear right away.
Solution: Start typing anyway. Once you start typing the mask appears.
In the Birthdate field type February 14, 1954.
Does your typing show at all?? The letters do not! Letters are not allowed by the input mask: 99/99/00;0
Explanation of the mask 99/99/00;0
Where you see 9 in the mask, you can type a digit or a space.
Where you see a 0, you must type a digit, and not a space.
BUT, if you want to type just one digit for a month or day, it must be in place of the second 9, not the first one.
So... you can type: space 5 space 4 6 4 and Access will show the date 5/4/64. But if you type 5 space 4 space 6 4, then Access will show an error message and refuse your date!
Details on custom Input Masks
Problem: February 14, 1954 does show in the field
If you delete a value from a field, you may be able to type values that do not match the input mask... but the value will not be accepted.
If you save the value by moving to a different field or saving the record, then a message appears that says that the value is not appropriate for the input mask.
Solution: Click on OK and then delete the date. Try again.
Click in the Birthdate field and type 2141954.
Before you leave the field to save this value, the mask displays it as 21/14/95 and ignores the final 4 altogether! What is the 21st month, anyway!?
Press the TAB key to move out of the field and save the value.
A message appears that says your value is not valid. Your date is not a real date and Access recognized that when it tried to save it.
Select the date and press the Delete key to erase the value.
In the Birthdate field type 2/14/1954, including the slashes, starting from the left. Watch the mask as you type!
Whoops. These numbers do not fit the mask either! Typing the slash moved the cursor to the next section of the mask, but only the first two digits of the year fit in the mask.
Press the TAB key to move to the next cell and save the value.
The date is rewritten! Access did not remember the 54 and thought you meant 2019 for the year. Not good!! Even though this one is a legitimate date, its the wrong date.
Let's try again.
The mask requires 2 digits for the month and 2 for the day and 2 for the year.
In the Birthdate field type 021454.
Finally! The date is the one we wanted.
Press the right arrow key to move to the next field.
The field is reformatted, without the leading zero, in Short Date format. This is the Format Property at work!
In this case the difference is subtle, but it causes a big confusion if you need to edit the date.
--------------------------------------------------------------------------------
Using an Input Mask: Edit Value (Date)
When the Format Property formats a value differently from the input mask, trying to make changes can be frustrating! You must enter your values as the mask requires. If the mask does not show, you may have a hard time figuring out what is wrong.
Drag across the 2 in the Birthdate to select it.
Type a 3 and then click in another field to save the change.
A message appears that says your value is not appropriate for the input mask.
Even though the date is being displayed as 2/14/54, to edit it, you must match the mask, not what you see!! Confusing!!!
Try again, replacing the 2 with 03 AND replace 1954 with 54.
TAB or arrow or click on another field to save the change.
The date is changed to 3/14/54.
This is all VERY annoying... and crazy-making if you do not know what is going on!
--------------------------------------------------------------------------------
Create a Custom Input Mask
Access provides a wizard for creating some common input masks plus you can create a custom mask using symbols. Your mask can demand a certain number of characters or allow for fewer. Your mask can require only numbers or only letters letters or it can allow any kind of character.
Now you will see the effect of an input mask when the data does not match.
Input Mask Vs. Existing Data: Creating an input mask does not cause an error if data that is already in the table does not match the mask. Only when you enter or edit data will an error occur.
Switch to the Design view of the Staff table.
Click on the field StateOrProvince and then in the box for the property Input Mask.
Type AA;;@ in the box as the mask definition.
The A means that you are required to enter a letter in this position, instead of a number or another character. There are no literal characters in this mask, so the second section of the definition is blank. The @ symbol will be the placeholder. This is easier to see than an underscore, but more confusing to a new user.
Caption: The value in the Caption property will be the column heading (State/Province) instead of the name of the field (StateOrProvince). With a caption you can use characters that are not allowed in the name of a field, like the slash /character.
Save the table,
Switch back to the Datasheet view.
In the first record, click in the left side of the field State/Province, but not on the divider line. The first placeholder @ is selected.
Is it obvious to you that you are to type two characters only? Probably.
Is it obvious that they must be letters and not numbers or other characters? Probably not!
Problem: Whole cell was selected instead.
What happened: You caught the border of the cell when you clicked.
Solution: Click out of the cell to deselect it and try again.
Problem: The second placeholder was selected instead.
Solution: Press the left arrow key to move left one character.
Problem: No @ symbols show.
Solution: Click out of the cell to deselect it and try again.
ype Tenn, which is a common abbreviation for the state of Tennessee, USA.
Only the first two characters were kept. No error message!
Delete the "Te"
Type tn , which is the actual postal abbreviation for Tennessee, and press TAB to exit the cell.
The two letters remain in lower case even though the mask used a capital A. That capital A does not tell the mask anything about the capitalization of what you type. It means that a letter or digit is required in this position. A lower case a would mean that a letter or digit is acceptable but is not required.
(Yes, I know that the address in this record is in Argentina. We are experimenting here!)
--------------------------------------------------------------------------------
Add Format
Switch back to Table Design view.
In the Format box for the field StateOrProvince, type > to force upper case for this field.
Save the table.
Switch back to Datasheet view.
The text is now in upper case, all capital letters, like the postal service wants.
Of course since there is no state TN in Argentina, we have a little problem to deal with! Unhappily, there is no neat way to use an input mask for this field if you will have addresses from different countries. The same issue keeps us from using an input mask for telephone numbers and postal codes also. The joys of international business!
Switch back to Design view and delete the Format and the Input Mask for the field StateOrProvince.
Database Fields
Fields can be one of a range of different data types, including: Text, Integer, Real number, Yes/No (Boolean), Date, Time, Sound, and Video.
Key fields allow computers to uniquely identify each record. They contain a unique value for each record. The records in a file are usually sorted by the value of the key field, for example, the ProductID in a stock file.
Each field can only hold one data type at any time. e.g. a field for a date will always, and only, hold a date. Fields can be either fixed or variable in length.
Fixed length fields will always use a certain number of characters for each record. Variable length fields only use the minimum number of characters necessary for each record.
Fixed length fields are faster to search through, but variable length fields use less storage space.
--------------------------------------------------------------------------------
Coding Databases
A well-designed database will code data that appears many times, rather than entering full terms.
e.g. in a database that records the gender of a person, it would be sensible to code as "M" or "F" rather than typing "Male" or "Female" each time.
This also allows you to enter data much faster, as well as allowing you to easily validate the input.
--------------------------------------------------------------------------------
Searching Databases
Databases can be searched by creating a query, of which there are two types: simple and complex.
Simple Query
A simple query uses one test on a single field to find records in a database. They are made up of three parts: a field name, an operator, and a value.
Examples of operators are equal to (=), more than (>), less than (<), and not equal to (<>).
An example of a simple query is:
Colour = Red
Complex Query
Complex queries use more than one test, or look at more than one field. They are made up of two or more simple queries joined together by logical operators, such as AND, OR and NOT.
An example of a complex query is:
Colour = Red OR Colour = Blue
--------------------------------------------------------------------------------
Database Summary
Databases consist of 1 or more files (or tables). Files are made up of records.
Records consist of 1 or more fields, basically information about something.
Key fields make a record unique, e.g. a customer number.
Fields contain different data types including Text, Integer, Real number, Yes/No (Boolean), Date, Time, Sound, and Video
Fields can be fixed or variable length. Fixed length fields search faster; variable fields take up less disk space.
Coding data speeds up data entry and helps avoid mistakes. e.g. instead of entering child or adult, code as c and a in the appropriate field.
Databases are searched with simple or complex queries. Simple queries search on a single field. Complex queries can search more than one field on a combination of terms.
--------------------------------------------------------------------------------
Thursday, November 19, 2009
Information Computer Technology
Welcome to the ICT link.This page is designed to act as a portal to enable you to find information you need.If you are looking for links to worksheets,home work,past papers.... Please visit ICT Resources link on the right.
Subscribe to:
Posts (Atom)