Expert needed!!!

[Muffinshuttleworth]

Hi

I work for a very small charity (AVSED) in UK and have inherited a MS Access db of which I know absolutely nothing at all and have muddled my way through this. The db is now in a mess and most certainly not built to what I am guessing MS Access (from reading most of this site) demands! Does anyone know of anyone who will look at our db for us and tell me what needs to change (I know it will be start again but we cannot function one day without it). I am prepared to pay for this but will be coming out of my own pocket so needs to be someone reliable. I have already had promises from two people (not thru this site) and they have let me down. HELPPPPPPPP PLEASE!

 

[Galaxiom]

Don't panic.
When you get a new database you don't have to do without it while you slowly fix the old one. You can design a new one including the code required to import the old data into the new. One day you install the new database and suddenly it has all the new features.

However you may be surprised how easily the existing design can be restructured if necessary, adding a couple of new tables and making some new forms using the wizard. Access and VBA can do almost anything with your data.

Open you database and find the relationships diagram. Tidy it up its layout if necessary and post it here. Print screen. Its easy. We can tell a lot from this picture particularely if the table and field names are self explanitory.

 

[Kondwani]

i have lots of time. ill do it for free. send it to me.

 

[oumahexi]

I am prepared to pay for this but will be coming out of my own pocket so needs to be someone reliable. I have already had promises from two people (not thru this site) and they have let me down. HELPPPPPPPP PLEASE!

That is just silly. The charity, and I don't care which one it is, pays its directors a fortune, unless you are a director I don't see why it should come out of your pocket, let them pay for it.

However, I doubt that will be necessary, your best solution is probably to let us know exactly what you mean by "a mess". Then let us help you to figure it out.

 

[Muffinshuttleworth]

Here it is - thanks for your offers - I can't thank you enough!

 

[Muffinshuttleworth]

Re paying for this. We do not have a Director - we have a Chairman who is actually one of our Volunteers. There are only 4 paid staff - one who drives the minibuses, my boss, myself and a Community worker just for your info. I am not ripping anyone off and will pay out of my own pocket if I have to!

 

[oumahexi]

Re paying for this. We do not have a Director - we have a Chairman who is actually one of our Volunteers. There are only 4 paid staff - one who drives the minibuses, my boss, myself and a Community worker just for your info. I am not ripping anyone off and will pay out of my own pocket if I have to!

Didn't mean to imply you were ripping anyone off, I've just had very bad experiences with charities.

 

[oumahexi]

OK, it's difficult to see from your diagram exactly what relates to what as much of it is missing. The idea is, however, with relationships to make sure that you get an absolute match. So, if you have in AVSED Client table a field named Route and it is linked to Transport Route table field named id they should both return the same data, in my opinion it is much more helpful to name your fields so that they are easily identified such as RouteId. This may be where you will start, look at all your relationships and make sure that they match up.

You might also need to take a look at the type of joins you are using, if they are supposed to be one to many, many to many etc.

Hope this helps

 

[Fleur]

Thanks for that info. That's where the problem lies - I can build the tables and stuff but have no idea about the relationships despite using the wizards and reading up - I just can't put it into practice.
I also don't know what tables I need i.e. should there be a table for all of the separate info I need to record? We hold data on client's mobility aids they use, their health and what's wrong wrong with them, the type of accommodation they live in, the group or social centre they go to, what other services they use, and all the relevant personal data. So do I need a separate table for each of these? And how should they be connected relationship wise?

 

[Scooterbug]

Based off of what you said you keep track of, I would have to following:

tblClient
Client Info (Personal Data)

tblMobilityAids
ClientID Used to record the client
Mobility Aid Info

tblHealthStatus
ClientID Used to record the client associated with the health history
Health History

tblAccomodations
ClientID (As above)
Accomodation Data

tblServices
ClientID (As above)
Service Data

tblSocial
ClientID (As above)
Data on Social Activies

That is just off the top of my head. The important thing is, as you create data for a client in it's relavent table, make sure you also record the ClientID so that when you need to pull up all the data on a client, all you have to do is to find the related record via the ClientID.

I like to look at building a database like playing with legos. You build your final project by connecting all the individual pieces (Each piece represents data in a table) by finding all the piece that have something in common. In your case it would be the ClientID. A bit simplified, but that's it in a nutshell.

 

[mcdhappy80]

Geeze, looking at this picture makes my stomach spins. Whoever built this didn't knew "didly squat" about database design.
My suggestion would be that You explain exactly what do You need out of the database (what will it be used for) in as much detail as possible, and that someone here build You a new one, for free of course .
Just by looking at this I can see there is to much of redundancy (unnecessary repetition) of data here.
For example:
You have several tables with different names (AVSED client, Tally clients, Tally volunteers, AVSED Gardening clients) and all those tables have sveral attributes in common (Name, Surname, Address, etc.). The redundancy problem could be solved if You would create just one table that will hold clients (persons) data (Name, Address, etc.), and then add other table (Person Type for example) which will hold this data [AVSED client, Tally clients, Tally volunteers, AVSED Gardening clients].
This procedure will reduce the amount of tables in Your database and thus making it simplified for managing.
I hope this helped a little, good luck

 

[Kondwani]

Hey. give it to me. ill make it internet based as well.

 

[Muffinshuttleworth]

Thanks everyone, will try start again from scratch and recreate something less disastorous! Yes, it was built by someone who had absolutely no knowledge of Access and had not even seen it beforehand but we try and use what we can too get the best of things!