Guidance needed for a text database project please

[Vitesse]

I'm kinda lost in here so I'd appreciate some help in pointing me in the right direction please. I've no doubt the answers are out there but I'm having trouble finding what I need, probably because I don't know how to frame my questions!

Basically the story is this - there are three guys covering various aspects of the business, they each visit three separate locations - all guys visit all locations. We have made a "big plan" that has 14 key deliverables in the locations as a "region" .

For each Key Deliverable there will be Actions, some Actions will be done in all locations, some might only be needed in one location. Each owner will describe the Action and link it to the Key Deliverable it supports.

What we want to do are things like
Take Location 1 and Guy A and show all the things he is doing to support Key Deliverable X.

or

Show all the Actions in Location 2 that are being done to deliver Key Deliverable Y by all Guys.

That sort of thing - progress reporting and identifying where we have nothing happening to support a Key Deliverable or too many actions in one Location etc

We have all our plans in Excel spreadsheets at the moment, each guy fills in a sheet and at present the idea is that we copy and paste it into the mother of all spreadsheets to present to the Gods - it's not happened yet because it's clumsy and well, it's my job to present it and I thought "there's gotta be a better way!" I can import the sheets into Access but then it all gets a little tricky and I run out of tallent.

OK so if you've read this far you're wondering if I have any idea what I'm talking about when it comes to Access! Well I am pretty much self taught and rusty but I can see that Access could help us get organised and

My limitations - I'm using standard Access 2007, I doubt corportate IT security would allow me to add in any useful plug ins or upgrades etc.

Am I using the right tools? I'm limited to MS Office really so I mean should I stick to Excel or carry on pursuing an Access solution?

Thanks for reading this far

 

[KeithG]

I would definetly say Access is a better tool than excel for this. First thing you need to do is define your tables and relationships. Once you have done this post your table schema's a and relationships and then we can help out more.

 

[rsmonkey]

Yep it should be a fairly easy excercise just read up abit on Database design and Normalisation, look at some of the sample db's in this forum. If you get really really stuck i've got a db which grabs aloada Customer Sat Survey info from excel spreadsheets and i made access do all the number crunching.. i.e. I turned peoples word answers into a scoring system, which was the exported into excel into a predefined template so now every month i click 2 or 3 buttons and its job done! but please for your sake rather than mine research abit on DBs get back to the basics.. Play with queries and study the sql is always handy aswell! what are your time constraints on this project? I would preferably use Access 2003 but thats purely from a gui perspective im sure 2007 has loadsa optimisations im just not too great with change! Oh and another thing its nice to see another Reading boy on ere!!

 

[Vitesse]

Thanks guys - I've used a db before but never in this way, ie with text and no calculations.
When you say post a schema what do you mean? I can post the outline of the data collected in the Excel spreadsheet (attached) and what I've done so far is to split that up into tables of similar data in Access - what I can't quite get my head around is whether I'm doing that right or not. I keep wanting to maintain one big table but that's probably not the way forward!
There are repeated items in things like the Location, Key Deliverables and Actions as often each location will be required to do the same thing so there are three entries, they may complete them at different times and learn different things hence they are recorded as three distinct Actions.

 

[rsmonkey]

Looking at your spreadsheet and reading your description i struggling to know what you really want. Well i know you want a large spreadsheet to show to the 'Gods' as you put it but if you could give me a quick example of the Kind of data you want to put into the DB (well we've got that with your previous attachement) and then a quick knock up of the kind of spreadsheet you want to get out of it then that would be a massive help. But at the moment its difficult to help with your db design if we dont really know what the end result is going to be. Potentially you mite not need to make any difinitive tables rather have a Linked table and code a couple forms to do all the magic!

mind you im a muppet some of these other genius's mite have a better idea of what your trying to achieve!

 

[Vitesse]

Looking at your spreadsheet and reading your description i struggling to know what you really want. QUOTE]

I'll tell you what I want, what I really really want...

Each guy has a plan with lots of actions in support of numerous Key Deliverables, guys, actions, locations, key deliverables - there's a load of things to keep track of. In a spreadsheet there would be a couple of hundred lines to start with - and it will grow.

