Updating within a table

[Reppers]

Again, I feel I'm missing something obvious!

I'm making a school database. Each child has a record with their basic details (Forname, Surname, DOB, gender) in tblChild, and a record for each year they are with us containing extra information (which may be different year on year such as SEN status, eligibility for free school meals etc) in tblYearData. This data is updated throughout the year, and is needed for analysis of historical data (ie. looking at how children with free school meals performed over the last 5 years)

As the majority of information in tblYearData does not change year on year I am looking for a way to copy the information when I add a new record for a new year.

By way of example;

Tom has an SEN status of 3 and is eligible for free school meals in the year 08/09.

In the new school year (09/10) the majority of his yearly information has not changed, and so it would be quicker to copy across all the information from last year and just update the year to 09/10, and then to manually change the couple of changed items.

Is there a way of doing this - there seems as though there is - I just can't find it again!

Cheers in advance.

 

[RuralGuy]

Here's one way to do it: http://www.mvps.org/access/forms/frm0012.htm

 

[Reppers]

Cheers for the reply RG - apologies if I'm misreading, but doesn't that just apply if I'm updating the records through a form?

I'm after a query which will add a new record to tblYearData for each child currently on roll in tblChild using that child's previous record in tblYearData for all the data (aside from the year).

If your link does do that, then is there any way you could explain the steps I'd need to follow as I'm not that advanced.

Cheers again

 

[RuralGuy]

Your understanding of the link I supplied is correct. I thought you were going to change a few of the fields after the new records is created. I would think that would be achieved in a form, and as long as you are in the form why not use it to add the new record as well?

 

[Reppers]

Thanks RG, I'm with you - the fault is with my inaccurate description of what I want to do - sorry.

250 or so new records will be created in tblYearData at the start of the year. A good number of the records will remain exactly the same for whole year. Records would be updates over the year, but a couple of fields on a couple of records at a time. It's not a case of each record being changed slightly - reading back my original message, that's my fault.

In my particular case I want to essentially duplicate all the records for the previous year, changing only the year field initially (+1 on both sides - 08/09 becomes 09/10), and then I would access individual records through a form to change the records as and when.

with this clarified - any suggestions?

 

[RuralGuy]

Hmm...I do not know of an easy way to achieve what you want, sorry.

 

[Brianwarnock]

Isn't this just an append query?

Brian

 

[RuralGuy]

Isn't this just an append query?

I would agree Brian but getting the previous data for each field is the trick.

 

[Brianwarnock]

But surely
Insert into yourtablename (field1,field2,....)
Select field1,field2,...
from yourtablename
Where

achieves that?

Brian

 

[RuralGuy]

Not being very good at SQL you are probably correct assuming the OP can come up with the right WHERE clause.

 

[Brianwarnock]

Just tried it but used the design grid to save typing so got excess stuff in SQL but this worked

INSERT INTO Table1 ( fnames, f1, myr )
SELECT Table1.fnames, Table1.f1, Format(Left([myr],2)+1,"0#") & "/" & Format(Right([myr],2)+1,"0#") AS myrnew
FROM Table1
WHERE (((Table1.myr) Like "??/08"));

Brian

 

[Reppers]

Sorry to be a pain, but is there any chance of you walking me through the solution in baby steps?

Here are the details of the table;

tblYearData contains the fields;
YearDatatID
ClassID
ChildID
SchoolYear
SENID
ReadingRecovery
LookedAfter
SeriousCVA
FreeMeals
ELS
Wave3
Quest

So, I need to find the ChildID in the previous year (ie. 08/09), and copy the fields (SENID onwards) to the record with the same ChildID in the new year (09/10)

I really appreciate all the help on this.

 

[Brianwarnock]

I was creating the New Record with the data in it, however you appear to have created a New record with a minimum of data and now need to Update other fields so what we need I think is an update query. I don't have a lot of time at the moment but it would go like this Drag your table onto the Design grid twice, join on ChildID and where clause of Like "??/10" for copy1 and Like "??/09" for copy2 and then drag fields required from copy1 into the field row and create an update query with the fields from Copy2 in the update to row.

Try this but take a copy of your table first.

Brian

 

[Brianwarnock]

I'm back and reading my hurried explanation I realised I'd got things the wrong way round, also you cannot drag field names onto the Update To row.

The SQL for my little test looks like this

UPDATE Table1 INNER JOIN Table1 AS Table1_1 ON Table1.fnames = Table1_1.fnames SET Table1.f1 = [TABLE1_1].[F1]
WHERE (((Table1_1.myr)="08/08") AND ((Table1.myr)="09/09")) OR (((Table1_1.myr)="07/08") AND ((Table1.myr)="08/09"));

I suspect that you want to use the Design Grid so I did to create the above.

Steps
1 Select the table twice and join on ChildID, we will update the original not the _1 version
2 Select from Table1 all of the fields you wish to update
3 change to update Query
4 type in the fields from _1 version as shown in the SET clause above, ie [Tablename_1].[fildname]
5 Drag the year field from original version and set criteria to 08/09 and then Drag the year field from _1 version and set to 07/08 on same row of criteria, if more criterion apply as in my example create more rows as needed.

6 Run

Brian

 

[Brianwarnock]

Oh lots of swear words the penny dropped late last night that I'd been having a major senior moment with the year field treating 08/09 as a month/date field, the clue is in my field name, instead of 2008/2009.

Although this does not alter my logic it does make the coding simpler, no Like "??/09" in post 11, no Or in the last post, but I am concerned that it might have lead to confusion for anybody following this thread.

I hope Reppers understood the approach enough to ignore my mistake and solve his problem.

Brian

 

[Brianwarnock]

One further point
Since a new record is created with ChildID and SchoolYear in it wont RuralGuy's approach have worked ?

Brian

 

[Reppers]

Cheers for all the help guys - although I must admit (ashamedly) that there was an extremely simple solution to my problem!

Ultimately it was solved by using as simple append query - Initially finding all the previous year's data (08/09) and appending every field to a new record, except the school year which was simply the previous year's year +1 (if you get me!)

Problem solved. Many thanks though, and having sat and worked through what you put I have learnt a number of things, so I'm very grateful to the both of you!

Cheers!

 

[RuralGuy]

Glad we could help. Thanks for posting back with your success.

 

[Brianwarnock]

So we went back to post #9, happy to have helped.

Brian