Guidance for some challenging (for me at least) data entry

[HomerJ]

Greetings and thank you for taking a moment to look at my problem in hopes of offering assistance.

My challenge is this. In my position, I collect receipts and cash/checks that are taken in for a variety of permits and fees. What makes this tricky for me are a number of questions I need to answer so I can take the correct approach. I currently do this in Excel with some extensive code that I was able to Frankenstein together with a variety of case statements and input boxes. If it would be helpful to see an example of my Excel sheet and code, please let me know.

First, the fees collected for any given permit/fee go into from 1-3 different account codes. If all the fees were fixed numbers, I could do this with a query and unique codes. However some of the fees are variable. For example, a building permit fee is based on a number of factors that are calculated by the folks who actually take in the money. Currently I just enter the variable amount from the receipt into an input box. Fortunately, none of the fees that are split out to more than one code require any input of the dollar amount.

There is one other thing that I have to take into account on a couple of the codes that do get split out. There are a couple of permits that have set base fees, plus a per lot fee that adds a set amount per lot to 2 of the 3 account codes in the split.

So as you can see I have a bit to try to get my brain around. Some fees I have to enter manually, while others have a per lot fee. The rest are fixed amounts that would be reasonably easy to work with on their own. I've learned a great deal about Access lately, but am still a mere neophyte trying to stretch my limits.

Any guidance to point me in the right direction is greatly appreciated.

If more info is needed or if I have been as clear as mud, please let me know.

Thanks in advance.

 

[Andrew Fergus]

Hi HomerJ
What have you done so far?
Do your data structures include all of the relevant pieces of data that you need to collect to perform the calculations?
Have they been set up to take account of the fee structures you described?
Have you considered setting up a form similar to that you currently use in Excel?
If you are able to verbalise or draw the problem, then the solution should present itself.
Pretty vague I know but HTH
Andrew

 

[HomerJ]

Thanks for the reply Andrew. I'll post some of what I have in Excel when I return to work on Monday in the hopes that it will give a clearer picture of what I'm trying to do. I want to get the benefits that Access has to offer without increasing the data entry tasks.

Thanks again for inquiring. Have a great weekend!

 

[HomerJ]

Hi Andrew.

Sorry I'm a little late getting around to this. Here is my data entry sheet that I use. All I have to enter is the Permit #, whether it is Cash or Check, and the permit code. I have VBA code that populates everything else. I'll post the code below. I removed a couple of columns from this sheet for space consideration, but I think the code will essentially make sense.

MrExcelHelp.xls
	A	B	C	D	E	F	G	H
1	DepositDate	Receipt#	Permit#	Check/Cash	Code	DeptFee	Lots	BARS
2	02/14/05	2413	BP41-1243	Check	BPS	$1,340.00		001-053-32210
3	02/14/05	2413	BP41-1243	Check	State	$4.50		602-000-38900-559
4	02/14/05	2414	BP52-0416	Check	BPS	$240.00		001-053-32210
5	02/14/05	2414	BP52-0416	Check	State	$4.50	2	602-000-38900-559
6	02/14/05	2415	SHP99-317	Check	SHPFINAL	$300.00		101-000-32290
7	02/14/05	2415	SHP99-317	Check	SHPFINAL	$140.00	2	001-053-32290
8	02/14/05	2416	PLP41-320	Check	PLPFINAL	$915.00	21	101-000-32290
9	02/14/05	2416	PLP41-320	Check	PLPFINAL	$920.00	21	001-053-32290
10	02/14/05	2417	SHP00-177	Check	SHPFINAL	$300.00		101-000-32290
11	02/14/05	2417	SHP00-177	Check	SHPFINAL	$160.00	3	001-053-32290
12	02/14/05	2418	BP52-0133	Check	BP	$49.50		001-053-32210
13	02/14/05	2419	BP41-1491	Check	BPS	$280.00		001-053-32210
14	02/14/05	2419	BP41-1491	Check	State	$4.50		602-000-38900-559
15	02/14/05	2420	BLA52-524	Check	BLA	$57.00		101-000-32290
16	02/14/05	2420	BLA52-524	Check	BLA	$150.00		001-053-32290
17	02/14/05	2420	BLA52-524	Check	BLA	$51.00		104-000-34650-LAN
18	02/14/05	2421	BP41-1485	Check	BPS	$974.00		001-053-32210
19	02/14/05	2421	BP41-1485	Check	State	$4.50		602-000-38900-559
20	02/14/05	2422	CGP52-525	Check	CGP	$240.00		101-000-32290
21	02/14/05	2422	CGP52-525	Check	CGP	$250.00		001-053-32290
22	02/14/05	2423	BP41-1229	Check	BPS	$367.00		001-053-32210
23	02/14/05	2423	BP41-1229	Check	State	$4.50		602-000-38900-559
24	02/15/05	2424	BP52-0152	Check	BPS	$295.50		001-053-32210
25	02/15/05	2424	BP52-0152	Check	State	$4.50		602-000-38900-559
26	02/15/05	2425	BP52-0153	Check	BP	$1,043.00		001-053-32210
Data

 

