using data in a linked spreadsheet to calculate values for a report 1

[soccercook44]

Hello, I'm fairly new to access and am trying to create a database that uses a linked spreadsheet of names, depts, and dates some training was preformed.

My goal is to create a report that has percentages of who has completed said training. For the whole command and by departments. If the person hasn't completed the training the field is left blank but they still have a record.

My idea is to find all the records of each department and count them, if they are in dept a and one to a and one to total... so on and so forth till I get all the records. this would be time consuming and a pain to figure out how to do. Does anyone have any advice on how I could complete said goal either doing what I have said or a new idea?

And the data is downloaded from a site every day or two, so the table must be the linked spreadsheet so it can update quick and easily, by over writing the spreadsheet on the disk.

Thanks for your time and help.

 

[dhookom]

Do you have any table and field names to share as well as sample records?

You mention "For the whole command" but don't tell us what a "command" is. Access programmers think a "command" is something other than your usage.

Also, this statement "they are in dept a and one to a and one to total... " lacks clarity. If "a" is a department, you are better off using "A" or "DeptA".


Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Based on the terminology you use, I'm guessing [blue]NAVY[/blue].

To begin with, I would create these tables.
[tt]
[ul square]tblPersonnel
[ul disc]
[li]PersonnelID Number Primary Key[/li]
[li]LastName Text[/li]
[li]FirstName Text[/li]
[li]Rank Text[/li]
[li]Dept Text[/li]
[/ul]
[/ul]

[ul square]tblCourses
[ul disc]
[li]CourseID Number Primary Key[/li]
[li]CourseName Text[/li]
[/ul]
[/ul]

[ul square]tblTraining
[ul disc]
[li]TrainingID Auto Number Primary Key[/li]
[li]PersonnelID Number Foreign Key (tblPersonnel)[/li]
[li]CourseID Number Foreign Key (tblCourses)[/li]
[li]StartDate Date Field[/li]
[li]EndDate Date Field[/li]
[li]Grade Number or Text[/li]
[/ul]
[/ul]
[/tt]
You can update from a linked spreadsheet by building queries that Add, Delete, Update the various records.

Randy

 

[soccercook44]

Please excuse my vagueness.

rank FName Lname Dept TrainDate
HN Jonathan Scinta DMH
CONT Zach Kover DMH
CIV Junior Stroud DFA 12/7/2009
LT Christon Duhon DPH 12/7/2009
CIV Hayden Mennley DCSS 11/9/2009
GS Jenneva Borboa DFA 11/10/2009
HA Nickolas MCClellan DCSS 11/6/2009
CONT Kahteryn Smith DMS

This is a sample from my table. With the Field names at the top. For my report: rank,FName, and Lname can be dropped because I dont care about them. So I use a Query to only show Depts and TrainDate.

Thats where I am right now. From here I need to be able to count the total number of records, count the number of records who have done training(with a date in the field), and then also count the number of records in each dept, and the number from each dept that has done training. Then take those numbers and come up with a percentage for the whole table who have done training, as well as by dept.

EX. Report

Command = 5/8 = 62.5%
DMH = 0/2 = 0%
DFA = 2/2 = 100%
DPH = 1/1 = 100%
DCSS = 2/2 = 100%
DMS = 0/0 = 0%

I hope this is a little more clear, I forget that I need to not explain things as if people know what i'm thinking.

 

[randy700]

SELECT Count(ID) AS CountOfID, Dept, Count(TrainDate) AS CountOfTrainDate, CountOfTrainDate/CountOfID AS PctTrained
FROM myTable
GROUP BY Dept

Randy

 

[soccercook44]

Thank you Randy and Duane(for the initial help).

I just got this to work.