Getting started on Access
Discussion
Chaps,
I am trying to make a database. I have never made one before, but know exactly what I want it to do, have planned out the various objects I want to be in the database and the relationships between them. I know the data I want each object to contain, and how they will relate to each other, and the questions I want to be able to ask of it. That's (to me) the easy part.
I then thought that I could knock up the database itself in a cool 20 minutes on Access. Trouble is, I have run into a fairly fundamental problem, and I think this is only the first of many. I'm more than happy to describe the problem, but I think that if this one was solved then I would simply come across another one ... and another one ... and so on, and it would get tiresome for people to answer a load of dumb-ass questions.
My problems are to do with getting the various things in the database to relate to each other properly. I can set up (very) simple relationships on Access, but they don't do what I want. Access also has a brilliant sample database set up - "Northwinds" - which is wonderful, but they tell you diddly-squat about how to set up something like this. I can see what it does, but not how to make it do it.
So, can anyone recommend a good site for learning to use Access? Something that explains how to achieve the different types of relationship and how to set them up. To give you an idea of where I am at the moment, I am trying to set up a system to keep track of people (children) taking part in activities. You would set up a family ("family" objects) and then set up individual children ("children" objects), and link them to particular families, so that you can see who is related to who, and only have to enter addresses and so on once. With me so far? The trouble is that I can set up families, and children, and look up the family name (surname) for the children, but can't see how to inherit (hey - technical huh!) the family address to the individual children. That's the sort of level of competence I have ... pretty sad story, eh?
So, if you have any good ideas of places to look for help, I would be most grateful to help. (Or, failing websites, good books to borrow or buy.) This is all a bit more embarassing 'cos I have a degree in Computer Science, but have never made a database in my life before ...
Oli.
I am trying to make a database. I have never made one before, but know exactly what I want it to do, have planned out the various objects I want to be in the database and the relationships between them. I know the data I want each object to contain, and how they will relate to each other, and the questions I want to be able to ask of it. That's (to me) the easy part.
I then thought that I could knock up the database itself in a cool 20 minutes on Access. Trouble is, I have run into a fairly fundamental problem, and I think this is only the first of many. I'm more than happy to describe the problem, but I think that if this one was solved then I would simply come across another one ... and another one ... and so on, and it would get tiresome for people to answer a load of dumb-ass questions.
My problems are to do with getting the various things in the database to relate to each other properly. I can set up (very) simple relationships on Access, but they don't do what I want. Access also has a brilliant sample database set up - "Northwinds" - which is wonderful, but they tell you diddly-squat about how to set up something like this. I can see what it does, but not how to make it do it.
So, can anyone recommend a good site for learning to use Access? Something that explains how to achieve the different types of relationship and how to set them up. To give you an idea of where I am at the moment, I am trying to set up a system to keep track of people (children) taking part in activities. You would set up a family ("family" objects) and then set up individual children ("children" objects), and link them to particular families, so that you can see who is related to who, and only have to enter addresses and so on once. With me so far? The trouble is that I can set up families, and children, and look up the family name (surname) for the children, but can't see how to inherit (hey - technical huh!) the family address to the individual children. That's the sort of level of competence I have ... pretty sad story, eh?
So, if you have any good ideas of places to look for help, I would be most grateful to help. (Or, failing websites, good books to borrow or buy.) This is all a bit more embarassing 'cos I have a degree in Computer Science, but have never made a database in my life before ...
Oli.
Not used it myself, but the "Dummies" range of books might be a good place to start - they should do an "Access for Dummies" book.
I think what you're trying to do is the following:
Table - family
Family name
Address
Table - child
Family name
First name
You'd link the family name from the family table to the child table, so the query to get a child's address would be
select family.address
from family, child
where family.family_name = child.family_name
and child.family_name="Smith"
(sql format is tablename.fieldname)
which translates as give me the address (from the family table) where the family name in the family table is the same as that in the child table, and where this name is "Smith".
hth
I think what you're trying to do is the following:
Table - family
Family name
Address
Table - child
Family name
First name
You'd link the family name from the family table to the child table, so the query to get a child's address would be
select family.address
from family, child
where family.family_name = child.family_name
and child.family_name="Smith"
(sql format is tablename.fieldname)
which translates as give me the address (from the family table) where the family name in the family table is the same as that in the child table, and where this name is "Smith".
hth
Mr Hasler,
Thanks for replying! We have tangled on here before, and you have been most helpful then as well.
You are using a query for your description. I haven't go to grips wih these before, which could be where I am going wrong. You are working on the basis that it is not not possible to simply take the information you need directly from the family table, you need to make a single-point link and then run a query to take the rest of the information.
That is not how I was trying to do it, and could be the basis of the problem ... I'll see what I can do, thanks!
Oli.
Thanks for replying! We have tangled on here before, and you have been most helpful then as well.
You are using a query for your description. I haven't go to grips wih these before, which could be where I am going wrong. You are working on the basis that it is not not possible to simply take the information you need directly from the family table, you need to make a single-point link and then run a query to take the rest of the information.
That is not how I was trying to do it, and could be the basis of the problem ... I'll see what I can do, thanks!
Oli.
Ed's example does, however, assume that all children with the same family name belong to the same family.
If you need to separate out families with the same surname, then you'll need an extra field in the family table that uniquely identifies the family (e.g. an Access autonumber) and then have another field in the Child table to link to that new field, e.g.:
Table - family
Family name
Address
ID
Table - child
Family name
First name
FamilyID
If you need to separate out families with the same surname, then you'll need an extra field in the family table that uniquely identifies the family (e.g. an Access autonumber) and then have another field in the Child table to link to that new field, e.g.:
Table - family
Family name
Address
ID
Table - child
Family name
First name
FamilyID
I would try www.utteraccess.com
it's a great forum for all things Access with a huge wealth of shared knowledge & people very happy to help out (a bit like PH really).
Your first question should be "What is data normalisation?"
that should get you started on the right 'relational' track
it's a great forum for all things Access with a huge wealth of shared knowledge & people very happy to help out (a bit like PH really).
Your first question should be "What is data normalisation?"
that should get you started on the right 'relational' track
pdV6 said:
Ed's example does, however, assume that all children with the same family name belong to the same family.
If you need to separate out families with the same surname, then you'll need an extra field in the family table that uniquely identifies the family (e.g. an Access autonumber) and then have another field in the Child table to link to that new field, e.g.:
Table - family
Family name
Address
ID
Table - child
Family name
First name
FamilyID
agree as better way. always use indexes as unique identifyer for correct binding
I would suggest the following:
Table: Parent
ParentID (autonumber) (Primary Key)
Name text
aother attributes...
Table: Child
ChildID (autonumber) (Primary Key)
ParentID integer
Name text
aother attributes...
You can then create a relationship between child and parent (1 parent to many children) and you will know which children belong to which parent.
On the subject of relationships, Access supports 1:1, 1:M and M:M, but if your relationship is anything other than 1:M (many), you have almost certainly done something wrong!
Access is pretty easy to pick up as you go along, so I would recommend buying a reference book instead of a 'teach yourself...' book. This way you can adapt simple non-ongoing samples into what you are doing.
Give me a shout if you need any more help.
Table: Parent
ParentID (autonumber) (Primary Key)
Name text
aother attributes...
Table: Child
ChildID (autonumber) (Primary Key)
ParentID integer
Name text
aother attributes...
You can then create a relationship between child and parent (1 parent to many children) and you will know which children belong to which parent.
On the subject of relationships, Access supports 1:1, 1:M and M:M, but if your relationship is anything other than 1:M (many), you have almost certainly done something wrong!
Access is pretty easy to pick up as you go along, so I would recommend buying a reference book instead of a 'teach yourself...' book. This way you can adapt simple non-ongoing samples into what you are doing.
Give me a shout if you need any more help.
Access supports M:M relations?
If Ted Codd was dead I am sure he would spin in his grave.
Whatever happened to resolution entities?
These kids, bucking the rules, I dont know
Oli, if your data structure isnt sensitive put the business rules up here. You'll have a fully specified relational database within minutes I would have thought...
If Ted Codd was dead I am sure he would spin in his grave.
Whatever happened to resolution entities?
These kids, bucking the rules, I dont know
Oli, if your data structure isnt sensitive put the business rules up here. You'll have a fully specified relational database within minutes I would have thought...
plotloss said:
Oli, if your data structure isnt sensitive put the business rules up here. You'll have a fully specified relational database within minutes I would have thought...
I'd have thought you'd have at least 3 different solutions and 17 different reasons why they are all wrong.
Not that I am saying PHers are argumentative...
hang on, yes I am!
). Chaps,
Lots of excellent help, and I am genuinely grateful for all of them.
I have the database fully specified, and am happy about what it is meant to do. The next and difficult stage is actually building it ... and I'm an obstinate and persistant bugger and would like to solve it myself! Partially for the educational experience and partially for the sheer bloody-minded satisfaction of having done it.
Part of the trouble seems to be that there are half-a-dozen ways of doing things, even within Access. I have worked out what what I want to do is an autocomplete query, and Access provides a natty little interface for setting one up, but the help system is a pile of steaming manure. Alternatively, it seems you can write queries in SQL (which I have never seen before but am getting to grips with swiftly).
Either way, thanks for your help. What would be really helpful would be a list of useful reference websites for Access. I have tried the usual Google and Ask Jeeves (rather naff that one) searches, but to no great avail. I think that part of the problem is that I don't know the proper terminology for what I am trying to do - finding the phrase "Autocomplete Query" was, in itself, a good step forward. I tried utterlyaccess, but without great success.
Oli.
Lots of excellent help, and I am genuinely grateful for all of them.
I have the database fully specified, and am happy about what it is meant to do. The next and difficult stage is actually building it ... and I'm an obstinate and persistant bugger and would like to solve it myself! Partially for the educational experience and partially for the sheer bloody-minded satisfaction of having done it.
Part of the trouble seems to be that there are half-a-dozen ways of doing things, even within Access. I have worked out what what I want to do is an autocomplete query, and Access provides a natty little interface for setting one up, but the help system is a pile of steaming manure. Alternatively, it seems you can write queries in SQL (which I have never seen before but am getting to grips with swiftly).
Either way, thanks for your help. What would be really helpful would be a list of useful reference websites for Access. I have tried the usual Google and Ask Jeeves (rather naff that one) searches, but to no great avail. I think that part of the problem is that I don't know the proper terminology for what I am trying to do - finding the phrase "Autocomplete Query" was, in itself, a good step forward. I tried utterlyaccess, but without great success.
Oli.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff