Transcript BC_07_January_2003

Happy New Year
Databases
January 7 2003
One of the tools to treat information
• To present information :
– Word and PowerPoint
• 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 PowerPoint
• 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/session10
The tables of bdcomptoir
• The information presented in this example is quite
typical of what a sales dept wants to monitor :
–
–
–
–
–
–
–
–
Orders
Orders details (components)
Suppliers
Products
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
• Expr1: [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 (20026FF) to 11032 (32937,5FF)
• This gives a detail of the 706 983,67 FF