Select date range from mulitple fields on a single record 1

[zach028]

Hey all....

I have a table with payment fields (pmt1-pmt12) and date fields (date1-date12) for several thousand accounts at a collection agency. I want to make a query that selects only payments made within a given date range. A payment can be in any field.
My problem arises with the fact that an account can have multiple payments made within the same month and subsequently can have several, one, or no relevant fields in each record. I have tried making twelve queries and joining the results, but I end up with duplicate results. A single query gives all payments for each account that had a payment made within that date range.
Any suggestions would be most helpful...

 

[RickSpr]

Well, Mistah Repo Man, ;-)

This is the classic problem with putting multiple copies of a data item in a relational table. You've broken a cardinal rule of relational theory, and now you must suffer the consequences!!!!!

Just kidding--about the suffering, anyway. The proper way to create this database would have been to have one table for basic (non-repeating) account info, and another table that carries the account number, a date, and payment info. Then you wouldn't have your current problem, and you wouldn't be limited to 12 payments, and various other problems wouldn't come up some day because of the table design.

But since it's designed this way, let's try to deal with it as it is. You said you "tried making twelve queries and joining the results" but you end up with duplicates. Well, don't you mean you used a union query to combine the results? "Joining" in relational database terminology is a different kind of operation, and wouldn't give you what I think you wanted.

Anyway, if you used a union query and that gave you duplicates, a quickie fix to eliminate the duplicates is to create a plain select query that just selects * from the union query, but set the Unique Values query property to Yes. (You can't do that directly in the union query.)

Of course, your 12 queries running may be rather time consuming, but the only way around that is to either redesign your tables, or write VBA code to process the data sequentially. You can't cheat the Relational Database Gods! ;-) Rick Sprague

 

[zach028]

Rick-

What you suggested first, creating a table to hold the account number, payment, and date, couls work since we haven't begun collection on these accounts.
I do have question about this (of course). The payment fileds were created so I could track payments made per specific account, and keep a running total which is calculated automatically. I assume the table you recommend would have no primary key, so how could I keep track of the current balance?
Sorry if this question is silly, but if you keep being nice, maybe I'll let you keep your car ;-)

As far as the union query, what I tried to do was create one select query from my original twelve. Probably the total wrong way to go about it, but hey! I never said I was an expert! X-)

 

[RickSpr]

Assuming the original amount to be collected is in the account table, you can use a query to get the current balance calculated any time (rather than storing it and having to update it when a payment is made).

Assume an Accounts table with Account and Amount (original amount to be collected) fields. You could set Account as the primary key, by the way. Assume also a Payments table with Account, Date, and Payment fields.

You build the query by adding the Accounts table and the Payments table. Then you drag a line (with the mouse) from Account in the Accounts table to Account in the Payments table. (Depending on how you set up Access and the tables, Access may already have drawn this line for you.)

Next, go to the menu and choose View>Totals. This reveals the Total: line in the query grid. Drag Account and Amount from the Accounts table to the grid. In a third column of the grid, key the following on the Field line:

    Balance: Accounts.Amount - Sum(Payments.Payment)

On the Total line of that column, choose Expression from the dropdown list. Save and switch to Datasheet View. You'll have a list of accounts, their original collection amounts, and the balance due.

The line between the tables is the secret. It tells Access that you want it to keep records from both tables with the same account number grouped together. In other words, they're "related" by account number. That's why it's called a relational database. (No, your question wasn't silly. Can I keep my car now? )

One more thing: Once you have these tables, and make Account the primary key in the Accounts table, click on the main database window and then choose Tools>Relationships from the menu. This opens the Relationships window. If you don't get the Show Tables dialog box as well, choose Relationships>Show Tables from the menu. Add these two tables to the Relationships window, then close the Show Tables dialog. Now, drag a line from Account in the Accounts table to Account in the Payments table. Access will open an Edit Relationships dialog. Click on Enforce Referential Integrity, then click the two Cascade check boxes. Finally, click Create, and close the Relationships window.

What this will do is, if you change an account number in the Accounts table, Access will automatically update it in the Payments table (Cascade Updates). And if you delete an account from the Accounts table, Access will automatically delete all the payments for that account from the Payments table (Cascade Deletes). Last and most important, Access won't let you add a payment to the Payments table unless the account number there matches one in the Accounts table (Enforce Referential Integrity). Together, these three options help you keep from having inaccurate or obsolete account numbers in the Payments table. Just remember, though, that if you delete an account and add it back, you've lost all the payment info! Rick Sprague

 

[zach028]

OK, we're cooking with fire now....

I have my table set up and a form with subform set up to view and enter payments. I am still encountering problems with calculating a current balance though.
I have the select query you suggested built. Trying to do an update query from that one to field "curbal" on the main table does not work. It tells me the query is not updatable.
I also tried to do a Set Value macro using Ccur, but that also does not work since the original balance and the payment amounts are in separate tables.
Any suggestions?

 

[RickSpr]

Ah! I didn't realize you wanted the current balance to show on a form. I was thinking you just wanted a listing of accounts and balances, and the query was fine for that.

Here's what you need to do. Open the subform in Design View. Add a Form Header/Footer to it. Place a text box in the footer. Call it TotalPayments. Set its Control Source to the expression: =Sum(Payment).

(Don't worry about the form header and footer--they won't be displayed within the subform control, only if you open the subform itself from the database window. We're just using the footer as a place to put the calculated total.)

Save and close the subform. Now open the main form. In your CurBal control, set the Control Source to the following expression (I'm assuming the subform is named PmtsSubform): = Amount - [PmtsSubform].Form![TotalPayments]

If you prefer, you can show the total payments on the main form, too. Just drop in another text box and set its Control Source to: = [PmtsSubform].Form![TotalPayments]

By the way, this technique is demonstrated in the Northwind sample database, on the Orders form and subform. You might want to examine that if you get stuck.

This is the general way to get data out of a subform into a main form: [subform control name].Form![control name on subform]. But it only works as expected for summary data such as totals. If you try to use it for a field in the subform's record source, you'll only get the value for the last record.

Let me know if you get it working, ok? Rick Sprague

 

[zach028]

Rick-
I must say you've been most helpful. Everything is running smoothly (for now). I appreciate your time...

Zach