Microsoft Access Application Redesign

HickersonTransportationV3.1-Copy.accdb

Home >Computer Science homework help >Microsoft Access Application Redesign

Customer_ID	Company_Name	Vendor_Number	First_Name	Last_Name	Email	Phone_Number	Address	City	State	ZIP	Shipping_History	Shipping_Preferences
1	ABC Corp	VN001	John	Doe	[email protected]	123-456-7890	123 Main St	Cityville	CA	12345	Shipment 1	Express Shipping
2	XYZ Inc	VN002	Jane	Smith	[email protected]	987-654-3210	456 Oak St	Townsville	NY	54321	Shipment 2	Standard Shipping
3	LMN Corporation	VN003	Bob	Johnson	[email protected]	555-123-4567	789 Maple St	Villagetown	TX	67890	Shipment 3	Express Shipping
4	PQR Ltd	VN004	Sara	Williams	[email protected]	111-111-1111	101 Pine St	Cityville	CA	12345	Shipment 4	Standard Shipping
5	KLM Inc	VN007	Daniel	Taylor	[email protected]	777-888-9999	202 Elm St	Villagetown	TX	67890	Shipment 7	Standard Shipping

Driver_ID	First_Name	Last_Name	Hire_Date	DL_Class	DL_State	DL_Number	DL_Expiration	Endorsements	Phone_Number	Address	City	State	Zip	Medical_Certification	Medical_Expiration	Service_Hours	Training_Records	Performance_Metrics
1	Alice	Smith	2022-01-15	Class A	CA	DL123456	2024-01-15	Tanker	987-654-3210	456 Oak St	Townsville	CA	54321	true	2023-01-15	100	Training 1	Good performance
2	David	Brown	2022-02-01	Class B	NY	DL987654	2023-02-01	Hazmat	222-333-4444	555 Elm St	Villagetown	TX	67890	true	2023-02-01	150	Training 2	Satisfactory performance
3	Emily	Jones	2022-03-15	Class C	CA	DL789012	2024-03-15	None	333-444-5555	777 Oak St	Townsville	NY	54321	false		80	Training 3	Average performance
4	Michael	Miller	2022-01-10	Class A	TX	DL345678	2023-01-10	Tanker, Doubles/Triples	444-555-6666	888 Maple St	Cityville	CA	12345	true	2023-01-10	120	Training 4	Excellent performance
5	William	Lopez	2022-03-01	Class A	CA	DL345678	2023-03-01	Hazmat	888-999-0000	707 Pine St	Cityville	CA	12345	true	2023-03-01	180	Training 8	Satisfactory performance

Contact_ID	Driver_ID	Emergency_Name	Emergency_Number	Emergency_Address	Emergency_City	Emergency_State	Emergency_Zip
1	1	Emergency Contact 1	111-222-3333	789 Elm St	Villagetown	CA	98765
2	2	Emergency Contact 2	777-888-9999	999 Pine St	Townsville	NY	54321
3	3	Emergency Contact 3	888-999-0000	111 Oak St	Cityville	CA	12345
4	4	Emergency Contact 4	999-000-1111	222 Maple St	Villagetown	TX	67890
5	5	Emergency Contact 5	111-222-3333	123 Oak St	Townsville	NY	54321
6	2	Emergency Contact 6	444-555-6666	456 Elm St	Cityville	CA	12345
7	3	Emergency Contact 7	777-888-9999	789 Pine St	Villagetown	TX	67890
8	5	Emergency Contact 8	999-000-1111	101 Maple St	Cityville	NY	54321
9	5	Emergency Contact 9	111-222-3333	123 Oak St	Townsville	NY	54321

