Put a total in a table?

[Alexx]

Hey- I'm just learning Access, and I am way impressed with all the things it can do, but I am running into a problem. I have set up a database with several students, and I need to keep track of their after-school hours. I thought about setting up a table with the dates as fields where I could enter in the hours like a spreadsheet. So far, no problem, but I need to total the hours. Is it possible for me to add a total column? I have tried tons of things, but I can't seem to make anything work. Thanks for any help!

 

[dk]

You can't add a total in a table. You can create a query which would give you a total for each student but you can't create a query which would include the detailed records along with a subtotal. To do that you can create a report or form to display the information you want. The Report Wizard is usually fairly easy to use and allows you to specify totals amongst other things.

Post back if you get stuck

 

[anvil19]

Hi Alexx

This, as dk has posted, is a tricky one. The ability to add a total is dependant on your grouping fields. To do this, you must add your fields, and then group them according to the data present in that field. This, however means that you will get a lot of records that are correct by SQL standards, but give you more than one row with the same field if another field has different data i.e. Same name but different dates.

You could create a query that adds the totals together using Parameters for the dates. Something like

PARAMETERS [Name] TEXT, [Start Date] TEXT, [End Date] TEXT;
SELECT * FROM Table_Name WHERE Name = [Name} and
YourDateFieldStart >= [Start Date] AND YourDateFieldLast <= [End Date]

Change YourDateFieldStart to the Field name where you wish the start date to begin looking, and YourDateFieldLast where you wish the last date to end looking. This query will find all the dates from [Start Date] to [End Date] inclusive and by name.

However, you still don't have a total. You could have another query that totals those columns

The way I would do this would be to create a form from your table and ad the fields by textbox. I have a form that adds how many people work at a site, and by selecting the site name I get a total of all staff.

You could use the name and date fields as a ComboBoxs, and add the data within the textboxes, so the code goes through all the controls on the form, checks that the data is numeric and adds the numbers together. Then it puts the total (intb) into the Label(Label57). By setting the forms OnCurrent property to this code name, every time you change data on the sheet you will get a new total in Label57.

Sub AddStaffNumbers()
    Dim ctl As Control
    Dim txty As TextBox
    Dim inta, intb As Integer
    inta = 0
    intb = 0
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Then
            ctl.SetFocus
            Set txty = ctl
            inta = txty.Text
            If IsNumeric(inta) Then
                intb = intb + inta
            End If
        End If
    Next ctl

 Me.Label57.Caption = intb

End Sub

Just change the Label number from Label57 to the number you have in your form.

Try that.

anvil19