Happy New Year Databases January 7 2003 One
Happy New Year Databases January 7 2003
One of the tools to treat information • To present information : – Word and Power. Point • To make calculations and draw graphics : – Excel • To store, treat and retreive (according to queries) : – Access
Businesses make a heavy use of information : • To communicate with other people : – Word and Power. Point • To treat information, to analyse information : – Excel • To interact with machines and with « systems » : – Access
Businesses • A business is an organisation to produce Products or Services • It needs to monitor all sorts of parameters (and to communicate some of them) • We shall be concerned with monitoring parameters which are in the form of structured information : hence ACCESS
Examples • Parameters related to machines : – Production data : piece per machine, piece per hour, downtime in the shopfloor, etc. • Parameters related to people : – Employee information (presence, absence, skills, assignments, etc. ) • Parameters related to sales : – Orders, products, date, clients, amount, etc.
« The daily report » • When in charge of a plant, we have every morning a report of the performance of the plant the day before : – Production data – Problems with raw material supply – Productivity data (hours/product) – Use of fluids (water, electricity, any production mean…)
« The daily report » 2 • The same is true of any other team with a manager : – A sales team (in charge of a particular set of clients) – A team of people in the accounting dept – A team of people in the store rooms – A team of people in the shipping & handling dept
The use of information • A first responsibility is « product manager » – We monitor and take decision concerning : • Packaging, prices, specifications, • Clients relationships • Procter & Gamble is a good example of a « marketing driven » organisation – The divisions correspond to products : detergents, health, food • Some other corporations are split functionally : – Manufacturing, Distribution, Finance, HR, etc. • Get the annual reports of P&G, Nestlé, Ford, Renault, Toshiba (for instance) and read them.
A look at an example in the sales division • The bdcomptoir. mdb • http: //lapasserelle. com/escem/20012002_business_computing/session 10
The tables of bdcomptoir • The information presented in this example is quite typical of what a sales dept wants to monitor : – – – – Orders details (components) Suppliers Product categories Clients Employees Messengers (people in charge of shipping)
Categories and products • 8 categories of products (77 products) : – – – – Sea food : 12 products Drinks : 12 products Desserts : 13 products Meat : 6 products Dairy (milk) : 10 products Cereals : 7 products Dry food : 5 products Condiments : 12 products
Use filters • Filters are the most basic questions we can ask a database • Filter by selection • Filter by form (it shows some bugs)
How many orders by… • How many orders were treated by Anne Dodsworth ? • (We have 9 employees. For each of them we have personal informa tion. ) • Anne Dodsworth treated 43 of the 830 orders recorded.
Caveat • It is very easy to spoil the data by a mistaken manipulation • Access does not ask for much confirmation of entries (it has to do with the weight of tables)
Sum of the orders of… • What quantity of the product ‘Guaraná Fantástica’ was altogether ordered ? • We shall use a query. A query is a question asked to the database (more elaborate than a simple filter). • Query = Requête = Question • We shall work with the tables « products » and « detailed orders »
Query construction • Query tab • New & Design view • Or Create a new query in creation mode
Query construction 2 • We shall « sort » our database to work only on the product « Guarana fantastica » appearing in elements of orders (the table « detail commandes » ) • And we shall sum up the quantity field of each relevant detail commandes.
The logic of the database bdcomptoir. mdb • It is a database about the orders processed by a food wholesaler over 3 years • There are 830 orders • An order is made of several lines (so many « chai » so many « salamies » , etc. ) • There is a more detail table : « detail commande » • The table « detail commande » is the corner stone of our database
The logic of the database bdcomptoir. mdb 2 • The building blocks of the database are the 2155 lines of command (order elements) • They relate on the one hand to orders, to clients, to employees, and to messengers • They relate on the other hand to products, to categories of products, and (of course) to suppliers.
Guarana fantastica • Over the three years of data gathered into the database, 1125 units of « guarana fantastica » were ordered. • They concerned 51 of the 2155 order building blocks
Guarana fantastica 2 • The biggest order was 110 • It was in order n° 10895 • It is an order from Ernst Handel of the 15 feb 1995, etc.
Let’s study the sales of Côte de Blaye • The total sales were over 3 years were • 706 983, 6 FF • Use the magic wand • Expr 1: [Détails commandes]![Prix unitaire]*[Détails commandes]![Quantité]*(1[Détails commandes]![Remise (%)])
Let’s study the sales of Côte de Blaye 2 • It appeared in 24 of the 2155 building blocks of the 830 orders (we obtain them by including a new degrouping in the query, for instance order ref. ) • From 10329 (20026 FF) to 11032 (32937, 5 FF) • This gives a detail of the 706 983, 67 FF
- Slides: 23