Access database help
Author
Discussion

Stu R

Original Poster:

21,701 posts

246 months

Monday 18th June 2007
quotequote all
Hi all. This might be a bit wordy but if you've any knowledge of access or databases in general I'd really appreciate your help.

I'm looking for a bit of help with an ms access database I'm making for my better half. I only have a very basic knowledge of databases from years back so i've fumbled my way through and made something remotely akin to a database and have hit a few hurdles I could really use some help with.
The database is for storing information relating to people my girlfreind helps with charitable work. In this there's all sorts of things covered from general information to basic details of the clients income and details of their utility bills and so on.

Basically the concept of relationships I've totally forgotten, always handy in a relational db.

1)
Ok, in each of the tables I've got the clients name and primary key (in this case their national ID number), and I've made a form for each of them to input the data. This seemed right before, but it doesn't now. She doesn't want one huge form to fill in so how can I get the name and NI number to automatically update the next form in the series? I'd assumed that as there's a chance one form may be required to be filled out and another might not be required, that it'd be good to have it featuring on each of them just in case.
2)
I want to make it so when you want to add details of a new client, it takes you through each of the forms in sequence, sort of like creating a new portfolio or profile from half a dozen forms. Is there an easy way to do this? I can't picture a link between the forms and switchboard / menu that would do this.
3) Relationships.
This is the bit I'm really stuck on at the minute. Basically from the notes I've been given details of each of the clients utility bills. For each of this utilities there's a few things that need filling in, which are the same for the majority of the utilities. Access is asking me to sort relationships when I go to make a form which gives the list of all these utilities on one page. But I really can't see where or why a relationship would be valid on any of them.
I can see why a product code would be related to a supplier, but none of these are directly related to eachother that I can see, the only common denomonator being that they're relating to one person.


That's it for now, but if anyone who knows access well I'd be eternally grateful if someone would take a look at what I've done so far and let me know whether it's ok or if there's any glaring errors or where it could be improved etc, as it's something I've basically learned as I've gone along from scratch in a short space of time, so I'm not brimming with confidence. Looks kinda pretty so far though, but I'm sure anyone who half knows what they're doing will look at it in horror.

Any help, pointers, links to good resources for this would be hugely appreciated.
Cheers,
Stu

Stu R

Original Poster:

21,701 posts

246 months

Monday 18th June 2007
quotequote all
anyone?

tumbleweed

fazz81

324 posts

266 months

Monday 18th June 2007
quotequote all
short answer....I think you need to go back and look at the design of your tables, once this is correct everything else will fall into place nicely.

For example, you could have a table called 'person' which has a unique ID for each entry and basic information about a person. There then could be a Bill table, this would also have a unique bill id + a foreign join to the person table using the unique person id.
On top of that, you could have a BillType table that has specific information about each type of bill. The Bill table could then have a foreign join to the BillType table.
In this design the information about a person or a bill only needs updating in one place and becuase of the joins you can pull all the correct infomation together about a person etc on a form or report.
Hope this makes sense. If not I have emailed you (via profile) a very small demo.

Regards
Simon - bored at work.

freddies_dad

12,689 posts

288 months

Monday 18th June 2007
quotequote all
Mate - I can't promise to spend very long on it, but PM me and I'll give you my work email address and have a look for you.

Stu R

Original Poster:

21,701 posts

246 months

Monday 18th June 2007
quotequote all
fazz81 said:
short answer....I think you need to go back and look at the design of your tables, once this is correct everything else will fall into place nicely.

For example, you could have a table called 'person' which has a unique ID for each entry and basic information about a person. There then could be a Bill table, this would also have a unique bill id + a foreign join to the person table using the unique person id.
On top of that, you could have a BillType table that has specific information about each type of bill. The Bill table could then have a foreign join to the BillType table.
In this design the information about a person or a bill only needs updating in one place and becuase of the joins you can pull all the correct infomation together about a person etc on a form or report.
Hope this makes sense. If not I have emailed you (via profile) a very small demo.

Regards
Simon - bored at work.
Thanks for the help mate.

Basically I've broken everything down into tables, perhaps too much, which I'm currently rethinking.
I've got a "client" table, which has all the general info (name, DOB, NI number and so on).
in another table I've got details of their family, another table for income and so on.
The table that's causing the confusion relates to utility bill details. Each of the utilities needs the same info putting in, so I've kept them all seperate but starting to think combined would work better.
Thanks for the offer of an example mate, going to spend an hour using that if poss, as the northwind one access gives you which is far far far overcomplicated for me isnt helping with hammering out what the best approach is.
It's all kind of new to me so it's taking me ages to do stuff that took me an hour when I was at uni a few years back. depressing.

Freddiesdad; much appreciated mate, I'll get a little bit more done with some alterations to what I've got done already (it's in access 2007 format so I need to change out some of the stuff to make it access2000 compatible, big oversight on my part,oops), and then I'll fire you my work so far and see what you think, it should make you giggle if nothing else lol.

Cheers guys,
Stu thumbup


Edited by Stu R on Monday 18th June 18:46

Stu R

Original Poster:

21,701 posts

246 months

Wednesday 27th June 2007
quotequote all
Ever wished you'd never left your server off when your computer's died. I do.

bring on the pro plus and coffee abuse, this ones gonna be a long one frown

Starting fresh, I guess at least I've got a bit more of an idea now, still not exactly relishing the prospect of what I can only imagine will be days infront of a PC, but there's nothing like a bit of pressure to get the work done I guess cry

Wish my missus was rich, I'd love to have subcontracted this fecker out! Give me spanners to twirl any day.