Equipment_ID	Insurance_ID	Driver_ID	Type	VIN	Model_Year	Ownership_Status	Equipment_Price	Monthly_Payment	Description	Vehicle_Weight	Vehicle_Length	Vehicle_Width	Vehicle_Height	Trailer_Weight	Trailer_Length	Trailer_Width	Trailer_Height
1	1	1	Truck	VIN123456	2022	Owned	¤ 80,000.00	¤ 1,500.00	Test Truck	12000	30	8	12	8000	40	8	12
2	1	3	Truck	VIN098765	2022	Leased	¤ 80,000.00	¤ 1,600.00	Leased Truck	18000	45	8	13	0	0	0	0
3	2	1	Trailer	VIN654321	2021	Leased	¤ 50,000.00	¤ 1,000.00	Leased Trailer	8000	40	8	12	0	0	0	0
4	2	2	Trailer	VIN567890	2021	Owned	¤ 60,000.00	¤ 1,100.00	Old Trailer	7500	30	8	11	0	0	0	0
5	2	3	Truck	VIN876543	2021	Owned	¤ 85,000.00	¤ 1,700.00	Owned Truck 3	18000	42	8	12	0	0	0	0
6	2	4	Trailer	VIN765432	2022	Leased	¤ 65,000.00	¤ 1,300.00	Leased Trailer 3	7200	32	8	11	0	0	0	0
7	3	3	Truck	VIN789012	2020	Owned	¤ 70,000.00	¤ 1,200.00	Old Truck	15000	35	8	11	0	0	0	0
8	3	4	Truck	VIN654321	2020	Owned	¤ 80,000.00	¤ 1,600.00	Old Owned Truck	16000	38	8	11	0	0	0	0
9	4	2	Truck	VIN012345	2023	Owned	¤ 90,000.00	¤ 1,500.00	New Truck	12000	30	8	12	8000	40	8	12
10	4	4	Truck	VIN456789	2023	Leased	¤ 90,000.00	¤ 1,800.00	Leased Truck 2	16000	40	8	12	0	0	0	0
11	4	5	Trailer	VIN543210	2023	Leased	¤ 70,000.00	¤ 1,400.00	Leased Trailer 4	8000	34	8	12	0	0	0	0
12	5	1	Trailer	VIN567890	2022	Leased	¤ 55,000.00	¤ 1,100.00	Leased Trailer 2	8500	38	8	11	0	0	0	0
13	5	5	Trailer	VIN234567	2020	Owned	¤ 50,000.00	¤ 1,000.00	Old Trailer 2	7000	28	8	10	0	0	0	0

Invoice_ID	Order_ID	Payment_Status	Due_Date	Amount_Due	Payment_Date	Payment_Amount	Payment_Method	Comments	Hauled invoice
2	1	Pending	2022-02-28	¤ 500.00	2022-02-15	¤ 300.00	Credit Card	Invoice for services rendered	Yes
3	2	Paid	2022-03-15	¤ 700.00	2022-03-10	¤ 700.00	Cash	Invoice paid on time	Yes
4	3	Pending	2022-04-01	¤ 900.00	2022-03-01	¤ 1,200.00	Credit Card	Pending payment for services	Yes
5	4	Paid	2022-03-01	¤ 1,200.00	2022-03-01	¤ 1,200.00	Credit Card	Invoice paid in full	Yes
6	5	Pending	2022-06-15	¤ 1,500.00	2022-03-10	¤ 700.00	Credit Card	Pending payment for services	Yes
7	6	Paid	2022-04-05	¤ 800.00	2022-04-05	¤ 800.00	Cash	Invoice paid on time	No
8	7	Pending	2022-05-20	¤ 1,200.00	2022-05-01	¤ 0.00	Credit Card	Pending payment for upcoming services
9	8	Pending	2022-07-01	¤ 1,000.00	2022-06-20	¤ 0.00	Bank Transfer	Pending payment for ongoing project
10	9	Paid	2022-04-15	¤ 600.00	2022-04-10	¤ 600.00	Credit Card	Invoice paid in full and on time
11	10	Pending	2022-08-01	¤ 2,000.00	2022-07-20	¤ 0.00	Cash	Pending payment for multiple orders	Yes
12	11	Paid	2022-05-01	¤ 850.00	2022-04-25	¤ 850.00	Credit Card	Invoice paid before the due date
13	12	Pending	2022-09-15	¤ 1,300.00	2022-09-01	¤ 0.00	Bank Transfer	Pending payment for extended services
14	13	Paid	2022-06-01	¤ 950.00	2022-05-28	¤ 950.00	Cash	Invoice paid in full and in cash

