Help with design

[Ray67]

Hello

I have a table with payments collected every month for different providers. I want my user to be able to reconcile all payments every month by provider. The following is what i need help with. First the user would look at the first provider and compare all payments. If one payment is missing than the user would check off a box. After the user closed the list of payment than a new window would open so the user can enter more data for any missing payments. How can i go about this?????



Thank you

[jzwp11]

Do you only have the 1 table in your database? If not, what other tables do you have and how are they related? In order to determine if any payments are missing, you need to know what payments are expected and which ones have been received. How are you doing that now within your table structure?

[Ray67]

Thanks for your response. Yes! I only have one table.
I have a report that tells me all the payments by months off of that table. I get a monthly settlement report and I compare them. Thanks!

[jzwp11]

First, you need another table. Since a provider can have many associated payments, that describes a one-to-many relationship. The structure would look something like this:

tblProviders
-pkProviderID primary key, autonumber
-txtProviderName


tblProviderPayments
-pkProvPayID primary key, autonumber
-fkProviderID foreign key to tblProviders
-other payment fields

If one payment is missing than the user would check off a box. After the user closed the list of payment than a new window would open so the user can enter more data for any missing payments.

The information about any missing payments has to go somewhere, so we will need another table or field within the payment table, but it has to relate somehow to the missing payment, but if the missing payment is not a record in the payment table that is a problem. So it sounds like we have to identify the missing payment, add it to the payment table with the appropriate flag saying that it is missing. Now what kind of additional information do you plan on adding once a missing payment is found?

Regarding the monthly settlement report, in what format is it? in Excel perhaps?

[Ray67]

The additional information would be comments, reason of error and status. The monthly settlement report is in excel format.Would it be a good idea to download the monthly settlement report to access and compare it with the other table?

[jzwp11]

Uploading the Excel data into a new table in Access would allow you to do a comparison via a query. What content is in the settlement report? Does it contain the payments that should have been received or actually received? I'm still a little unclear as to what is in your payment table--actual payments received?

The additional information would be comments, reason of error and status.

If you just have one comment, one reason and one status for each payment record, then you would just add the 3 indicated fields to the payment table.

[Ray67]

Sorry i'm new to this. So each provider gets a monthly fee settlement report which tells them all of the payment they will be receiving. The payment that we enter into access is based on payments we posted on our own systems. Example.

Access				Fee settlement Report
Receipt #	Amount			Receipt #	Amount
20500	$500.00			20500	$ 500.00
20530	$100.00			20530	$ 50.00
20580	$800.00			20580	$ 800.00
LockBox	$500.00			LockBox	$ 500.00
Grand Total	$1,900.00			Grand Total	$1,850.00

[jzwp11]

So it looks like you have to match the type (receipt or lock box) and the associated number (receipt number) if present and then compare the respective values. In your example, receipt #20530 shows an actual payment of 100 compared to an anticipated 50. So based on this difference, you have to record some information related to receipt #20530. Can you also have a receipt that shows up in Access but does not match anything in the settlement report? And vice versa?

[Ray67]

yes that happens too

[jzwp11]

So we have 3 possible conditions

1. Matching records exist in both sources (Access & settlement report)
2. Record exists in Access but not in settlement report
3. Record exists in settlement report but not in Access

1 can be accomplished with a simple SELECT query that finds matching records with different amounts. A form can be based on that select query, so your users can add the additional information.

Now regarding #2, Access can identify those records via a query and can open a form for your users to add any additional information. I assume that these records represent payments you have received, so do you want to do anything else with these records?

Regarding #3, If you do not have a match in your Access table, I assume that you want to bring them into your table and somehow flag them in addition to adding comments. Can the status field you mentioned be used for flagging purposes?

[Ray67]

The purpose of the fee settlement reports is that it tells me how much money the provider would get for that month. So Receipt #20530 on the fee settlement its off by $50.00. I would want to report on it and so next month the provider would get the credit.

1. That sounds good.
2. These are payments i received but since they weren't on the fee settlement the provider didn't get the payment. So i would have to report on it to notify them that they would have to credit the payment next month fee settlement.
3. Yes status field can be used for flagging purposes

[jzwp11]

Now you just have to get your payment and provider tables set up as I suggested. What are the field names in the payment table? What are all of the possible status values for a payment? I assume that a payment can have only 1 status at a particular point in time.

[Ray67]

Yes one status. So i was looking at my table and i have two tables. These tables are for my user to enter the data. Would this change everything. I was also thinking that downloading fee settlement into access would be too much work because its about 30 provider that i would have to download.

Table 1		Table 2
ID		ID
User		ID Main Table
Date of Posting From		Date of RCPT
Date of Posting To		RCPT #
Today's Date		Cash
Systems Posted on		Check
Provider Name		Credit Card
		Source of Payment
		Medical Assoc Payment
		Comments

[jzwp11]

Is there a relationship between table 1 and table 2? What are the datatypes of the fields?

I was also thinking that downloading fee settlement into access would be too much work because its about 30 provider that i would have to download.

But then the comparison process is more manual and prone to errors.

[Ray67]

The relationship between table 1 and table 2 is Id number. So ID Main table in table 2 is linked to Id on table 1.

Table 1			Table 2
Field Name	Date Type		Field Name	Date Type
ID	AutoNumber		ID	AutoNumber
User	Text (Combo box)		ID Main Table	Number
Date of Posting From	Date/Time		Date of RCPT	Date/Time
Date of Posting To	Date/Time		RCPT #	Number
Today's Date	Date/Time		Cash	Number
Systems Posted on	Text (Combo box)		Check	Number
Provider Name	Number(ComboBox)		Credit Card	Number
			Source of Payment	Number (ComboBox)
			Medical Assoc Payment	Number (ComboBox)
			Comments	Text