[HomerJ]

Here is the fee schedule that is referenced by the code. Once again, I have removed a couple of columns for the sake of space.

MrExcelHelp.xls
	A	B	C	D	E	F
1	Lookup	DeptFee	LotCharge	BARS	PermitFee	Split
2	BLA	57.00		101-000-32290	$258.00	3
3	BLA	150.00		001-053-32290	$258.00	3
4	BLA	51.00		104-000-34650-LAN	$258.00	3
5	BP			001-053-32210		1
6	BPS			001-053-32210		1
7	CGP	240.00		101-000-32290	$490.00	2
8	CGP	250.00		001-053-32290	$490.00	2
9	COPIES			001-053-34169		1
10	GEO	150.00		101-000-32290	$250.00	2
11	GEO	100.00		001-053-34589	$250.00	2
12	MAPS			001-053-34150		1
13	PLANCHK			001-053-34583		1
14	PLP3	250.00		101-000-32290	$2,943.00	3
15	PLP3	2,160.00	50.00	001-053-32290	$2,943.00	3
16	PLP3	533.00	29.00	104-000-34650-LAN	$2,943.00	3
17	PLPFinal	600.00	15.00	101-000-32290	$1,100.00	2
18	PLPFinal	500.00	20.00	001-053-32290	$1,100.00	2
19	SEPA	40.00		101-000-32290	$290.00	2
20	SEPA	250.00		001-053-32290	$290.00	2
21	SHP2	88.00		101-000-32290	$1,071.00	3
22	SHP2	500.00	50.00	001-053-32290	$1,071.00	3
23	SHP2	483.00	29.00	104-000-34650-LAN	$1,071.00	3
24	SHPFinal	300.00		101-000-32290	$400.00	2
25	SHPFinal	100.00	20.00	001-053-32290	$400.00	2
26	SHPREV	150.00	50.00	001-053-32290	$150.00	1
27	STATE	4.50		602-000-38900-559		1
Fees

 

[HomerJ]

Lastly, here is the code that makes it all work. This is essentially what I hope to figure out how to do in Access.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Amt As Double
Dim Lots As Integer
Dim Msg As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Target.Column = 5 And Target.Count = 1 Then
With Sheets("Fees").Range("A:A")
Set c = .Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

If c.Offset(0, 3) = 0 Then
    Amt = InputBox("How much?")
End If
If c.Offset(0, 4) > 0 Or c.Offset(1, 4) > 0 And c.Offset(0, 8) > 1 Or c.Offset(2, 4) > 0 And c.Offset(0, 8) > 2 Then
    Lots = InputBox("How many lots?")
End If

Select Case c.Offset(0, 8).Value

Case Is = 1

Target.Offset(0, -4).Value = Date

Target.Offset(0, -3).Value = Target.Offset(-1, -3) + 1
With Target
    .Offset(0, 1).Value = c.Offset(0, 1).Value
    .Offset(0, 2).Value = c.Offset(0, 2).Value
    .Offset(0, 3).Value = c.Offset(0, 3).Value + (c.Offset(0, 4) * Lots)
End With
    If c.Offset(0, 3) = 0 Then
        Target.Offset(0, 3).Value = Amt
    End If
    If c.Offset(0, 4) > 0 Then
        Target.Offset(0, 4).Value = Lots
    End If
With Target
    .Offset(0, 5).Value = c.Offset(0, 5).Value
    .Offset(0, 6).Value = c.Offset(0, 6).Value
    .Offset(1, -2).Select
End With

If Target = "BPS" Or Target = "Bps" Or Target = "bps" Then
'Msg = MsgBox("Is there a State fee on this receipt?", vbYesNo + vbQuestion, "State Fee")
    'If Msg = 7 Then GoTo TheEnd
    'If Msg = 6 Then
        With Target
            .Offset(1, 0).Value = "State"
            .Offset(1, -3).Range("a1:c1").Select
            Selection.FillDown
            .Offset(2, -2).Select
        End With
    'End If