Insurance_ID	Insurance_Provider	Policy_Number	Policy_Start_Date	Policy_End_Date	Coverage_Type	Deductible_Amount	Premium_Amount
1	Insurance Co. 1	POLICY123	2022-01-01	2023-01-01	Full Coverage	¤ 1,000.00	¤ 1,200.00
2	Insurance Co. 2	POLICY456	2022-02-01	2023-02-01	Collision	¤ 500.00	¤ 800.00
3	Insurance Co. 3	POLICY789	2022-03-15	2023-03-15	Liability	¤ 1,000.00	¤ 1,500.00
4	Insurance Co. 4	POLICYABC	2022-01-10	2023-01-10	Comprehensive	¤ 750.00	¤ 1,000.00
5	Insurance Co. 5	POLICYDEF	2022-06-01	2023-06-01	Comprehensive	¤ 1,000.00	¤ 1,200.00

Invoice_ID	Order_ID	Payment_Status	Due_Date	Amount_Due	Payment_Date	Payment_Amount	Payment_Method	Comments
2	1	Pending	2022-02-28	¤ 500.00	2022-02-15	¤ 300.00	Credit Card	Invoice for services rendered
3	2	Paid	2022-03-15	¤ 700.00	2022-03-10	¤ 700.00	Cash	Invoice paid on time
4	3	Pending	2022-04-01	¤ 900.00	2022-03-01	¤ 1,200.00	Credit Card	Pending payment for services
5	4	Paid	2022-03-01	¤ 1,200.00	2022-03-01	¤ 1,200.00	Credit Card	Invoice paid in full
6	5	Pending	2022-06-15	¤ 1,500.00	2022-03-10	¤ 700.00	Credit Card	Pending payment for services
7	6	Paid	2022-04-05	¤ 800.00	2022-04-05	¤ 800.00	Cash	Invoice paid on time
8	7	Pending	2022-05-20	¤ 1,200.00	2022-05-01	¤ 0.00	Credit Card	Pending payment for upcoming services
9	8	Pending	2022-07-01	¤ 1,000.00	2022-06-20	¤ 0.00	Bank Transfer	Pending payment for ongoing project
10	9	Paid	2022-04-15	¤ 600.00	2022-04-10	¤ 600.00	Credit Card	Invoice paid in full and on time
11	10	Pending	2022-08-01	¤ 2,000.00	2022-07-20	¤ 0.00	Cash	Pending payment for multiple orders
12	11	Paid	2022-05-01	¤ 850.00	2022-04-25	¤ 850.00	Credit Card	Invoice paid before the due date
13	12	Pending	2022-09-15	¤ 1,300.00	2022-09-01	¤ 0.00	Bank Transfer	Pending payment for extended services
14	13	Paid	2022-06-01	¤ 950.00	2022-05-28	¤ 950.00	Cash	Invoice paid in full and in cash

Logistic_ID	Order_ID	BOL_Number	Vehicle_Status	Weigh_Station	Permit_Required
1	1	456	In Transit	Station A	Yes
2	2	789	Delivered	Station B	No
3	3	123	In Transit	Station C	Yes
4	4	456	Delivered	Station D	No
5	5	789	In Transit	Station G	Yes
6	6	1011	In Transit	Station F	Yes
7	7	1213	Delivered	Station E	No
8	8	1415	In Transit	Station H	Yes
9	9	1617	Delivered	Station I	No
10	10	1819	In Transit	Station J	Yes
11	11	2021	Delivered	Station K	No
12	12	2223	In Transit	Station L	Yes
13	13	2425	Delivered	Station M	No

