Append query nightmare!

[Reppers]

Hi,

I am creating a database for a school. I have each child's details in a table called 'tblChild', and each child's grade in a table called 'tblGrade'. As I am trying to track the child's progress through the school, the grade table contains a year column. As the database is updated each summer holiday, I would like a row created in the grade table, for each current pupil. I though that this would be possible using an append query, however, I am REALLY struggling. I created a query which filters out the pupils who have left, leaving me with just the current pupils, and have managed to get an append query to copy all of their ChildID's into the grade table. However, I need to make sure there is not a duplicated row within a certain year, although duplicate values are allowed within the field.

I appreciate this is confusing! So, as an example, I want to be able to ensure that each child for a given year, has one (and only one) row of information within the grades table. However, as each child is theoretically with us for 6 years, there needs to be duplication within the ChildID field in the grades table.

Any help would be greatly appreciated - ESPECIALLY if you can see a more logical way of doing this, even if it means re-structuring tables etc.

Thanks in advance.

 

[Kiwiman]

Howzit

You will need to set a unique index on the table you are appending the record to which will be a combination of both the Child and the Year fields- i.e. this combination can only occur once.

Do this in table design view. On a toolbar there is an icon called indexes. Give the index a name (set the index to unique) and then select the two fields that will form this index.

Any append query will not add a combination that already exists.

 

[Reppers]

Kiwiman - thanks for the quick reply!

I have found the index tool, and this is going to sound thick as I've probably missed something obvious, but how do I select 2 fields for the index? It only lets me pick one from the combo box. I'm using Access 2007.

Cheers

 

[Kiwiman]

Howzit

I don't know access 2007 but in 2003, there are 3 fields Index name, Field name and Sort order.

In the IndexName - give it a name and make it Unique
In the field name - select the first field, then select the next field in the row below (leave the indexname field blank for this row

It will look something like the attached - IndexName "SurveyPic consists of two fields CustomerID and PicName

 

[Reppers]

wonderful!

Cheers

 

[Reppers]

As a follow up to the original question, can I, as part of the append query, copy the date entered by the user (in a text box called 'DateBox' on the 'SummerAdmin' form) into the SchoolYear field on the table 'tblGrade' at the same time?

Thanks in advance

 

[Kiwiman]

Howzit

Yes - you can use the datepart function, if the value in your text box is a date,

 INSERT INTO "yourtable( "yourfield1", "Yourfield2")
SELECT 1 AS Val1, DatePart("yyyy",[Forms]![Switchboard]![txtCY]) AS Year1;
1

or if it is a year

 INSERT INTO tblTest ( StudID, Year1 )
SELECT 1 AS Val1, [Forms]![Switchboard]![txtCY] AS Year1;

 

[Reppers]

Solved!

Cheers again!