If we are doing actions to support a Key Deliverable we need to be able to show them and group them by Location or by Owner/guy or by date(milestone) - so when the Gods say "What are you doing to achieve world domination?" We can push a button and say "Chris is doing Action 1, 2, 3, 4, in Location A, Action 2 in Location B and Actions 3 and 4 in Location C. Jason is doing Action 1 in Location C etc" And when, in a deep booming voice, the Gods say "When will Location A be ready for world domination?" we can say "When Action 2 and 3 are complete which is Tuesday at about teatime"

We need to be able to see where what we are doing is supporting the Key Deliverables we've signed up for and where, for instance, we're not doing enough or it's taking too long etc.

Reporting wise, I need to write something that filters and sorts the Actions into a "report" - doesn't actually have to look pretty (I can figure that out at my leisure) but it does need to be functional and that's the bit I can't get my head around - how do I filter and sort text data?


I hope that makes sense.

 

[GaryPanic]

coming in half way through - simplfy first

ok

what is the centre of the project
well its location .

each location will have 3 guys visit
john,David,Simon


each guy will have a job list*

* this is the awkward one

the job list I would have in one table and this job list I presume needs to have
PK
FK (john,david, simon )
FK location id
job
and some date field
actiondate
checkdate
completeddate
notes

having all the job in one table will make sense - in your form you will want these set up as a continuous form
and have a open form for each person
one for John, one for David and one for Simon
they open up the joblist and filter out by 2 critia one the locationid and the other by john,david etc

this should be the bare bones of it
it gets more completed if some of the jobs are the same for every location - but thats not too much of a problem a quick append qry will sort that out ... froma default table

(just my thoughts on this )

regards

 

[GaryPanic]

just looked at the spreadsheet - this could be built in quiote easliy into what i have suggested ..

 

[Vitesse]

what is the centre of the project
well its location .

each location will have 3 guys visit
john,David,Simon


each guy will have a job list*

* this is the awkward one

the job list I would have in one table and this job list I presume needs to have
PK
FK (john,david, simon )
FK location id
job
and some date field
actiondate
checkdate
completeddate
notes

regards

OK I can see where you're going and it's helpful - what do you mean by "PK" and "FK"? I'm assuming PK is Primary Key but what the FK is FK?

Location? Well the idea is we are UK based supporting three offshore locations but that's not really relevant, the actual location is my study in my house in Reading ;-)

What I'm struggling with is the idea of joining the Action table with other tables.
Here are the criterea I'm working with

Date Added - dd/mm/yyyy
Owner - from a list of "guys" currently three
Location - from a list, currently 3 locations
Action - the 'job', a free text description
Main Deliverable Supported - one of 14 pre-set statements
Benefits inc measures - free text
Progress Milestones - dates, perhaps I also need a free text description
Embedded/completed date - dd/mm/yyyy
Comments & Observations - free text
Learnings along the way - free text
Risks, Issues, Dependencies - free text
Ongoing review dates - could be dd/mm/yyyy but at the moment it's free text

So what I'm stuck with here is the Ah-ha! moment where I understand how I should set out the db and get it to provide those reports that say "In support of Main Deliverable 1 the following actions are underway."

 

[Rabbie]

FK is foreign Key. That is the primary key of a record from another table. It's what puts the relationship into relational databases.

 

[rsmonkey]

Firstly your struggling with the problem off connecting your excel spreadsheet into your database tables. well my friend this is relatively easy and i see 2 potential soloutions for you depending on how often the data within the spreadsheet is updated. If these 3 guys or whoever updates this database on a regular basis you probably want to go for a Linked Table where Access has a table which is a direct manifestation of the Excel Table & whilst the db is open it is connected real time to the spreadsheet i.e you update the Spreadsheet, you are effectively updating the table within the db. From here you can create a form or a number of forms your choice really which at the click of a button will 'organise' your data into your relevant tables so you can then simply create the neccesary reports for the gods. Alternately if this is going to be a weekly or monthly update it will probably be worth having a static Temporary (by temporary i mean it is a permanent table but simply used for data storage and does not interact with the db like other tables in ure db) table in which you click a button and import the excel file and then like before have a form or 2 containing aloada VBA/SQL and sort it all out for you!
be prepared to get your hands dirty with code however, the projects i've done like this usually contain lots of loops etc...

 

[Vitesse]