Maint_ID	Equipment_ID	Description	Maint_Date	Maint_Type	Service_Provider	Maint_Cost	Parts_Replaced	Labor_Hours	Maint_Miles	Maint_Hours	Documentation	Comments
1	1	Routine maintenance	2022-03-01	Oil Change	Service Center	¤ 100.00	Oil Filter	2	5000	4	Service Receipt	Routine checkup
2	2	Brake Inspection	2022-03-05	Brake Inspection	Service Center B	¤ 50.00	Brake Pads	2	7000	3	Service Receipt 2	Routine checkup
3	3	Oil Change	2022-04-10	Oil Change	Service Center C	¤ 75.00	Oil Filter	2	8000	4	Service Receipt 3	Routine maintenance
4	4	Engine Tune-up	2022-02-20	Tune-up	Service Center D	¤ 100.00	Spark Plugs	3	6000	5	Service Receipt 4	Scheduled maintenance
5	5	Tire Replacement	2022-05-10	Tire Replacement	Service Center E	¤ 80.00	Tires	2	10000	4	Service Receipt 5	Emergency repair
6	2	Battery Replacement	2022-06-20	Battery Replacement	Service Center F	¤ 60.00	Battery	1	12000	3	Service Receipt 6	Routine maintenance
7	3	Transmission Service	2022-04-05	Transmission Service	Service Center G	¤ 120.00	Transmission Fluid	2	8000	5	Service Receipt 7	Scheduled service
8	3	Brake Repair	2022-03-15	Brake Repair	Service Center H	¤ 100.00	Brake Pads	3	6000	6	Service Receipt 8	Emergency repair
9	5	Oil Change	2022-07-10	Routine Maintenance	Service Center P	¤ 45.00	Oil Filter	1	5000	2	Service Receipt 9	Regular maintenance

Operation_ID	Equipment_ID	Fuel_Card	Fuel_Consumption	Fuel_Efficiency	Current_Miles	Total_Miles	Current_Hours	Total_Hours	Next_Maint_Date
1	1	FuelCard123	12 gallons	10 miles per gallon	10000	50000	1200	5000	2023-01-15
2	2	FuelCard456	15 gallons	8 miles per gallon	15000	75000	1800	8000	2023-02-01
3	3	FuelCard789	10 gallons	10 miles per gallon	10000	65000	1200	7000	2023-03-01
4	4	FuelCardABC	20 gallons	6 miles per gallon	20000	80000	2400	9000	2023-01-15
5	5	FuelCardDEF	18 gallons	7 miles per gallon	18000	85000	2200	9000	2023-03-01

Order_ID	Customer_ID	Driver_ID	Commodity_Type	Total_Miles	Freight_Charge	Pickup_Date	Pickup_Time	Pickup_Location	Delivery_Date	Delivery_Time	Delivery_Location
1	1	1	Electronics	500	¤ 1,000.00	2022-03-01	1899-12-30	Warehouse A	2022-03-05	1899-12-30	Client B Warehouse
2	1	5	Electronics	1800	¤ 2,400.00	2022-11-10	1899-12-30	Warehouse M	2022-11-15	1899-12-30	Client M Warehouse
3	2	1	Furniture	700	¤ 1,500.00	2022-04-01	1899-12-30	Warehouse B	2022-04-05	1899-12-30	Client C Warehouse
4	2	2	Appliances	1800	¤ 2,800.00	2022-05-20	1899-12-30	Warehouse F	2022-05-25	1899-12-30	Client F Warehouse
5	2	3	Appliances	1900	¤ 2,500.00	2022-08-15	1899-12-30	Warehouse J	2022-08-20	1899-12-30	Client J Warehouse
6	3	1	Clothing	900	¤ 1,800.00	2022-05-01	1899-12-30	Warehouse C	2022-05-05	1899-12-30	Client D Warehouse
7	3	3	Clothing	2000	¤ 3,000.00	2022-04-10	1899-12-30	Warehouse G	2022-04-15	1899-12-30	Client G Warehouse
8	4	2	Electrical	1200	¤ 2,000.00	2022-03-15	1899-12-30	Warehouse D	2022-03-20	1899-12-30	Client A Warehouse
9	4	4	Furniture	2200	¤ 3,200.00	2022-03-25	1899-12-30	Warehouse H	2022-03-30	1899-12-30	Client H Warehouse
10	4	5	Clothing	2200	¤ 2,800.00	2022-09-10	1899-12-30	Warehouse K	2022-09-15	1899-12-30	Client K Warehouse
11	5	1	Electronics	1500	¤ 2,500.00	2022-06-10	1899-12-30	Warehouse E	2022-06-15	1899-12-30	Client E Warehouse
12	5	3	Electronics	1700	¤ 2,200.00	2022-07-05	1899-12-30	Warehouse I	2022-07-10	1899-12-30	Client I Warehouse
13	5	5	Furniture	2400	¤ 3,000.00	2022-10-25	1899-12-30	Warehouse L	2022-10-30	1899-12-30	Client L Warehouse

