Timeline

[lefty78312]

I'd like to create a timeline for all the soldier's who were stationed at my post in Germany and include not just their years of service (e.g., 1972-1974) but the months as well, e.g., (3-1972 to 7-1974). It would be nice if I could also include the unit they were stationed in. Also sortable. Since it will run from approximately 1952 - 2000, is there some way to avoid having 49 years X 12 months (588 rows)? There are currently almost 900 soldiers on the message board and the list is slowly growing.

Now I understand this is no 20-minute project. It'll take me months to enter all the data. At this juncture I'm looking for the best format for entering the data and making it sortable.

 

[Gerald Higgins]

Hi lefty. I think I understand what you are trying to do, but with respect, your post is pretty vague, so it's difficult to know exactly what advice to give you.

What do you want to use this data for ?
If you want to find out who was at the post on a certain date, or whether two people were there at the same time, then I think you need to focus on capturing certain bits of data, such as name, unit, and start date and leaving date.
Is it possible that people left, and then returned later, so that they had two or more spells on site ? If it's possible, how do you want to handle that ?
Is it possible that people changed their unit during their stay ?
Is there any other information that you want to capture, like maybe rank, date of birth, where they were from, where they came from immediately before your post, where they went to immediately after your post, their current contact details, and so on ?

 

[Smitty]

is there some way to avoid having 49 years X 12 months (588 rows)? There are currently almost 900 soldiers on the message board and the list is slowly growing.

If you've only got 900 members so far, then you're well within Excel's capabilities.

Similar to Gerald's suggestions, I'd put in all of your vital stats as column headers (Name, Rank, ID, In-Service Date, Exit Date, Unit, etc.), and start from there.

If you have Access you might want to check out this Membership Database Template.

HTH,

 

[lefty78312]

Thank you Gerald and Smitty. It would be extremely unusual for someone to leave their post and return unless they were on TDY (temporary duty), which doesn't count. In the extremely rare chance that they did, I'd just create a separate entry for them. But I don't think it's gonna be an issue.

The point is to have a sortable database that I can send to the members for their own reference. Most of them have either Excel or OpenOffice. In many ways, Access might be a better solution, but most people either don't have it and/or don't understand how to use it. But I could use it for my own records, and I may do that.

 

[Akashwani]

Hi lefty78312,

I've come across your post whilst having a rest from my little headache.

I've been working on something similar, HR based. It's a simple set up, but has taken me for ever to get to grips with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Clock Number	Postion	Department	From	To	Duration	Address1	Address2	Town	County	Post Code	Phone	Email
2	Bill	111111	Clerk	Wages	02/02/2000	20/03/2004	4 years, 1 months, 18 days	*	*	*	*	*	*	*
3	Tom	111112	Manager	Production	02/02/1998	20/03/2004	6 years, 1 months, 18 days	*	*	*	*	*	*	*
4	Bob	111113	General Op	Production	02/02/2004	20/03/2006	2 years, 1 months, 18 days	*	*	*	*	*	*	*
5	Sue	111114	Auditor	Accounts	02/02/1999	20/03/2001	2 years, 1 months, 18 days	*	*	*	*	*	*	*
6	Flo	111115	General Op	Production	02/02/2002	20/03/2002	0 years, 1 months, 18 days	*	*	*	*	*	*	*
7	Mo	111116	Cleaner	Services	02/02/2000	20/03/2001	1 years, 1 months, 18 days	*	*	*	*	*	*	*
8	Dan	111117	Electrician	Services	02/02/2008	20/03/2009	1 years, 1 months, 18 days	*	*	*	*	*	*	*
9	Den	111118	Electrician	Production	02/02/2000	20/03/2002	2 years, 1 months, 18 days	*	*	*	*	*	*	*
10	Jo	111119	Cook	Canteen	02/02/2002	20/03/2004	2 years, 1 months, 18 days	*	*	*	*	*	*	*
11	Joe	111120	Fitter	Services	02/02/1999	20/03/2000	1 years, 1 months, 18 days	*	*	*	*	*	*	*
12	Tim	111121	Fitter	Production	02/02/2001	20/03/2002	1 years, 1 months, 18 days	*	*	*	*	*	*	*
13	Ben	111122	General Op	Production	02/02/2002	20/03/2002	0 years, 1 months, 18 days	*	*	*	*	*	*	*
Master

I have another sheet which is used to Lookup particular data.....

Excel Workbook
	H	I	J	K	L
1	Department	Count	*	*	*
2	Production	6	*	*	*
3	*	*	*	*	*
4	Name	Clock Number	Position	From Date	End Date
5	Tom	111112	Manager	02/02/1998	20/03/2004
6	Bob	111113	General Op	02/02/2004	20/03/2006
7	Flo	111115	General Op	02/02/2002	20/03/2002
8	Den	111118	Electrician	02/02/2000	20/03/2002
9	Tim	111121	Fitter	02/02/2001	20/03/2002
10	Ben	111122	General Op	02/02/2002	20/03/2002
Lookup

Or....

Excel Workbook
	U	V	W	X	Y	Z
1	Year	Count	*	*	*	*
2	2000	6	*	*	*	*
3	*	*	*	*	*	*
4	Name	Clock Number	Postion	Department	From	To
5	Bill	111111	Clerk	Wages	02/02/2000	20/03/2004
6	Tom	111112	Manager	Production	02/02/1998	20/03/2004
7	Sue	111114	Auditor	Accounts	02/02/1999	20/03/2001
8	Mo	111116	Cleaner	Services	02/02/2000	20/03/2001
9	Den	111118	Electrician	Production	02/02/2000	20/03/2002
10	Joe	111120	Fitter	Services	02/02/1999	20/03/2000
Lookup

As you can see from the data above it would be very easy for you to convert the data from say Clock number to ID number or Position to Rank, Department to Unit.

I have put a sample of the above Database here if you are interested.....

lefty78312 Sample.xls

Good luck with your project, if mine is anything to go by, I'm sure that you will find it interesting and challenging.

Ak