TheEnd:
End If

Case Is = 2

With Target
    .Offset(0, -4).Value = Date
    .Offset(0, -3).Value = Target.Offset(-1, -3) + 1
    .Offset(0, 1).Value = c.Offset(0, 1).Value
    .Offset(0, 2).Value = c.Offset(0, 2).Value
    .Offset(0, 3).Value = c.Offset(0, 3).Value + (c.Offset(0, 4) * Lots)
End With
    If c.Offset(0, 3) = 0 Then
        Target.Offset(0, 3).Value = Amt
    End If
    If c.Offset(0, 4) > 0 Then
        Target.Offset(0, 4).Value = Lots
    End If
With Target
    .Offset(0, 5).Value = c.Offset(0, 5).Value
    .Offset(0, 6).Value = c.Offset(0, 6).Value
End With

Target.Offset(1, -4).Range("a1:e1").Select
Selection.FillDown
With Target
    .Offset(1, 1).Value = c.Offset(1, 1).Value
    .Offset(1, 2).Value = c.Offset(1, 2).Value
    .Offset(1, 3).Value = c.Offset(1, 3).Value + (c.Offset(1, 4) * Lots)
End With
    If c.Offset(1, 4) > 0 Then
        Target.Offset(1, 4).Value = Lots
    End If
With Target
    .Offset(1, 5).Value = c.Offset(1, 5).Value
    .Offset(1, 6).Value = c.Offset(1, 6).Value
    .Offset(2, -2).Select
End With

Case Is = 3

With Target
    .Offset(0, -4).Value = Date
    .Offset(0, -3).Value = Target.Offset(-1, -3) + 1
    .Offset(0, 1).Value = c.Offset(0, 1).Value
    .Offset(0, 2).Value = c.Offset(0, 2).Value
    .Offset(0, 3).Value = c.Offset(0, 3).Value + (c.Offset(0, 4) * Lots)
End With
    If c.Offset(0, 3) = 0 Then
        Target.Offset(0, 3).Value = Amt
    End If
    If c.Offset(0, 4) > 0 Then
        Target.Offset(0, 4).Value = Lots
    End If
With Target
    .Offset(0, 5).Value = c.Offset(0, 5).Value
    .Offset(0, 6).Value = c.Offset(0, 6).Value
End With

Target.Offset(1, -4).Range("a1:e1").Select
Selection.FillDown
With Target
    .Offset(1, 1).Value = c.Offset(1, 1).Value
    .Offset(1, 2).Value = c.Offset(1, 2).Value
    .Offset(1, 3).Value = c.Offset(1, 3).Value + (c.Offset(1, 4) * Lots)
End With
    If c.Offset(1, 4) > 0 Then
        Target.Offset(1, 4).Value = Lots
    End If
With Target
    .Offset(1, 5).Value = c.Offset(1, 5).Value
    .Offset(1, 6).Value = c.Offset(1, 6).Value
    .Offset(2, -2).Select
End With

Target.Offset(2, -4).Range("a1:e1").Select
Selection.FillDown
With Target
    .Offset(2, 1).Value = c.Offset(2, 1).Value
    .Offset(2, 2).Value = c.Offset(2, 2).Value
    .Offset(2, 3).Value = c.Offset(2, 3).Value + (c.Offset(2, 4) * Lots)
End With
    If c.Offset(2, 4) > 0 Then
        Target.Offset(2, 4).Value = Lots
    End If
With Target
    .Offset(2, 5).Value = c.Offset(2, 5).Value
    .Offset(2, 6).Value = c.Offset(2, 6).Value
    .Offset(3, -2).Select
End With
End Select

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End If
End With
End If

End Sub

I realize this may not make things any clearer, but I hope it does.

When I enter a BP, I get an input box asking for the amount. After I type that in and click OK, it gets posted into the DeptFee field. If I enter BPS, I get the same prompt, but after it posts the amount I enter, it automatically adds a second row with the STATE fee which always goes with a BPS. If I enter CGP, I do not get any prompts, it simply enters 2 rows with the appropriate preestablished split of $240.00 for one BARS and $250.00 for the other. If I enter a PLPFinal, I get an input box that requests the number of lots. After entering the number of lots, the code adds 3 rows to the data page with a preestablished split of a base fee, plus a per lot fee that is multiplied by the number of lots entered into the input box.

I realize it's a crazy setup, and that is why I seek help.

Thanks.

 

[Andrew Fergus]

