I'm in the military and am trying to build a database that allows me to keep track of what post my troops where on a what day of that month and if need be print a report for a specific day listing all posts for that day.
Currently we use 2 excel spreadsheets that are linked and do this for us but I've been tasked with a way to put this into access so we can build on the database with more than just this info.
The excel sheets are split: 1 is the report that prints the requested dates info and the other is a 12-tab (1 for each month) book that in each tab lists the following info.
Name, certifications, licenses and than 31 columns for the month(this is where they put in where they were posted).
Here is where my problem begins. I've tried building different combination's of tables but am having hard time getting any configuration to work the way we need it to.
1st configuration:
tbl1: Name, certifications, licenses, unique ID
tbl2: unique ID, Date, Post (joined by unique ID)
However I need this to display in an array so we can assign the troop his post and look at where he's been posted in the last few days. Example I'm doing the roster for the 6th of April and I need to see his previous 5 posted days. Couldn't figure out how to make this work. Also when people left the unit and they where deleted from the database than I couldn't go back and pull up where they were posted(yes I need to be able to do this).
2nd configuration:
Basically had all this info in 1 table that looked much like my 2nd spreadsheet so it had 3 columns of info with another 31 for posts for that month. I know this sounds like a horrible table setup but I was getting desperate to find something that worked.
Problem here was I'm unaware of how to pull only a specific column based on dynamic criteria. Example: I want to pull all the names, certifications, and than only the info from column x where x is the dynamic criteria.
Any ideas on how to set this database up to get it doing what I need it to?
Currently we use 2 excel spreadsheets that are linked and do this for us but I've been tasked with a way to put this into access so we can build on the database with more than just this info.
The excel sheets are split: 1 is the report that prints the requested dates info and the other is a 12-tab (1 for each month) book that in each tab lists the following info.
Name, certifications, licenses and than 31 columns for the month(this is where they put in where they were posted).
Here is where my problem begins. I've tried building different combination's of tables but am having hard time getting any configuration to work the way we need it to.
1st configuration:
tbl1: Name, certifications, licenses, unique ID
tbl2: unique ID, Date, Post (joined by unique ID)
However I need this to display in an array so we can assign the troop his post and look at where he's been posted in the last few days. Example I'm doing the roster for the 6th of April and I need to see his previous 5 posted days. Couldn't figure out how to make this work. Also when people left the unit and they where deleted from the database than I couldn't go back and pull up where they were posted(yes I need to be able to do this).
2nd configuration:
Basically had all this info in 1 table that looked much like my 2nd spreadsheet so it had 3 columns of info with another 31 for posts for that month. I know this sounds like a horrible table setup but I was getting desperate to find something that worked.
Problem here was I'm unaware of how to pull only a specific column based on dynamic criteria. Example: I want to pull all the names, certifications, and than only the info from column x where x is the dynamic criteria.
Any ideas on how to set this database up to get it doing what I need it to?
