update a record using previous data

[capdownlondon]

I have a table that keeps track of targets and points for kids at the school i work at and the table has these fields:

-Name
-week starting
-target 1
-target 2
-target 3
-points 1
-points 2
-points 3
etc......

Okay, now what i need to do:
Now lets use little Johnny as an example.
I entered Johnnys Targets at the begining of the week using a form.
And his points on relevant days on a seprate form.
Now Johnnys Targets don't change that often, say once a month, sometimes more often, sometime less.
Is it possible to use a query, or say create a button on a form to when clicked or run will:
-search for Johnny's(or any other child whos record it is) previous weeks Targets.
-create a new row in the table with the same Targets for Johnny, but with the next week start and blank points fields.

 

[dhookom]

I would not spend any more time on this table until it was normalized. Is there any way that you can change your table structure?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[capdownlondon]

i am currently self teaching this and don't know what you mean?
normalized? how else could i structure it.
I have the following tables?

Category
-CatId
-Category

Targets
-TargetId
-Target
-Category

Week Start
-WeekID
-Week Start

then....

Child details
-ChildID
-Name
-Primary Carer
-Address 1
-Address 2
-Post code
-On roll (yes or no)

All this i believe to be structrued fine, i don't know though?

Then.....
The Weekly points table, which i know could be structured better, but i couldn't work out how.

It is as follows:

Weekly Points
-WeeklyID
-Childs Name
-Week Start
-Taregt 1
-Target 2
-Target 3
-Target 4
-Target 5
-t1m1
-t1m2
-etc

t1m1, and t1m2 etc stands for target1monday lesson 1, target1monday lesson 2 etc.
So as you can see there are over 125 fields in that table (5 lessons a day x 5 days) x 5targets

any ideas????

 

[PHV]

there are over 125 fields in that table (5 lessons a day x 5 days) x 5targets
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Jeff Conrad has several links at his recently moved page (update your favorites and links)

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]