Help with form creation

[emma313823]

Hi All,



I have a few issues in the creation of a form. I'm a newbie with Access, so I appreciate your patience.
Before I get into my questions, let me explain what I've done up to this point.

I had one large worksheet which I broke into two separate worksheets because I thought it would be more efficient in table structure.

First here are my basic tables:

Principal table = a unique list of those account names who pay a commission check monthly

Territory table = five distinct territories - Metro, Mid, NE, PA, and UNY

The large worksheet included basic information of the receipt of each principal payment and after processing the disbursal to each territory, so I broke this into two tables:

Incoming Commission table

Includes pay date, principal name, check#, check value and comments

Disbursal table

Includes check#, territory and disbursed value to each territory (in general a line for each territory for each check)

The snapshot below shows the relationships I created.

And I know this is a Form question, but if anyone has time to give me your thoughts on the structure, I would really appreciate your feedback.



As to the form...

I just created a simple form to see what things looked like off of the Incoming Commission table. I think because I created a relationship of Check# the form displayed the upper section as the basic info for an incoming check and the bottom shows the Disbursal table detail associated to that check.

To be clear the form and what it displays is actually perfect for my needs of entering an incoming check and then to input the disbursal values to appropriate territories once the check is processed.

In the screenshot you see an existing payment with disbursals in the sub-table. As you can see there may be a territory that gets none of the commission payment, however I need to see all five territories for any check. This is a must as I report all territories to the owners, and they want to see all 5 regardless of nothing paid to a given territory.


My Questions:


1. Is there a way in the Disbursal sub-table on the form to have Territory show as a dropdown list, so I can just select rather than type it each time?

2. Is there is a way to add search options to the form that would allow me to more easily get to the check I need when I'm ready to input disbursals? For example, at any given time, I may be working on processing payments that are in two months - current and previous months, so there could be more than 50 checks to wade through.

I do know that I could be on the home ribbon and use the search feature, however if there is a way to encapsulate what the search options I'm looking for into the form, I'd rather do this.

For search options or fields, I'd like to add at minimum the following search ability:

a. Search on check number and display the resultant data for that check number?
b. Search on check value and display the resultant data for that check value?

[emma313823]

UPDATE...I figured out how to get the dropdown in the disbursal table to show in the form, so I'm all set on item #1

[Micron]

Tips:
- get out of the habit of spaces and special characters in names. It only adds work and sometimes, failure.
- ID is a poor name for a field, especially when you start seeing it in sql statements and reports/forms. ID of what?, you'll be saying. PrincipalID is better, PrincipalIDpk is even better. You will know it is the primary key of Principal table.
- your design doesn't look right, although it might be working now, with the emphasis on 'now'. ID fields are almost always primary key fields when they belong to the parent record. When they are in the related (child) records, they are virtually always the foreign keys of those records. So - CommisionIDpk, not CheckNum (notice I dropped the #) would be the pk and DispersalID would be the pk in that Disbursal table. CommissionIDfk would be the Disbursal foreign key in the Disbursal table. These comments are based on the premise that it is usually unwise to use real data as pk or fk fields.

If you haven't studied db normalization I highly recommend it. If you have, then no offense intended but you should reveiw some more. Perhaps it was not explained very well where you got your info from. I cannot stress how important it is to prevent hair pulling over issues caused by improper design.

Last but not least, many use a prefix so that no matter where you are reading, you know what the object is. tblCommissions is obiously a table, while qryCommissions is obviously a query. It can help a lot. Others have their own prefix ideas and perhaps others don't believe in it at all.

[emma313823]

This is so helpful...no offense taken at all. I'm in a huge learning curve.

[Micron]

Then I suggest you go here
https://www.accessforums.net/showthr...773#post521773

[mike60smart]

Hi
In your Parent main Form you would need to put a Combobox to allow you to search for the Specific CheckNr.

There is a Wizard that will step through the process.

[kd2017]

I have some thoughts:
1. here's something to expand on what micron said in his first response.


(I used _id to denote both primary key and foreign key fields. some prefer to use _pk and _fk. Pick a convention and stick to it!)

2. Create a search form based on a aggregate query of your checks and disbursals that will calculate total disbursed amount. Then you can click in a row and it will open the incoming commission form at the record you want to edit.







Of course you can expand on the search form to filter out based on the remainder, dates, principal, etc. With some tweaks to the underlying query you could filter by territories..

3. Question: Can an income commission only have ONE disbursement per territory? If so I would add unique index on the disbursement table across the income commission and territory foreign keys.

[emma313823]

Hi kd2017

I don't want to bomb you with responses all at once, so let me start with your feedback on the relationships. I removed all the relationships I had and made changes based on your recommendations to characters, spaces etc, as well as addressed the primary keys to the IDs. I am now trying to reestablish my relationships but am bumping into some issues, so not sure if I'm misunderstanding something.

In your example (i marked it up and included it below) helping to define primary key to foreign key, I tried to create the one to many relationship of the principal to the Incoming Commissions, but I get a dialogue box which I'm not sure how to address.

What I'm also stuck on in the relationship creation is that while the check number is unique, I can't create a separate table for it because new check numbers are being added all the time, so it is not a static list like Principal or Territory. The one common connection between the Incoming Commission table, Disbursal table and the Processing log is the Check Number. Would you have a suggestion on how to approach this?

Emma

[kd2017]

Let me start off by pointing out an error of my own. In my example I created the principal table with a field also named principal... don't do that lol. Strive to name everything something unique and descriptive.


Answer 1: Yes, the field [incoming_commission].[principal_id] is a foreign key that references the primary key field in the [principal] table.

Answer 2: When you're linking fields the datatype for both fields have to be the same. In your original iteration the [tblIncomingCommissions].[Principal] was probably a text field while the [tblPrincipal].[Principal_ID] is an autonumber/long integer number field. You will have to change the datatype in the incoming commissions table to what ever the principal tables id field is.

One of our goals in designing a database is to minimize or eliminate repeating data. If you have a principal named "ABC Widget Building Co., LLC." with and ID of 32, and let's say they have written you a 1,000 checks over the years, would it make more sense to store "ABC Widget Building Co., LLC." over and over and over again in the incoming commissions table or just the number 32? Which do you think would be faster for a computer to sort and find, a long complicated string in a list of long complicated strings or a simple number in a list of simple numbers?

Please check out micron's link from earlier, the information linked there should help explain these things. Even just google "relational databases 101" and spend some time with it.

[Micron]

Foreign key fields related to autonumber id's have to be Long integer type because autonumbers are longs. Are your id fields autonumbers (or any other type of long integer) and your related fields long integer as well?

[kd2017]

Here is the demo db I built.

emma.zip