Payroll_ID	Driver_ID	Start_Date	End_Date	Deliveries_Complete	Gross_Pay	Deductions	Reimbursements	Net_Pay	Payroll_Date	Payroll_Type
1	1	2022-02-01	2022-02-28	20	¤ 2,000.00	¤ 200.00	¤ 50.00	¤ 1,750.00	2022-03-05	Regular
2	2	2022-03-01	2022-03-31	15	¤ 1,500.00	¤ 150.00	¤ 20.00	¤ 1,330.00	2022-04-05	Regular
3	3	2022-04-01	2022-04-30	18	¤ 1,800.00	¤ 200.00	¤ 30.00	¤ 1,570.00	2022-05-05	Regular
4	4	2022-06-01	2022-06-30	20	¤ 2,000.00	¤ 200.00	¤ 30.00	¤ 1,770.00	2022-07-05	Regular
5	5	2022-05-01	2022-05-31	25	¤ 2,500.00	¤ 250.00	¤ 40.00	¤ 2,210.00	2022-06-05	Regular
6	1	2022-04-01	2022-04-30	22	¤ 2,200.00	¤ 220.00	¤ 35.00	¤ 1,945.00	2022-05-05	Regular
7	2	2022-03-01	2022-03-31	18	¤ 1,800.00	¤ 180.00	¤ 25.00	¤ 1,595.00	2022-04-05	Regular
8	2	2022-07-01	2022-07-31	18	¤ 1,800.00	¤ 180.00	¤ 25.00	¤ 1,595.00	2022-08-05	Regular
9	4	2022-08-01	2022-08-31	22	¤ 2,200.00	¤ 220.00	¤ 35.00	¤ 1,945.00	2022-09-05	Regular

LevelID	Description	Remarks	State
1	Admin
2	User

State_Milage_ID	Equipment_ID	State	Miles
1	1	CA	3000
2	2	NY	2000
3	3	TX	2500
4	4	CA	3000
5	5	TX	3000
6	1	CA	3500
7	3	NY	4000
8	4	TX	2500
9	4	AZ	3000
10	2	CA	2800
11	3	AZ	3200
12	5	NY	3500
13	1	TX	4000
14	2	TX	3000
15	5	CA	2800

ID	From	To	Index
1	2022-01-01		Weekly

Training_ID	Driver_ID	Program_Name	Training_Date	Training_Duration	Training_Provider	Training_Location	Certificate_Obtained	Expiration_Date	Comments
1	1	Safety Training	2022-01-10	2 hours	Safety Institute	Training Center A	Certificate of Completion	2023-01-10	Safety training completed
2	2	Hazmat Training	2022-02-15	3 hours	Safety Institute	Training Center B	Hazmat Certificate	2023-02-15	Hazmat training completed
3	3	Defensive Driving	2022-01-20	2 hours	Safety Institute	Training Center C	Defensive Driving Certificate	2023-01-20	Defensive driving completed
4	4	Hazardous Materials Training	2022-02-20	4 hours	Safety Institute	Training Center D	Hazmat Certificate 2	2023-02-20	Hazmat training completed again
5	5	Fleet Safety	2022-01-25	3 hours	Safety Institute	Training Center E	Fleet Safety Certificate	2023-01-25	Fleet safety training completed
6	1	Defensive Driving	2022-06-10	2 hours	Safety Institute	Training Center F	Defensive Driving Certificate 2	2023-06-10	Defensive driving completed again
7	3	First Aid/CPR	2022-03-05	5 hours	Safety Institute	Training Center G	First Aid/CPR Certificate	2023-03-05	First Aid/CPR training completed
8	5	Cargo Securement	2022-07-20	3 hours	Safety Institute	Training Center H	Cargo Securement Certificate	2023-07-20	Cargo securement training completed
9	5	Hazmat Awareness	2022-08-10	2 hours	Safety Institute	Training Center I	Hazmat Awareness Certificate	2023-08-10	Hazmat awareness training completed

