Completely lost in creating this kind of form 3

[johnyrm]

I am quite familiar with simpler functions of Access and creating simple to medium difficult/structured forms. I was now given an assignment that looked simple enough, but I have been trying really hard to come up with a working solution for a few days with no success.

Let me explain: I have three basic criteria: organisations (about 40 entries), cost fields (about 30 entries), months (12 entries). What I have to do is come up with a form for entering data for each organisation, with the whole year (12 months) and all cost fields (about 30) opened at the same time (something similar like an Excel table for each organisation).

(To simplify, I'll leave unimportant fields in this description out)
I started by creating a table Organisations with an ID and name field, and entered the 40 organisations.
I also created a table Cost_Field with Cost_ID, and description, and entered the 30 cost fields.
Then I created 12 additional tables, each containing autonumber, ID (Dropdown menu linking to Organisations table -> ID field), and 30 columns (Cost_Field entries).
I don't like this structure, as it doesn't automaticall change if one Cost_Field changes, or gets removed, or if something is added. I also experimented with some other variations, but I cannot get any solution that would work and get the desired form result.

Again, I am obliged to follow the instruction to get the form (which will be used to enter data) sorted by Organisations (each organisation is a separate entry), with 12 months times all Cost_Fields (table-like) listed on the same page.

What I'd like to ask if you have any suggestions/solutions how to:
1st - create tables
2nd - set relations so that I'll be able to create a report that would meet the requirements listed above

Thank you for your help!

 

[willir]

It sounds like you have an issue with your schema.

Some questions:

Does each organization have it's own cost codes? Or are the cost codes applied for each organization?

The monthly entries -- are these are cost codes for each month? Does each organization have it's own entry for each cost code?

I suspect the reason you are having a problem is that you probably have not identified the relationship one-to-many or many-to-many for Organization and CostCodes.

Post back with answers to the questions asked and others and myself will be able to help you?

Richard

 

[TheAceMan1]

How are ya johnyrm . . . . .

[blue]Willir[/blue] is on target here. Since this is from scratch, [purple]we need a foundation of what your trying to accomplish and how things relate to each other[/purple] ([purple]similar to everyday use without the database[/purple]). With this info it'll be much easier to translate your needs to tables & relationships.

At your leisure, have a look at the following links:

Normalizing Tables Table Relationships Fundamentals of Relational Database Design

See Ya! . . . . . .

 

[johnyrm]

To answer willir:

1. all organisations share the same cost codes (the Cost_Field table is to be used with all organisations)

2. monthly entries are costs divided into cost fields (codes/areas). In the month table I created (which I doubt is OK), each organisation has its own entry for each cost field.

And to TheAceMan1:

I'll try to explain again what I have to come up with - I need to create a form where it would be clear how much money each organisation spent on each cost field (code/area).
The layout has to be table/spreadsheet like, listing all 12 months with all cost fields at the same time. This will be where the data will be entered.

Also thank you for the posted links; I found part that refreshed my memory, and some information was relatively new to me.


Thank you for for replying; I hope you'll be able to guide me to my solution as well.

 

[OhioSteve]

Johnyrm,

I can almost guarantee that Willir is correct: your database is not normalized. You need to recreate the entire application.

I recognize that it is frustrating to get a complex answer when you are seeking a simple answer. Unfortunately in this case we can't give you a simple solution.

If you post a description of all of the stuff you want to store, then we could get you started on a schema.

 

[OhioSteve]

The first step to creating a schema is to define the process that you are automating. Give us a brief, precise (about 50 word) description of the process that you are automating. The second step is to define entities. Entities are usually the nouns in your description. In your case organizations and months are probably entities. Since you are storing money info, you probably also have an entity for economic transactions (something like "sales" or "purchases").

I recognize that you want to output costs per month. Don't worry about that yet.

 

[johnyrm]

First, let me just say that I really appreciate your help; effort and time-wise.

I am trying to get an overview of expenditures of several organisations according to the cost fields (economic transactions, as you said) for each month. For more info, please read the first post again.

My end product (the form) has to look exactely as described in posts above.

I also have to mention that English is not my mother tongue, I am using a non-English version of MS Access 2002, so forgive my terms if they're not on the spot...

 

[OhioSteve]

Johnyrm,

I can understand your English perfectly. If you cannot understand a word that I use, tell me.

So far I only see two entities (nouns) in the process you describe: organization and expenditure. Perhaps in time we will discover other entities but we will start with those two. The next step is to determine the attributes of each entity. An attribute is like a characteristic. Here is an example of your two entities with some fictitous attributes:

organization
ID
name
contact name
telephone number

expenditure
ID
amount
date
reason

You need to tell us the real attributes.

We also need to develop a more precise definition of expenditure. You keep using the term "cost". Can the database store individual transactions? Or is someone giving you a spreadsheet with aggregate values?

 

[johnyrm]

OK, you got the first entity (organisation) on the spot and I already have a table like that. The second entity (expenditure), is however a bit different:
ID
amount
cost_field

You see, this will not be about individual transactions, but only the sum of transactions of each cost_field (let's say all costs for transport in one month will be entered in one field).

 

[willir]

johnyrm

Sorry about getting back to you so late -- the Tek-Tip web site seemed to ahve crashed while I was creating a post, and was down for a while.

Any way, your Organization table is fine. The trick is to realize that your CostCodes is a separate table, and that you have a type of many-to-many relationship between Organizations and CostCodes. This requires a "joiner" table. The twist is that you need to also capture the amount, and month. I will go further, you probably need to capture the year too.

The way ot works...
For each organization, using the CostCode table as a template, generate blank entries for the "joiner" table for each "month" using the CostCode table as a template. This will generate 30x12 records for each organization for each year. The "joiner" table is a type of "transaction" table.

Using this approach over using 30 fields, one for each CostCode will make it much easier to create your queries, will be easier to maintain, and will get away from a lot of hard coding.

tblOrganization
OrganizationID - primary key
OrganizationName

tblCostCode
CostCode - primary key, text string
Active - boolean yes / no

Discussion:
I have made the actual CostCode the primary key. This has some advantages in that once you have created your "transaction", you do not have to include/join this table in your quries. IF the CostCodes can change over time, then change the design to...

CostCodeID - primary key
CodeCode
Active
tblOrganizationCosts - this is the "joiner" table
OrganizationID - foreign key to tblOrganization
CostCode - foreign key to tblCostCode (or use CostCodeID)
TransDate - date field
TransAmount - currency

Primary key is OrganizationID + CostCode + TransDate

Discussion:
The OrganizationID + CostCode are traditional for the design on the many-to-many "joiner" table. The TransDate is the odd thing about this table. You want to ensure that you have only one CostCode entry for each month.

Why use a date field to track the month? Well, I can easily see that you may want to track costs over several years, and by using a date field, you can use the date for the month and month + year.

Creation of tblOrganizationCosts records.

You can do this several way, but here is one way using DAO. The code is run from a command button on the form for an Organization.

Assumptions:
frmOrganization - Main form based on tblOrganization
sbfrmOrganizationCosts - subfrm, contineous, based on tblOrganizationCosts
cmdCreateTransactions - command button on frmOrganization used to initialize the create of entries in tblOrganizationCosts
CreateTransactions - function used to create tblOrganizationCosts records. This function is called by cmdCreateTransactions but can also be called else where, i.e., create records for all organizations instead of just one organization.

Code for the command button cmCreateTransactions

Private Sub cmdCreateTransactions_Click()

Dim datTest As Variant, booTest As Boolean

If Nz(Me.OrganizationID, 0) Then
    datTest = InputBox("Enter Transaction Year", "Year For Transaction", Year(Date))
    datTest = "01-01-" & datTest
    If IsDate(datTest) Then
        booTest = CreateTransactions(Me.OrganizationID, DateValue(datTest))
        
        If booTest Then
            Me.sbfrmOrganizationCosts.Requery
        Else
            MsgBox "Creation of transactions failed"
        End If
    Else
        MsgBox "Invalid Year entry"
    End If
Else
    MsgBox "Must have a valid Organization record first"
End If

End Sub

Create a module and add the following function..

Function CreateTransactions(lngID As Long, datYear As Date) As Boolean

Dim dbs As DAO.Database, rstC As DAO.Recordset, rstT As DAO.Recordset
Dim strSQL As String, booPass As Boolean, strMsg As String
Dim intYear As Integer, intX As Integer

booPass = True

If Not IsDate(datYear) Then
    booPass = False
    strMsg = "Invalid date: " & datYear
End If

If DCount("OrganizationName", "tblOrganization", "OrganizationID = " & lngID) = 0 Then
    booPass = False
    strMsg = strMsg & vbCrLf & "Invalid OrganizationID: " & lngID
End If


If booPass Then
    intYear = Year(datYear)
    If DCount("TransDate", "tblOrganizationCosts", "Year(TransDate) = " _
    & intYear & " and OrganizationID = " & lngID) > 0 Then
        booPass = False
        strMsg = strMsg & vbCrLf & "CostCode entries already exist for: " _
        & vbCrLf & "Oragnization: " & lngID & " for Year: " & intYear
    End If
End If

If booPass Then
    strSQL = "SELECT CostCode From tblCostCode WHERE Active = True ORDER BY CostCode"
    Set dbs = CurrentDb()
    Set rstC = dbs.OpenRecordset(strSQL)
    Set rstT = dbs.OpenRecordset("tblOrganizationCosts")
    
    With rstC
        .MoveFirst
        Do While Not .EOF
            For intX = 1 To 12
                rstT.AddNew
                    rstT!OrganizationID = lngID
                    rstT!CostCode = !CostCode
                    rstT!TransDate = intX & "-01-" & intYear
                rstT.Update
            Next intX
            .MoveNext
        Loop
    End With
    
    CreateTransactions = True
    rstT.Close
    rstC.Close
    dbs.Close
    Set rstT = Nothing
    Set rstC = Nothing
    Set dbs = Nothing
    
Else
    MsgBox strMsg
    CreateTransactions = False
End If

End Function

The code can be tightened up, but hopefully you see what I am getting at.

Presentation
Presentation will be important here. You have a lot of records, and want to display the data in a managable way. The above code was obviously me just trying to get a possible solution for you where I used a contineous form.

Other approaches may be to...
- Use an unbound combo box to select the year
- Use a second unbound combo box to select the CostCode
- Instead of a contineous subform form, use a single form subform with 12 text boxes, one for each month
- The end user can now select the year from the combo box. Coding behind the combo box will ensure the subform will display the selected year.
- The end user can also select the CostCode (with or without the year). Coding behind the combo box will ensure the subform will display the CostCode for all years or the selected year.
- The end user can also scroll though the CostCodes or Years by using the navigation controls on the subform.

Instead of the single form, you can also use the contineous subform, but add control buttons on the subform to scroll to the next year or next cost code.

Most of the above reliese on changing the RecordSource of the subform and using the Requery method...
strSQL = "SELECT ....
Me.sbfrmOrganizationCosts.RecordSource = strSQL
Me.sbfrmOrganizationCosts.Requery

Hopefully, this help[ed. And if so, this should keep you busy for a while.

Richard

 

[OhioSteve]

I agree with Johnyrm. However, he skipped the conceptual steps and just gave you a solution. It is important to understand why his solution is accurate.

You have two entities, costs and organizations. How do we link them? There are two ways to link entities: one-to one and one-to-many. Let me give you some examples of each type of link.

In a one-to-one link, each record in the first entity links to one (and ONLY one) record in the other entity. Examples of one-to-one relationships include: nations and capitols, integers and squares of integers, fingerprints and persons.

In a one-to-many link, records in the first entity may or may not link to records in the second entity. Also, individual records in the first entity can link to more than one record in the second entity.

Examples of one-to-many links include women and children, countries and rivers, or stars and planets. In each case, the first thing could match with several instances of the second thing. A woman could have zero children or many, a country could have zero rivers, or many, etc.

Now, think about that same terminology and your task. Unfortunately, your situation does NOT match either of my examples. An organization can have many costs, but a cost can occur in many organizations. So from what I have said so far, your task would seem impossible.

Luckily, it is not impossible. You need to create the "joiner" table that Johnyrm mentions. That entity will have a one to many relationship with both costs and organizations. When Johnyrm uses the term "primary key", he means the "one" part of the one-to-many relationship. When he uses the term "foreign key", he means the "many" part of the the relationship.

 

[johnyrm]

An enourmous THANK YOU for the detailed instructions. I am under some additional deadlines, so I'll probably be looking into this with my full attention tomorrow. I'll get back to you with the outcome.

Thanx again!

 

[pbrodsky]

Don't forget that your database structure (your tables and relationships) does NOT need to match the way your forms look. You can write code and/or queries enter or display data from forms that look any way you need them.

 

[johnyrm]

I wasn't able to access the internet during the weekend but I had some time to work on this.

Unfortunately, I did not manage to get wilir's suggestion working. I tried both updating my own tables and forms as well as creating them from scratch.

I created all three tables, set the relations, created the forms, but I think I messed it up with the cmdCreateTransactions.

I know this is not the usual process, but woudl it be possible that someone (maybe wilir?) would take a look into my file?

My email is j_o_h_n_y_at_r_o_c_k_e_t_m_a_i_l_._c_o_m (without underscores and at=@)

My deadline for completing this project is tomorrow morning. Impressive, huh

Seriously, though, I'm desperate and your help would be very appreciated.

 

[johnyrm]

I would like to thank all of you who helped with your inputs, and I would especially like to thank willir as he helped me tremendously.

If you are wondering what I did wrong-it was the coding:
- I assigned the same name to the module and the function (each has to have a unique name)
- I defined OrganizationID as text field, and wilir had it numeric, so he did some changes (quotes, strQ)

Willir also helped me with setting some filters which solves the user-friendly issue.

With your help, I managed to file in this project, even though a bit late.

 

[willir]

Thanks for the star Johnny

For the record, with 30 cost codes for each period is a lot of records, so a way of easily managing the data was required. The filters were created by using a central subroutine...

Private Sub ApplyFilter()

Dim strD As String, strQ As String
Dim datID As Date, strID As String, strCostID As String
Dim strSQL As String

If Len(Nz(Me.OrganizationID, "")) Then
    
    strD = "#"
    strQ = Chr$(34)
    
'   Build basic SQL for subform

    strID = Me.OrganizationID
    strSQL = "SELECT * FROM tblOrganizationCosts WHERE" _
    & " OrganizationID = " & strQ & strID & strQ
    
'   If a date / period / mmm-yyyy is selected, refine query
    If IsDate(Me.cmbDateQry) Then

        datID = Me.cmbDateQry
    
        strSQL = strSQL & " and Month(TransDate) = " & Month(datID) _
        & " and Year(Transdate) = " & Year(datID)
        
        Me.cmbYearQry = Null
    
    End If
    
'   If a year is selected, refine query
    If Nz(Me.cmbYearQry, 0) Then
    '   Only applicable if not Period selected
        If Not IsDate(Me.cmbDateQry) Then
        
            datID = Me.cmbYearQry
            
            strSQL = strSQL & " and Year(TransDate) = " _
            & Year(DateSerial(datID, 1, 1))
            
        End If
        
    End If
    
'   if a cost code is selected, refine query
    If Len(Nz(Me.cmbCostCode, "")) Then
    
        strCostID = Me.cmbCostCode
        
        strSQL = strSQL & " and CostCode = " & strQ & strCostID & strQ
    
    End If
        
    Me.sbfrmOrganizationCosts.Form.RecordSource = strSQL
    Me.sbfrmOrganizationCosts.Requery
        
End If

End Sub

The code was run from various places. For example the AfterUpdate from a combo box for the date (mmm-yyyy)...

Private Sub cmbDateQry_AfterUpdate()

ApplyFilter

End Sub

And scroll command buttons to scroll to the next or previous persion...

Private Sub cmdNextMonth_Click()

If IsDate(Me.cmbDateQry) Then
    Me.cmbDateQry = Format(DateAdd("m", 1, Me.cmbDateQry), "mmm-yyyy")
Else
    cmdToday_Click
End If

ApplyFilter

End Sub

Lastly, a method was developed to scroll through Cost Codes...

A central subroutine...

Private Sub ApplyCostCodeFilter(strSQL As String)

Dim rst As DAO.Recordset
Dim strQ As String, strID As String, strStore As String

strQ = Chr$(34)

If Len(Nz(Me.cmbCostCode, "")) Then
    Set rst = CurrentDb.OpenRecordset(strSQL)
    strID = Me.cmbCostCode
    
    With rst
        
        strStore = ""
        .MoveFirst
        
        Do While Not .EOF
            
            If strStore = strID Then
                Me.cmbCostCode = !CostCode
                'Found record, early exit
                .MoveLast
                .MoveNext
            Else
                strStore = !CostCode
                .MoveNext
            End If
        
        Loop
        
    End With
    
    rst.Close
    Set rst = Nothing
Else

    Set rst = CurrentDb.OpenRecordset(strSQL)
    strID = Me.cmbCostCode
    rst.MoveFirst
    Me.cmbCostCode = rst!CostCode

End If

ApplyFilter

End Sub

Scroll command buttons allowed scrolling to the next or previous record...

Private Sub cmdNextCostCode_Click()

Dim strSQL As String
strSQL = "SELECT * FROM tblCostCode Order By CostCode"

ApplyCostCodeFilter strSQL

End Sub

Richard