Hmmmm, yes well ...... this might have to be a collaborative effort. I have some thoughts on how this should / could be set up by am having lots of difficulty getting some of the detailed receipt data into a form (which is the whole point). Maybe this is where some of the other more learned contributors can help.

My thoughts on the appropriate data structures follows :

Table_Fee_Header
FH_Code (unique key e.g. BLA or GEO etc)
FH_Description (e.g. generic fee descriptor)

Table_Fee_Components
FC_Code (unique key e.g. BLA1, BLA2, BLA3, State, Geo1, Geo2 etc)
FC_Description (generic fee component descriptor)
FC_BARS
FC_Department_Fee (fixed $ fee for this fee component, e.g. $57 for BLA1 etc)
FC_Lot_Fee ($ fee per lot, default = zero, e.g. $15 for PLPFinal1 and $20 for PLPFinal2 etc)

Table_Fee_Structures
FH_Lookup_Code (linked to FH_Code)
FC_Lookup_Code (linked to FC_Code)

The purpose of this table is to set up the many-to-many relationship between the fee headers and fee components (e.g. there may be more than one fee that has the 'state' component, this structure will allow that wihtout duplicating the 'state' component). Once the fee headers and fee components are created, it is a matter of using this table to create / modify the fee schedule by joining each table to the other through this intermediary table (much like your sample fee schedule spreadsheet).

Table_Receipts
Receipt_ID (unique id)
Receipt_Date (default = Date())
Client_Lookup (link to a client field, or if you don't plan on using a client table, then substitute this field for some of the permit holders details)
Payment_Method (Cash/Check, possibly link to another table? OTT IMO)
Permit_Number (unique? or does this link to another table? Are the payments annual or one time? If one time, then store here, if regular then create a permit table and link on this field)
Fee_Lookup (link to FH_Code in Table_Fee_Headers - see below, I think this is the part that is causing trouble on the form)
Lots (number of lots, derfault = 0, used for permits where there is a 'per lot' fee)
Manual_Fee (used to store the manual fee, i.e the not fixed fee, that is entered for the likes of BP & BPS etc)
Total_Fee (probably unnecessary but I decided to include it anyway, this figure is derived as a sum of the manual fee + department fees + lots x lot fees)

Using this data structure, the intention was to create a form/subform whereby the main part of the form had the receipt details and the subform part of the form contained the fee components. I can get the 2 to link etc. but cannot get the subform totals onto the main form (to include in the total fee). I suspect the issue is linking the Fee_Lookup field in the receipts table to the FH_Code field in the Fee_Header table. Or I have possibly over-complicated the data structure and that is getting in the way. I am probably missing something really basic but the solution is eluding me for the moment.

Apologies for only half an answer but this might get you started.

If some of the other MrExcel contributors would like to contribute, then please feel free to amend / criticise my suggestion so that we can resolve this issue for HomerJ.

Andrew.

 

[HomerJ]

Hi Andrew.

Thank you for all the thought that you have obviously put into this. You've helped clarify things greatly for me. The integration of the manual fee was one of the highest hurdles that I was looking at in this. But your description of the base structure certainly seems like a good launch point.

I will have to work on setting this up and see what challenges or questions I encounter in the process. I don't know if it makes a difference or not, but the State fee of $4.50 only goes with BPS. I assigned it it's own ID because it goes to a specific BARS code. Although I suppose it would even be possible to implement a yes/no checkbox for the state fee and do away with the BPS code? BPS is the same as BP except for the state fee.

Thanks again for all your assistance. I'll post back as I run into or overcome any challenges.

Homer

 

[Andrew Fergus]

Hi Homer
If there is a one-to-many relationship between the types of fees and the fee 'components' (per your 'state' fee example) then the many-to-many structure is not required. This means you can do away with the Fee_Structure Table, but add a FH_Header_Link field into the Fee_Component table (set up as a one-to-many relationship between Fee_Header and Fee_Component). Also, I omitted to include a BARS field for the manual fee - you could add this to Fee_Header table as the default BARS for the manual fee.
HTH, Andrew

 

[HomerJ]

Hi Andrew.

I just wanted to let you know that your help in getting my brain pointed in the right direction made it possible for me to overcome my challenges with this project. The real key was the manual fee field. I've set up a query that allows me to output both summary and detail information that looks exactly the same as the old Excel version with all the benefits that come with Access, so those who receive the reports will not even notice a difference.

Now I'm just working on the finishing touches and fine tuning the entry form and the final reports and automating a few things so that when someone fills in for me while I'm on vacation they will have an easy time of it.

So I just wanted give you a hearty Cheers and Thanks!!!