The purpose of this set of web pages is to demonstrate knowledge of building Microsoft Excel based solutions, data structure design and live querying on data tables (MySQL and MS SQL).
Using the previous example where the three tables were joined together using an inner join, the data output can be improved upon to make more information fit on the page, by concatenating the output. In the results below, there is no need to have separate columns for first names and surnames so these have been concatenated.
It is possible to record the data within one field within the database, but it is prudent to keep the source data separate as it allows the flexibility to produce a mailmerge at a later date.
As the data in fields trans_cust_id and cust_id are both identical due to the matching performed on the join, The cust_id field has been removed to save space.
The code within the query is as follows, complete with 'c' and 's' in use as an alias for the respective Customer and Staff tables:
MySQL Code
CONCAT(c.cust_salutation, ' ',c.cust_firstname,' ',c.cust_surname) AS cust_fullname
CONCAT(s.staff_salutation,' ',s.staff_firstname,' ',s.staff_surname) AS staff_fullname
If the database being used was an MS-SQL database, this would be achieved as follows:
T-SQL (MS SQL)
c.cust_salutation + ' ' + c.cust_firstname + ' ' + c.cust_surname AS cust_fullname,
s.staff_salutation + ' ' + s.staff_firstname + ' ' + s.staff_surname AS staff_fullname
Live results from the MySQL database are below:
| trans_cust_id | cust_name | cust_fullname | trans_grn_widgets_sales | trans_blu_widgets_sales | staff_fullname |
|---|---|---|---|---|---|
| 4589 | Wilton's Fine Products Ltd | Mr Will Wilton | 500.00 | 0.00 | Mrs Anthea Allen |
| 4583 | Quentin's Cabins Ltd | Mr Quentin Quinn | 500.00 | 1800.00 | Mr Billy Beaumont |
| 4568 | Billingham's of Braithwaite Ltd | Mr Bertie Billingham | 1000.00 | 0.00 | Mr Charles Chuzzlewit |
| 4570 | Dimbleby PLC | Miss Diana Dimbleby | 2000.00 | 4500.00 | Miss Deirdrie Dawson |
| 4582 | Peacock & Bradshaw Ltd | Mr Peter Peacock | 2500.00 | 3000.00 | Mrs Francesca Faversham |
| 4582 | Peacock & Bradshaw Ltd | Mr Peter Peacock | 1000.00 | 1800.00 | Miss Gina Ginola |
| 4587 | United Uniforms Ltd | Ms Una Unsworth | 1500.00 | 600.00 | Mrs Anthea Allen |
| 4589 | Wilton's Fine Products Ltd | Mr Will Wilton | 1500.00 | 600.00 | Mr Billy Beaumont |
| 4582 | Peacock & Bradshaw Ltd | Mr Peter Peacock | 2000.00 | 600.00 | Mr Charles Chuzzlewit |
| 4568 | Billingham's of Braithwaite Ltd | Mr Bertie Billingham | 2000.00 | 600.00 | Miss Deirdrie Dawson |
| 4568 | Billingham's of Braithwaite Ltd | Mr Bertie Billingham | 1500.00 | 1200.00 | Mrs Francesca Faversham |
| 4618 | Zen Products Ltd | Mr Zane Zellweger | 1000.00 | 0.00 | Mr Billy Beaumont |
| 4567 | Acorn Traders Ltd | Mrs Anthea Adams | 3500.00 | 1200.00 | Miss Gina Ginola |
| 4583 | Quentin's Cabins Ltd | Mr Quentin Quinn | 1500.00 | 3600.00 | Mrs Anthea Allen |
| 4584 | Rena's Supplies Ltd | Mrs Rena Rimshaw | 1000.00 | 4000.00 | Mr Billy Beaumont |
| 4583 | Quentin's Cabins Ltd | Mr Quentin Quinn | 2500.00 | 600.00 | Mr Charles Chuzzlewit |
| 4585 | Shenley Shovels Ltd | Mr Simon Sixsmith | 3000.00 | 3500.00 | Miss Deirdrie Dawson |
| 4616 | X-cel Ltd | Mrs Xaria Xanda | 2000.00 | 3000.00 | Mrs Francesca Faversham |
| 4617 | Yeovil Yards Ltd | Mr Yusef Yorke | 3000.00 | 3600.00 | Miss Gina Ginola |
| 4618 | Zen Products Ltd | Mr Zane Zellweger | 1500.00 | 1800.00 | Mrs Anthea Allen |
| 4587 | United Uniforms Ltd | Ms Una Unsworth | 1000.00 | 2400.00 | Mr Billy Beaumont |
| 4589 | Wilton's Fine Products Ltd | Mr Will Wilton | 3300.00 | 1200.00 | Mr Charles Chuzzlewit |
| 4567 | Acorn Traders Ltd | Mrs Anthea Adams | 1200.00 | 0.00 | Mr Billy Beaumont |
| 4570 | Dimbleby PLC | Miss Diana Dimbleby | 1500.00 | 600.00 | Miss Deirdrie Dawson |
| 4571 | East Empire Ltd | Ms Elaine East | 3000.00 | 600.00 | Mr Charles Chuzzlewit |
| 4572 | Frannock Manor PLC | Mr Frederick Franklin | 2000.00 | 0.00 | Miss Deirdrie Dawson |
| 4573 | Gimley of Gosborton Ltd | Mr George Gimley | 3000.00 | 1200.00 | Mrs Francesca Faversham |
| 4585 | Shenley Shovels Ltd | Mr Simon Sixsmith | 1000.00 | 1800.00 | Mrs Anthea Allen |
| 4582 | Peacock & Bradshaw Ltd | Mr Peter Peacock | 1500.00 | 2400.00 | Mr Billy Beaumont |
| 4618 | Zen Products Ltd | Mr Zane Zellweger | 1000.00 | 3000.00 | Mr Billy Beaumont |
| 4601 | Irlington Industries Ltd | Mr Ian Icke | 1500.00 | 3000.00 | Mr Charles Chuzzlewit |
| 4602 | Jeff Johnston Ltd | Mr Jeff Johnston | 1000.00 | 1200.00 | Miss Deirdrie Dawson |
| 4603 | Kensome & Kingston Ltd | Mrs Karen Kensome | 1500.00 | 3000.00 | Mr Ernie Ecclestone |
| 4616 | X-cel Ltd | Mrs Xaria Xanda | 2000.00 | 1200.00 | Mrs Francesca Faversham |
| 4582 | Peacock & Bradshaw Ltd | Mr Peter Peacock | 500.00 | 2400.00 | Miss Gina Ginola |
| 4583 | Quentin's Cabins Ltd | Mr Quentin Quinn | 500.00 | 3600.00 | Mr Harry Harrison |
| 4616 | X-cel Ltd | Mrs Xaria Xanda | 2500.00 | 600.00 | Mrs Anthea Allen |
| 4572 | Frannock Manor PLC | Mr Frederick Franklin | 2500.00 | 600.00 | Mr Billy Beaumont |
| 4570 | Dimbleby PLC | Miss Diana Dimbleby | 2000.00 | 600.00 | Mr Billy Beaumont |
| 4585 | Shenley Shovels Ltd | Mr Simon Sixsmith | 2000.00 | 2400.00 | Mr Charles Chuzzlewit |
| 4617 | Yeovil Yards Ltd | Mr Yusef Yorke | 1500.00 | 3000.00 | Miss Deirdrie Dawson |
| 4567 | Acorn Traders Ltd | Mrs Anthea Adams | 1500.00 | 4200.00 | Mr Ernie Ecclestone |
| 4571 | East Empire Ltd | Ms Elaine East | 1500.00 | 1800.00 | Mrs Francesca Faversham |
| 4572 | Frannock Manor PLC | Mr Frederick Franklin | 1500.00 | 1800.00 | Miss Gina Ginola |
| 4591 | Yaxley Pies Ltd | Miss Yasmine Yolander | 1000.00 | 2400.00 | Mr Harry Harrison |
| 4592 | Zimmerman's of Sawtry Ltd | Mr Zak Zanetti | 1000.00 | 4800.00 | Mr Harry Harrison |
| 4618 | Zen Products Ltd | Mr Zane Zellweger | 2000.00 | 3400.00 | Mrs Anthea Allen |
| 4601 | Irlington Industries Ltd | Mr Ian Icke | 1500.00 | 3550.00 | Mr Billy Beaumont |
| 4602 | Jeff Johnston Ltd | Mr Jeff Johnston | 1500.00 | 3000.00 | Mr Charles Chuzzlewit |
| 4603 | Kensome & Kingston Ltd | Mrs Karen Kensome | 1500.00 | 3000.00 | Mr Charles Chuzzlewit |
| 4583 | Quentin's Cabins Ltd | Mr Quentin Quinn | 1500.00 | 2400.00 | Miss Deirdrie Dawson |
| 4585 | Shenley Shovels Ltd | Mr Simon Sixsmith | 2000.00 | 2400.00 | Mr Ernie Ecclestone |
| 4617 | Yeovil Yards Ltd | Mr Yusef Yorke | 2000.00 | 2400.00 | Mrs Francesca Faversham |
| 4616 | X-cel Ltd | Mrs Xaria Xanda | 2500.00 | 2400.00 | Miss Gina Ginola |
| 4571 | East Empire Ltd | Ms Elaine East | 2500.00 | 3600.00 | Mr Harry Harrison |
| 4611 | Sally's Supplies Ltd | Mrs Sally Smithers | 1500.00 | 3600.00 | Mrs Anthea Allen |
| 4612 | Thompson's Tents Ltd | Mr Toby Thompson | 1500.00 | 3600.00 | Mr Billy Beaumont |
| 4613 | Ursula Umbridge Ltd | Mrs Ursula Umbridge | 1500.00 | 3600.00 | Mr Charles Chuzzlewit |
| 4603 | Kensome & Kingston Ltd | Mrs Karen Kensome | 1500.00 | 1800.00 | Mr Charles Chuzzlewit |
| 4616 | X-cel Ltd | Mrs Xaria Xanda | 2000.00 | 1800.00 | Miss Deirdrie Dawson |
| 4614 | Violet Vans Ltd | Mrs Violet Vardy | 2000.00 | 1800.00 | Mr Ernie Ecclestone |
| 4572 | Frannock Manor PLC | Mr Frederick Franklin | 1500.00 | 1800.00 | Mrs Francesca Faversham |
| 4603 | Kensome & Kingston Ltd | Mrs Karen Kensome | 1500.00 | 1800.00 | Miss Gina Ginola |
| 4571 | East Empire Ltd | Ms Elaine East | 1500.00 | 1800.00 | Mr Harry Harrison |
| 4601 | Irlington Industries Ltd | Mr Ian Icke | 1000.00 | 3000.00 | Miss Gina Ginola |
| 4602 | Jeff Johnston Ltd | Mr Jeff Johnston | 1000.00 | 3000.00 | Mr Harry Harrison |
| 4603 | Kensome & Kingston Ltd | Mrs Karen Kensome | 1000.00 | 3000.00 | Mrs Francesca Faversham |
| 4583 | Quentin's Cabins Ltd | Mr Quentin Quinn | 1000.00 | 2400.00 | Mr Ernie Ecclestone |
| 4585 | Shenley Shovels Ltd | Mr Simon Sixsmith | 1500.00 | 2400.00 | Mr Ernie Ecclestone |
| 4612 | Thompson's Tents Ltd | Mr Toby Thompson | 1500.00 | 1800.00 | Miss Deirdrie Dawson |
| 4616 | X-cel Ltd | Mrs Xaria Xanda | 1500.00 | 1800.00 | Mr Charles Chuzzlewit |
| 4617 | Yeovil Yards Ltd | Mr Yusef Yorke | 1500.00 | 1800.00 | Mrs Anthea Allen |
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.