Performing Calculations from Two Tables

[gnarpeggio]

Hello,

I'm fairly new to Access, so while creating a DB for my office, I came across a tricky little problem I need assistance with.

I'm creating a DB that tracks payments made to our office. In the main table (titled Main), I have the typical fields for each entry (FirstName, LastName, CompName Batch# etc.). This table also contains the payment types (CopyJob, Renewals, Fines and so on) for each check received. The second table (titled Refunds) contains fields indicating what date the refund was issued, the fiscal month and the same list of payment types that are in the Main table.

Now onto the tricky part (for me, at least). I'm trying to pull the payment entries from the Main table and then subtract the entries from the Refund table to create the difference. I need this calculation to appear properly on a report.

Any help with this would be appreciated greatly!

 

[HiTechCoach]

Hello,

I'm fairly new to Access, so while creating a DB for my office, I came across a tricky little problem I need assistance with.

I'm creating a DB that tracks payments made to our office. In the main table (titled Main), I have the typical fields for each entry (FirstName, LastName, CompName Batch# etc.). This table also contains the payment types (CopyJob, Renewals, Fines and so on) for each check received. The second table (titled Refunds) contains fields indicating what date the refund was issued, the fiscal month and the same list of payment types that are in the Main table.

Now onto the tricky part (for me, at least). I'm trying to pull the payment entries from the Main table and then subtract the entries from the Refund table to create the difference. I need this calculation to appear properly on a report.

Any help with this would be appreciated greatly!

Welcome to AWF!

It would really help to know more about the tables and how the relationships.

Would you please post the table structures. It may be easier to just post a sample database with only the tables. Be sure to remove any confidential data and only include sample data, we need enough sample data to see the issue.

 

[gnarpeggio]

I've attached an example of an entered record in both the Main and Refund forms. The Refund form is based on a query but the recordset property is set to Dynaset so both tables will be modified.

I hope this helps addressing my issue. Thanks again for your time

 

[HiTechCoach]

Thank you for posting the database. That helps a lot.

According to your design, there can only be one refund per main table record (the one-to-one relationship). Is that correct?

 

[gnarpeggio]

That's correct. We enter the checks received (our only form of payment) as one record at a time. We don't need a separate table specifically for parties/customers since they are sporadically received. Because of this, we'll only need to issue one refund per check (record) received when a refund is needed.

The "ROC#" field located at the top of the table is for the group or batch of checks we enter once a week. I sort the checks by this number and deliver them in a printed report to our accounting unit. My supervisor is trying to get the difference calculated onto a report in order to show higher management the numbers on paper.

 

[HiTechCoach]

That's correct. We enter the checks received (our only form of payment) as one record at a time. We don't need a separate table specifically for parties/customers since they are sporadically received. Because of this, we'll only need to issue one refund per check (record) received when a refund is needed.

The "ROC#" field located at the top of the table is for the group or batch of checks we enter once a week. I sort the checks by this number and deliver them in a printed report to our accounting unit. My supervisor is trying to get the difference calculated onto a report in order to show higher management the numbers on paper.

In a properly normalized table structure this would be very easy. You could use a SUM().

Since you design is like a spreadsheet and not a relational database ( has repeat fields => not properly normalized), you will have to do a lot more of work.

You will have to add and subtract all the fields to get a total like this:

= [Field1] + [Field2] + ... + [field10] - [field11] - [field12] - ... - [field20]


What if the payment types change? With your spreadsheet style design, it will require a lot of work to add or remove a payment type.

If it were me, I would fix the table structure now. It will make reporting a whole lot easier.

 

[gnarpeggio]

Do you have any suggestions for normalizing this DB? I've heard the term many times but am not too familiar with the process.

My forst guess for this would be to create an individual table for payments/refunds in general and to relate them to both the Main and Refund tables. This way, the calculations could be contained within one table, sparing me a lot of confusion.

Do you suggest changing the payment types to record entries instead of designated fields? Doing this would allow me to create a combo box for the payment type thus getting rid of all those repeat fields.

As you can see, I'm very new to Access. Any more suggestions are welcome! Thanks.

 

[HiTechCoach]

Do you have any suggestions for normalizing this DB? I've heard the term many times but am not too familiar with the process.

