Question No. 1 & 2)
Creating Spreadsheet: All_Employee_Data:
Club the information about all the sites at one place(in one file, excel). Can use Ashford_site_job_code.csv file.
Use All_Employee_Per_Info.csv to bring all the information at one place.
While combining the data, create field names as per the dbms rules.
Use Vlookup to gather the data for Site, Job Code & Contract Hours.
Save it as All_Employee_Data………….. as excel workbook.
Copy Job_Code_Description data in an appropriate application software and save it as CSV.
Question No.3)Create a database named Combined_Factory_Data and import the data.
Import : All_Employees_Data, Job_Codes_Description & Site_Info Files.
Create Cross-tab Query: Select Site as Row, Job Code as Column Heading and Payroll Number to count the records.
In design view replace the Job_Code of All_Employees_Data field by Job_Description field of Job_Code_Description table.
Uncheck the box, Yes,include row sums.
Design As per the requirements.
For Total Company Staff use formula:
=Sum([Admin Assistant]+[Admin Manager]+[Line Engineer]+[Line Operative]+[Line Supervisor]+[Site Assistant]+[Site Manager]++[Technical Manager]+[Technical Operative]+[Warehouse Manager]+[Warehouse Operative])
Alternatively you can check the box, Yes,include row sums and using layout view, Use Sum function to Sum total of Payroll Number, and copy paste the formula on top of the report as required Or alternatively use the below formula on top of the report.
=Sum([Total Of Payroll_Number])