Struggling with this Query

[yus786]

I have 2 tables

tblStudents
tblFees

tblStudents has all the info on the students and the StudentID is the PK
tblFees is empty. Field1 is StudentID, Fields2 to 10 are the 'Periods'

The idea is to enter amounts in here once they have paid.

I'm trying to build a query that will take the StudentID from the tblStudents and drop it into tblFees.

I need to run this query everyweek and would like the query to add onto tblFees any new starters.

What type of query should i build and how?

Many thanks

 

[maxmangion]

Hi,

Ideally you shouldn't have 9 different fields in a table to store a same thing i.e. fees (this certainly break the rules of normalisation).

What you can do is that you build the fees table with three fields, feeId, Fee and studentid, then you can use a query which groups your records by studentId and will add the fee field for that student.

 

[yus786]

Hi,

Ideally you shouldn't have 9 different fields in a table to store a same thing i.e. fees (this certainly break the rules of normalisation).

What you can do is that you build the fees table with three fields, feeId, Fee and studentid, then you can use a query which groups your records by studentId and will add the fee field for that student.

Thanks

I think you misunderstood my 9 'Period' Fields

They are not the same but relate to each Period (month) of the year. So Period1 would be August, Period2 Sep etc.

 

[georgedwilkinson]

The same thing applies. It is not normalized and will likely give you problems, if this is going to be a working OLTP database.