Automatic Transfer of Data from One Table to Another?

[Tyria]

PLEASE ANSWER ASAP!!!

I was wondering if there was a way to automatically transfer data from one table into another. Specifically, if I have two tables, one called Students and one Alumni, is there a way I can click a button or something to transfer a student's data file from the Students' table into the Alumni table? Without having to delete the student's file from Student and retyping it all into Alumni?

Also, is there a way to, for instance, type individual monetary amounts into a single entry in a table and have the total automatically summed? Like, if I have a table for Donors, and every time one person makes a donation, add that one donation to their personal information and have another field that gives the total amount of all of their donations?

Thanks!

 

[Mile-O]

Why do you need two tables? Why not just stick a checkbox into the Students table to determine whether they are alumni or not?

 

[Uncle Gizmo]

Also, is there a way to, for instance, type individual monetary amounts into a single entry in a table and have the total automatically summed? Like, if I have a table for Donors, and every time one person makes a donation, add that one donation to their personal information and have another field that gives the total amount of all of their donations?

First of all you shouldn't be typing entries straight into tables. You should use forms for data entry.

If you had a form! You could add a text box to it with this function within it and it would display the total for the selected person. You need to pass to this function the ID of the person that you are viewing.

Public Function fSumPayments(lngConID As Long) As Currency
'http://www.oreilly.com/pub/h/3323
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String

strSQL1 = "SELECT Sum(PaymentAmount) AS SumOfPaymentAmount "
strSQL2 = "FROM tblDonations "
strSQL3 = "GROUP BY ContID "
strSQL4 = "HAVING (((ContID)="
'430
strSQL5 = "))"
  
  strSQL = strSQL1 & strSQL2 & strSQL3 & strSQL4 & lngConID & strSQL5
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim rs As New ADODB.Recordset
  rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
  'MsgBox rs.Fields("SumOfPaymentAmount")
  fSumPayments = rs.Fields("SumOfPaymentAmount")
  rs.Close
  Set rs = Nothing
  Set conn = Nothing
End Function

Usage:
call the function from a text box control source:

=fSumPayments([txtContID])

where txtContID contains the person's ID.

 

[Tyria]

The only reason I needed two tables is because I'm doing this as a project and my supervisor prefers it. I'll bring the check box thing up to him, though.

And I do use forms, but that function thing is way over my head. You could say I'm an Access n00b. What exactly do I need to do with that?

Thanks so much!

 

[Rich]

Use a Totals query to Sum individual amounts and an Append Query if you must transfer data from one table to another

 

[boblarson]

The only reason I needed two tables is because I'm doing this as a project and my supervisor prefers it.

The reason, I've found, that people want to have two tables is that they do not understand relational databases and how to generate queries that pull everything they want. It is a comfort-level thing but if they had the knowledge of how do design databases properly, and how to use them, they would have a lot less problems getting the information out that they want. As it usually winds up they will work with something that APPEARS, at least on the surface, to be easier but in the long run will become a source of pain and suffering.

 

[Uncle Gizmo]

>>> but in the long run will become a source of pain and suffering. <<<<

And not just to them!

 

[boblarson]

>>> but in the long run will become a source of pain and suffering. <<<<

And not just to them!

A BIG AMEN to that!

 

[The_Doc_Man]

There is always an oddball viewpoint around here. It doesn't bother me to be that oddball.

To the direct question:

If you wanted to move data from the students table to the alumni table, you would have to do the following.

1. Establish criteria for selection. For instance, add a Yes/No field to the student tables. Initially set it to NO. Run an update query that sets to YES the flag for any student who has become an alumnus.

2. Use an append query to copy all student records to the alumni table if they are marked YES.

3. Use a delete query to erase all student records marked YES. DO NOT RUN THIS QUERY UNTIL YOU ARE SATISIFIED WITH STEP 2.

Now, why would you do this? If the Alumni table contained a LOT more data than the student table - or a lot less. If they are the same, then I'm with those who said "flag them rather than move them." There is also the issue that if the school has a graduate program, an alumnus could be a student again. Think about that one for a while before you split the tables because if you do, you might have a really big issue looming ahead.

 

[hmoody]

Why do you need two tables? Why not just stick a checkbox into the Students table to determine whether they are alumni or not?

If you use a checkbox, how would you record the year of grad?

Thanks!