MGMT2006: Management Information Systems - Database Analysis Homework

This assignment solution addresses a Management Information Systems (MIS) problem for MGMT HARDWARE, a retail chain. It focuses on database management using MS Access, including the creation of various queries (Q1-Q5) to extract and analyze data related to item categories, purchase orders, vendor items, quarterly sales, and code violations. The solution also involves generating reports using Power BI, including column charts, pie charts, multi-row cards, and card visualizations, to present sales data, item counts, and key performance indicators. The document also explores the integration of Excel data into Access, setting up primary keys, and establishing relationships between tables. The Power BI section details the creation of interactive dashboards and data discovery, and it concludes with a bibliography of relevant sources.

Running head: MGMT2006 – Management Information Systems
MGMT2006 – Management Information Systems
Name of the Student
Name of the University
Author Note

MGMT2006 – Management Information Systems 1
Introduction and Background:
MGMT HARDWARE, runs a chain of 14 buildings that supplies stores that are
located in 4 of the Caribbean countries. The organisation caters for the wide set of items as
well as wide set of services that include home delivery, building materials, household
materials, online ordering, retail sales, drive through sales and order online pickup in store
sales. The organisation is having an ecommerce website that is centrally branded which caters
to the requirements of the all 4 countries as well as then presents a feel and consistent look to
all the consumers. The locations that are situated within the country can connect over the
Metro-E wide area network (WAN) that are provided by FLOW. However, there is no such
connectivity for country to country. It is because the organisation grew by the acquisitions
and mergers, not in all the locations utilize the similar ERP (Enterprise Resource Planning)
software. Each of the stores are having an offline server which can ensure about the location
is able to still sell and receive the stock whenever the WAN goes down.
Some reports needs to be produced that are relate to sales and stocks that is for an upcoming
tradeshow.
MS access:
DBMS means Database management system which is a Microsoft access. It's comes from
Microsoft which combines the software and a graphical user interface with the relational
Microsoft jet database engine with the development tools. Database management system is a
part of the Microsoft office, which was suite of applications. It is sold separately and also
included higher editions and professional. In this system Microsoft access can stores data,
and it's based on privet format mode, which access the jet database engine. In this system it
can also link directly or import to stored data in databases and other applications. Application
software was developed to access the Microsoft, which can use the data architects, software

2MGMT2006 – Management Information Systems
developers, and power users. VBA (visual basic for applications) is supported to access the
Microsoft office applications, which based on an object that can programming the language
to including the legacy of Data Access Objects (DAO) for reference the variety of objects. In
this system it gets many other components of ActiveX and mainly the ActiveX data objects.
In the VBA (Visual basic for applications) programing environment visual objects can used
for exposed the reports and forms in the properties and methods. Windows operating system
operations which was declare or call as VBA code modules.
Importing excel data to access:
Setting up primary keys:

3MGMT2006 – Management Information Systems
Relationship:
Queries:
Q1-Cat10Items:
SELECT Items.[Item No], Items.Descr, Items.Cat, Items.[Sub Cat], Items.[Selling Price],
Items.[Average Cost]
FROM Items
WHERE (((Items.Cat)="where")) OR (((Items.Cat) Like "10"))
ORDER BY Items.Cat;

4MGMT2006 – Management Information Systems
Q1-Cat10Items
Item No Descr Cat Sub Cat Selling Price Average Cost
1006992 CLIP W/BLK INSULATOR 10A SLO 10 1036 5.95 2.55
1007003 ELECTRICAL CLIP STL 5A 50 DNR 10 1036 4.25 2.51
1007002 ELECTRICAL CLIP 5A COPPER DNR 10 1036 4.25 2.50
1006997 INSULATOR 10A RED W/SCREW DNR 10 1064 5.95 2.55
1006996 CLIP W/RED INSUL COPPER AL DNR 10 1036 8.50 3.59
1006995 CLIP W/BLK INSUL 10A COPPE DNR 10 1036 5.30 3.90
1006492 BUTT CONNECTOR 22-18W 50035 50 10 1050 1.50 0.68
1006993 CLIP W/SCREW 10A COPPER 50 SLO 10 1036 4.25 2.66
1057401 MALE FORK TERM 16-14W (DNR) 10 1050 1.45 0.58
1006569 Y CONNECTOR 16-14W 50035 DNR 10 1050 2.50 0.78
1006568 Y CONNECTOR 22-18W 50035 DNR 10 1050 2.95 1.17
1006564 QUICK COUPLER 16-14W MALE 5003 10 1050 1.25 0.49
1006561 QUICK COUPLER 50044 50035J 163 10 1050 2.75 1.17
1006494 BUTT CONNECTOR 12-10W 50035 50 10 1050 0.75 0.30
1006493 BUTT CONNECTOR 16-14W 50035 50 10 1050 0.70 0.35
1006994 CLIP W/SCREW 10A STL 50250 DNR 10 1036 3.95 1.75
1057413 RING TERM 22-18W 3/8STUD SLO 10 1050 1.00 0.32
1057432 FLAG TERM 14W 1/4" 50018 50020 10 1050 3.85 1.56
1057429 RING TERM 12-10W 1/4STUD 50001 10 1050 1.95 0.78
1057423 RING TERM 16-14W 4-6STUD SLO 10 1050 0.70 0.30
1057419 FLAG TERM 18W 1/4 50018 50019A 10 1050 3.85 1.55
1057418 RING TERM 12-10W 1/2STUD SLO 10 1050 1.50 0.68
1057416 RING TERM 8W 3/8STUD 50001 500 10 1050 2.22 1.27
1007027 ELECTRICAL CLIP STL 5A M/G DNR 10 1036 2.75 1.17
1057414 RING TERM 16-14W 3/8STUD SLO 10 1050 1.00 0.46
1016207 FUSE 2A PF2 10 1057 4.95 0.38
1057411 RING TERM 50001D 58D 50005 10 1050 0.70 0.30
1057410 RING TERM 8W 1/4STUD 50001 500 10 1050 2.75 1.17
1057407 MALE TERM 16-14W 1/4" DNR 10 1050 1.00 0.46
1057406 BULLET RECEPTACLE 18-14W (DNR) 10 1050 1.45 0.58
1057405 MALE BULLET TERM 18-14W (DNR) 10 1050 1.00 0.46
1057437 FEMALE TERM 16-14W 1/4" 50018 10 1050 1.00 0.79
1057415 RING TERM 12-10W 3/8STUD SLO 10 1050 1.70 0.81
Q2-NotOnPO:

5MGMT2006 – Management Information Systems
SELECT Purchases.[Lst Rcvd Date], Items.[Item No], Items.Descr, Purchases.[Qty on Hand]
FROM Items INNER JOIN Purchases ON Items.[Item No] = Purchases.[Item No]
ORDER BY Purchases.[Qty on Hand];

6MGMT2006 – Management Information Systems
Q3VenItem50035Group:
SELECT Items.[Item No], Items.Descr, VenItems.Vendor, Purchases.[Lst Rec Cost],
Purchases.[Lst Rcvd Qty]
FROM (Items INNER JOIN Purchases ON Items.[Item No] = Purchases.[Item No]) INNER
JOIN VenItems ON Purchases.[Item No] = VenItems.[Item No];

7MGMT2006 – Management Information Systems
Q4QuarterlySummary:
SELECT Items.[Item No], Items.Descr, Purchases.[Qty on Hand], SalesHistory.Qrt, Items.
[Average Cost]
FROM SalesHistory, Items INNER JOIN Purchases ON Items.[Item No] = Purchases.[Item
No];
Q5–CodeViolation:

8MGMT2006 – Management Information Systems
SELECT Items.[Item No], Items.Descr, Items.Cat, Items.[Sub Cat], Items.[Selling Price],
Items.[Average Cost], Purchases.[Item No], Purchases.[Qty on Hand], Purchases.[Qty on
PO], Purchases.[Lst Rec Cost], Purchases.[Lst Rcvd Date], Purchases.[Lst Rcvd Qty]
FROM Items INNER JOIN Purchases ON Items.[Item No] = Purchases.[Item No]
WHERE (((Items.Cat)="where" Or (Items.Cat) Like "20"));
Report:

9MGMT2006 – Management Information Systems
Column chart:

10MGMT2006 – Management Information Systems

11MGMT2006 – Management Information Systems
SELECT Items.Cat, SalesHistory.[Year-Prd]
FROM Items, SalesHistory
WHERE (((Items.Cat)="10" Or (Items.Cat)="21" Or (Items.Cat)="25" Or
(Items.Cat)="30"));
Switch board:
Power BI:
In Microsoft this is a business analytics service which called the name of Power BI.
It's main target to provide business intelligence and interactive visualizations to create their
dashboards and own reports with the capabilities to interface simple enough for end users. In
this system cloud based BI services which provides by Power BI as also known to Power BI
Services, also which was based on desktop interface as called Power BI Desktop. In Power
BI it's including the data preparation, interactive dashboards and data discovery, which can
offer the capabilities of data warehouse in march 2016. Also Microsoft published an
additional service that's embedded on the azure cloud platform which called as name of
Power BI. The main deference of the product is able to load the custom visualizations.

12MGMT2006 – Management Information Systems
At Microsoft the application was originally imagined by Amir Net z and Thierry D'Hers of
the SQL server reporting services team. It was originally diagramed in summer of 2010 by
Ron George and give this diagramed name as project crescent. On July 1, 2011 the project
crescent was available for public download with SQL server Codename Denali. In September
2013 it's renamed to Power BI by Microsoft.
Clustered column chart:
Pie chart:

13MGMT2006 – Management Information Systems
Multi-row card:

14MGMT2006 – Management Information Systems
Card (WHO):

15MGMT2006 – Management Information Systems
Card (Item number):
One item Description:

16MGMT2006 – Management Information Systems
Full view:
The clustered column chart is configured with Qty (sales history table) and Year-prd. How
ever the day is not included in the chart. A pie chart has also been created and that is having
qty and quarter. Then there is a mutli row card that is having Description, Selling Price,

17MGMT2006 – Management Information Systems
Quantity on Hand, Last Received Cost and Last Received Quantity. A new measure has been
created for the purchase table (sum(Purchases[Qty on Hand]) / (sum(SalesHistory[Qty]) / 365
* 7). In a card WHO result is showing and in another card the count items is showing.

18MGMT2006 – Management Information Systems
Bibliography:
Lassmann, H. (2017). Targets of therapy in progressive MS. Multiple Sclerosis
Journal, 23(12), 1593-1599.
Rad, R. (2018). Power BI Components. In Pro Power BI Architecture (pp. 3-15). Apress,
Berkeley, CA.
Rad, R. (2018). Pro Power BI Architecture: Sharing, Security, and Deployment Options for
Microsoft Power BI Solutions. Apress.
Riemenschneider, M., Hvid, L. G., Stenager, E., & Dalgas, U. (2018). Is there an overlooked
“window of opportunity” in MS exercise therapy? Perspectives for early MS
rehabilitation. Multiple Sclerosis Journal, 24(7), 886-894.