UserId	EmployeeName	UserName	Password	SecurityLevel
1	Admin	Admin	123	1
2	Anas	Anas	12345	2
3	User	user1	1234	2
4	john	john	112233	2

SELECT DISTINCTROW * FROM DRIVER;

SELECT [Hauled Invoices].Invoice_ID, [Hauled Invoices].Order_ID, [Hauled Invoices].Payment_Status, [Hauled Invoices].Due_Date, [Hauled Invoices].Amount_Due, [Hauled Invoices].Payment_Date, [Hauled Invoices].Payment_Amount, [Hauled Invoices].Payment_Method, [Hauled Invoices].Comments, [Hauled Invoices].[Hauled invoice] FROM [Hauled Invoices];

SELECT INVOICE.Invoice_ID, INVOICE.Order_ID, INVOICE.Payment_Status, INVOICE.Due_Date, INVOICE.Amount_Due, INVOICE.Payment_Date, INVOICE.Payment_Amount, INVOICE.Payment_Method, INVOICE.Comments FROM INVOICE;

SELECT DISTINCTROW * FROM DRIVER;

SELECT DRIVER.Driver_ID, DRIVER.First_Name, DRIVER.Last_Name, DRIVER.Hire_Date, DRIVER.DL_Class, DRIVER.DL_State, DRIVER.DL_Number, DRIVER.DL_Expiration, DRIVER.Endorsements, DRIVER.Phone_Number, DRIVER.Address FROM DRIVER;

SELECT MAINTENANCE_HISTORY.Maint_ID, MAINTENANCE_HISTORY.Equipment_ID, MAINTENANCE_HISTORY.Description, MAINTENANCE_HISTORY.Maint_Date, MAINTENANCE_HISTORY.Maint_Type, MAINTENANCE_HISTORY.Service_Provider, MAINTENANCE_HISTORY.Maint_Cost, MAINTENANCE_HISTORY.Parts_Replaced, MAINTENANCE_HISTORY.Labor_Hours, MAINTENANCE_HISTORY.Maint_Miles, MAINTENANCE_HISTORY.Maint_Hours, MAINTENANCE_HISTORY.Documentation, MAINTENANCE_HISTORY.Comments FROM MAINTENANCE_HISTORY;

SELECT DISTINCTROW * FROM DRIVER;

SELECT [Hauled Invoices].[Hauled invoice] FROM [Hauled Invoices];

SELECT DISTINCTROW * FROM MAINTENANCE_HISTORY;

SELECT DISTINCTROW * FROM [date wise query];

SELECT DISTINCTROW * FROM [tbl reports];

SELECT DISTINCTROW * FROM [Hauled Invoices];

SELECT DISTINCTROW * FROM PAYROLL;

SELECT [SecurityLevel].[LevelID] FROM SecurityLevel;

SELECT UserAccount.UserId, UserAccount.EmployeeName, UserAccount.UserName, UserAccount.Password, UserAccount.SecurityLevel FROM UserAccount;

SELECT CUSTOMER.Customer_ID, CUSTOMER.Company_Name, CUSTOMER.Vendor_Number, CUSTOMER.First_Name, CUSTOMER.Last_Name, CUSTOMER.Email, CUSTOMER.Phone_Number, CUSTOMER.Address, CUSTOMER.City, CUSTOMER.State, CUSTOMER.ZIP, CUSTOMER.Shipping_History, CUSTOMER.Shipping_Preferences FROM CUSTOMER;