FK is foreign Key. That is the primary key of a record from another table. It's what puts the relationship into relational databases.

Cheers Rabbie

 

[twoplustwo]

Excellent posts guys.

OP,

Try reading up on Normalization for a good intro into sound db planning from the ground up. Buggering up relationships etc. at the very start can cause real problems later down the line.

 

[Vitesse]

Firstly your struggling with the problem off connecting your excel spreadsheet into your database tables.

I've done imports before but never actually linked an Excel table in the way you describe so that could be "fun".
The idea behind this whole thing is that we use the spreasheet to record and run the actions, when the Gods want a report we don't have to spend ages collecting and collating information about how the Action is progressing or what's complete etc. We can run a query and provide an up to date view of activity against Key Deliverables - we all hate reporting so we're trying to make it as easy as possible, trouble is I said I thought I could use a db and that was me labelled "expert" and dumped with the job

OK So if I put together the tables as you suggest, how do I write the queries that do what we need?

 

[Vitesse]

Excellent posts guys.

OP,

Try reading up on Normalization for a good intro into sound db planning from the ground up. Buggering up relationships etc. at the very start can cause real problems later down the line.

Normalisation - that's getting your data to be "clean" isn't it? ie "yes", "Yes" and "YES" are not the same until you normalise them into "yes" or am I thinking of something else?
Also, when you say "read up on" - where do you suggest I go to read up on this? All I have to work from is an Access 2000 book and the internet - I'm using Access 97 remember My biggest issue seems to be that I don't speak the language of db so when I say I want to do xyz I can't actually find it because in db language it's called something that's non-intuitive (to me)

Thanks for all the help guys, I'm gaining understanding.

 

[Vitesse]

Alternately if this is going to be a weekly or monthly update it will probably be worth having a static Temporary (by temporary i mean it is a permanent table but simply used for data storage and does not interact with the db like other tables in ure db) table in which you click a button and import the excel file and then like before have a form or 2 containing aloada VBA/SQL and sort it all out for you!
be prepared to get your hands dirty with code however, the projects i've done like this usually contain lots of loops etc...

It will be often updated, at least monthly but more likely weekly - I really don't want to get too far into the black arts of VBA before I have something that works. I'd really like to have something I understand and can use first

 

[Rabbie]

Normalisation - that's getting your data to be "clean" isn't it? ie "yes", "Yes" and "YES" are not the same until you normalise them into "yes" or am I thinking of something else?
Also, when you say "read up on" - where do you suggest I go to read up on this? All I have to work from is an Access 2000 book and the internet - I'm using Access 97 remember My biggest issue seems to be that I don't speak the language of db so when I say I want to do xyz I can't actually find it because in db language it's called something that's non-intuitive (to me)

Thanks for all the help guys, I'm gaining understanding.

First of all try looking in Access help for normalisation or data normalisation. The search this forum for normalisation and try googling for it as well. The acedemic sites can be very helpful on this topic.

 

[Vitesse]

Thanks - I think I'm clear on Normalisation now, that Wikipedia is great!

How do I go about creating the queries I need?

 

[rsmonkey]

Well the good news vitesse is that you can make this a one button job.. i.e. a single button will import, collate, analyse and spit out the reports however the more functionality out of a button you want the more coding and VBA is going to be needed so you're going to have to decide on a balance you want. As for the weekly/monthly imports I would say create a script to import the table rather than a link table, as I find linked tables can be abit fussy every now and again...

So tell us how much ease of functionality you want to get into and how much you wanna 'roll up your sleeves' and then we can guide you from their. If you could show us your db so far it would make things alot swifter and im sure people would be happy to contribute and modify as we go along!

 

[Vitesse]

Thanks rsmonkey, the way I look at this is that I know it can be done and I know it should be done but I don't know how it can be done. There's no other resource available to us apart from me and you guys So, to cut a long story short, I'm in with my sleeves rolled up.
The "database so far" consists of one attempt by me to import one of the spreadsheets from one of the guys. Whilst the data's not exactly business critical I just need to change the names to protect the guilty and make it a little more generic so I can publish it in this community. Nothing sinister but I'll just remove the world domination and overthrow government tasks and turn them into something less sexy - like "Create a training plan".
I'll be back later with a plain vanilla version we can hack about.