Multiple Tables/One Form RRS feed

  • Question

  • I am in the process of building an accounting database for our county fire department sales tax using Access 2010.

    I have 17 different tables, one for each department.  Each month the monies collected thru the tax is split equally between each department in monthly appropriations.  This is the only time that each department will have the same data entered into their respective table.  Each department has a form for entering the other monthly expenditures.

    My question is, how do I create a form to where I can input their monthly appropriation once and it populate it to all 17 tables without having to enter into each of their accounts manually?

    Any help would be appreciated.

    Friday, June 7, 2019 3:09 PM

All replies

  • Having a table for each department encodes data as table names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.  The correct design would be a single table which includes a DepartmentID or similar foreign key column as a foreign key referencing the primary key of a Departments table.

    You can then insert 17 rows into the single table by means of an unbound form with text box controls to enter the data, and a command button to build and execute an 'append' query e.g.

        Dim strSQL As String
        Dim strInsertList As String

        strInsertList = Me.txtA & "," & Me.txtB & "," & Me.txtC & "," & Me.txtD

        strSQL = "INSERT INTO TableNameGoesHere(DepartmentID,a,b,c,d)" & _
            " SELECT DepartmentID," & strInsertList & _
            " FROM Departments"

        CurrentDb.Execute strSQL, dbFailOnError

    where a, b, c and d are columns in the table, and txtA,t xtB, txtC and txtD are the text box controls in the form.  I've assumed here that all columns are of a number or currency data type.  For columns of text data type, when assigning the value to the strInsertList variable, the values of the controls would need to be wrapped in literal quotes characters, each represented as a contiguous pair of quotes characters. "", e.g.  

        ",""" & Me.txtB & ""","

    For columns of date/time data type the values would need to be wrapped in the date delimiter character, #, and the values would need to be formatted in US short date format or an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD, e.g.

        ",#" & Format(Me.txtC,"yyyy-mm-dd")  & "#,"

    Ken Sheridan, Stafford, England

    Friday, June 7, 2019 4:00 PM
  • Describing how to do this in this forum would be difficult. You need only 2 tables but the process of updating each department allocation is complicated to describe in words. I spent a little time and developed a very small sample database based on your requirements. You can download it here from my OneDrive. You also need the ability to add/delete Appropriation dates and Departments so I included that. Each Appropriation date allows you to enter the amount and it will then allocate amounts to each department based upon the number of departments defined. If there are 17, then the amount is divided by 17, but departments can be added or deleted so it uses the number of departments rather than a fixed number to divide by. I input 17 departments. It will also keep new allocations for each department. See if something like this is what you are needing. It is version 2007 but since you have 2010, you should be able to open and use it. I didn't know what other information you want to input and keep but you can always added fields. Hope this can help you.

    Saturday, June 8, 2019 5:58 PM