Appending data from different tables into a form

[Shimsham]

Hi

This is my first post to the forum so please excuse me if I don't express my request for asistance clearly. I am a "newbie" to Access and have learnt a few things since I began using it three months ago. However, it seems that I might be in need of some assistance.

I have 3 separate tables with the following fields: Year; Name of School; Emis No. (Primary Key); No. wrote; No. passed; Pass %. These tables were created in Excel and imported in Access.

I would like to do the following (see table below): 1. create a form that would allow me to view all the data per school in datasheet view and also allow me to enter new data annually; 2. create a new table where this information can be stored.
YEAREMIS NOSCHOOLNO WROTENO PASSEDPASS %2009109964Bhuqwini4012302010109964Bhuqwini6126432011109964Bhuqwini6422342012109964Bhuqwini





Any suggestions on how I could go about doing this would be appreciated as well as any suggestions.

Kind regards

 

[John Big Booty]

If your tables are properly Normalised you should be able to use a query to draw your data together. You can then use that query as the Record Source for your form.

 

[Shimsham]

Hi John Big Booty

Thanks for your advice. Have managed to get the first part of what I need to do working i.e. combine all three tables using a query and then creating a form based on the query. I am still struggling though on how to allow for updates on the form i.e. inputting data for future years. As I indicated in my query, I have data for the past three years but going forward would need to capture information for the currrent year and future years. Any suggestions? Regards

 

[Aleni]

Hi Shimsham!
You can use a button to add new record.
In click event of the button add this code.

If IsNull(txtItem) Then 'validation rule to not to accept blank record
MsgBox "Please fill the required fields", vbInformation
txtItem.SetFocus
Else
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset

strsql = "SELECT * FROM tblAcqDetails"
rs.Open strsql, cnn, adOpenKeyset, adLockOptimistic

With rs
rs.AddNew
!fldQty = Me.txtQty
!fldUOM = Me.txtUOM
!fldType = Forms!frmAddNew.cbo1
!fldNote = Me.txtNote
!fkItemID = intItemID
rs.Update
End With
Set rs = Nothing
Set cnn = Nothing

MsgBox ("New record is successfully added")
End If

Hope this will help you or at least give you some idea.