My forst guess for this would be to create an individual table for payments/refunds in general and to relate them to both the Main and Refund tables. This way, the calculations could be contained within one table, sparing me a lot of confusion.

Do you suggest changing the payment types to record entries instead of designated fields? Doing this would allow me to create a combo box for the payment type thus getting rid of all those repeat fields.

As you can see, I'm very new to Access. Any more suggestions are welcome! Thanks.

You are getting the basic idea.


To be able to giuve you any good advice on how to better structure your tables, we first need to have a better understanding of what you are doing.

You have a table named "main". "Main" what?

There are payments (is this "main"?) and refunds. Why are you getting payments? I assume there is some type of transaction/action that generates the need for a payment. Why so many refund?

Please, would you give a brief overview of the process.

 

[gnarpeggio]

Here's a quick breakdown of what I'm tasked with:

Our division receives payments for various reasons. Since our office only accepts payments by check, it is my duty to maintain a database that records the information for our accounting branch.

Every week or so, we'll enter these checks into the database I created under a specific batch number (ROC number). Each check will be entered as a record with the following information:

- Report of Collections or batch number (indicated as the ROC# field in the database)

- Check number located on the check (Chk# field)

- Date the check was received by our office (DateRecd field)

- Description of the check's purpose (Description field)

- First name of person, if applicable (FirstName field)

- Last name of person, if applicable (LastName field)

- Company name, if applicable (CompName field)

- Additional notes regarding this payment (Notes field)

- Payment type (currently 17 types of payments, 1 field per payment under the Currency data type)

Once a batch of checks are entered, I use a simple query to filter the records by the ROC# field and display it on a report. I then send it up to them for accuracy and completion.

It's not really a complicated database, it's pretty much a register of the checks we receive in our office.

In answer to your question, the table was originally titled "ROC" instead of "Main". It was to be used for the main entries for our office.

I hope this sheds a little more light of what I'm trying to do. The DB is self-contained, meaning it's only used by our office and not accessible to other users via a network or whatnot.

 

[HiTechCoach]

Here's a quick breakdown of what I'm tasked with:

Our division receives payments for various reasons. Since our office only accepts payments by check, it is my duty to maintain a database that records the information for our accounting branch.

Every week or so, we'll enter these checks into the database I created under a specific batch number (ROC number). Each check will be entered as a record with the following information:

- Report of Collections or batch number (indicated as the ROC# field in the database)

- Check number located on the check (Chk# field)

- Date the check was received by our office (DateRecd field)

- Description of the check's purpose (Description field)

- First name of person, if applicable (FirstName field)

- Last name of person, if applicable (LastName field)

- Company name, if applicable (CompName field)

- Additional notes regarding this payment (Notes field)

- Payment type (currently 17 types of payments, 1 field per payment under the Currency data type)

Once a batch of checks are entered, I use a simple query to filter the records by the ROC# field and display it on a report. I then send it up to them for accuracy and completion.

It's not really a complicated database, it's pretty much a register of the checks we receive in our office.

In answer to your question, the table was originally titled "ROC" instead of "Main". It was to be used for the main entries for our office.

I hope this sheds a little more light of what I'm trying to do. The DB is self-contained, meaning it's only used by our office and not accessible to other users via a network or whatnot.

That does shed a little more light on the process.

So you basically have a receipt that needs to be allocated to to 1 or more categories. (payment types)

so you would have:

- Receipts table
- Receipts Allocation Table (a record for each payment types)

The way I look at refunds is that the are the reverse of a receipt. So I would put them in the same tables. To show a refund I would put records in the Receipts Allocation Table with a negative amount for the payment type.

If you wanted the refund to be a totally separate transaction, in the Receipts table I would add a field for transaction type (check, refund, etc). Since here can only be one refund per receipt, add an additional field to like the refund to the corresponding check.

Hope this helps ...

 

[gnarpeggio]

I never thought of this structure! I appreciate your help in this matter greatly!!!

Thanks again,

Matt

 

[HiTechCoach]

I never thought of this structure! I appreciate your help in this matter greatly!!!

Thanks again,

Matt

Matt,

You're welcome.

we will be here is you need any further assistance.