Basic Question

[poppet]

Hi,
I haven't used Access much and I am just trying to familiarise myself with it.

Supposing I had a table of children and their ages. For each age they get a certain number of points (I know this makes no sense):

age points
1 10
2 15
3 22
4 8
5 11
(and so on up to the age of 20)

How do I implement the above age to points lookup table? Could that become an access table itself or should it be in a query when calculating the points per child (in a massive if statement)?
poppet.

[azhar2006]

Is it possible to explain more what you want .

[June7]

That could be a table. Table probably better than a long expression to calculate the points.

Implement the lookup table by building a query that joins tables on the common age fields.

[poppet]

So just to be clear...
I will have 2 access tables. The first containing the children and their ages and the second contains an age to points mapping? There will be no relation between the tables?

Is it possible to explain what you mean by build a query that joins the tables? How does one lookup values in a table? Presumably it works like a 2 dimensional array?

[poppet]

I worked it out.
Thankyou for your previous suggestions.

[orange]

poppet,

Something like this

tblChildren
ChildId
ChildName
ChildAge

tblAgePointsLkup
Age
Points

SELECT ChildName, Points
FROM
tblChildren Inner Join tblAgePointsLkup
ON tblChildren.ChildAge = tblAgePointsLkup.Age;

[azhar2006]

Wow
Very nice orange,,,,,, gorgeous

[June7]

Now lets address saving age value instead of birthdate. People grow and get older. If the child record will be worked with over several years, should save birthdate and calculate the age when needed.

[poppet]

Hi, thanks for the replies.
I haven't used SQL for a few years. It is coming back to me now.
I work in the education sector and I am working on a small project for the students at the moment. The project is an insurance problem and it has 5 of these small lookup tables. Age of client vs. fee, health of client vs fee etc. It seems to me that the above implementation is a great way of implementing a small lookup table.

(Yes, using age will cause problems in the long run).

[orange]

My example was simply an example.

Do NOT use Age in your table. Use DateOfBirth, then calculate Age when you need it.
It isn't a long term issue, it is a design issue. Change it now while it's fresh in your mind. It will save you frustration later.

Good luck with your project.