Applying payments to invoices to report outstanding debts

[JustAC]

Let me preface this with I have used Access (10 years ago) but really am learning again from scratch...

I have a database with 4 tables, only 2 of which I need to work with.

InvoiceTable
ClientName (Select from ClientTable)
InvoiceType (Select from LookUpTable)
InvoiceNumber (Primary Key - Number)
InvoiceDate (Date)
InvoiceAmount (Currency)
InvoiceComments (Long Text)

PaymentsTable
ClientName (Select from ClientTable)
InvoiceNumber (Select from InvoiceTable)
TransactionID (Primary Key - Number)
TransactionDate (Date)
TransactionAmou nt (Currency)

So I am looking to run a query that will sum(PaymentsTab le.TansactionAm ount) for all identical PaymentsTable.I nvoiceNumber's and then subtract that total from InvoiceTable.In voiceNumber that matches the sum of its Payments.

I hope that makes sense.

[nico5038]

I would create two Group By queries to SUM() the Amount fields for ClientName and InvoiceNumber with a "dummy" for the "missing amount" these two queries can then be grouped again in a UNION having a MAX() for the InvoiceAmount and the TransactionAmou nt.

This results in one row per client/Invoice with Invoice and Transaction Amount that can be subtracted.

Getting the idea ?

Nic;o)

[JustAC]

I would create two Group By queries to SUM() the Amount fields for ClientName and InvoiceNumber with a "dummy" for the "missing amount" these two queries can then be grouped again in a UNION having a MAX() for the InvoiceAmount and the TransactionAmou nt.

This results in one row per client/Invoice with Invoice and Transaction Amount that can be subtracted.

Getting the idea ?

Nic;o)

Thank you, that actually helped quite a bit... I am just struggling on combining the totals of all the invoices and all of the payments to give a representation of the clients account balance.

[nico5038]

The clients account balance is sum of invoices minus sum of payments. As these will be in separate tables, you'll need to combine them with a UNION, by making one field negative to let the sum get the outstanding amount or using a dummy column for "the opposite value" to get two fields you can subtract like:

select custid, invoice, 0 as payment from tblInvoice
union
select custid, 0 as invoice, payment from tblPayments

Finally a group by query using the above query will give the final result.

Nic;o)

[zmbd]

When you say:

(Select from ClientTable)
(Select from LookUpTable)

do you mean that you are using the "lookup field" property of the field in the table
MS Access 2010+ Create a lookup field

This is an important bit of information as the value shown in the field may not be what you need when creating the query.

IMHO - at the table level, lookup fields should be avoided unless one is creating a SharePoint web application and then only because SharePoint does NOT support the normal table relationships. (This is a position held by many of the Experts here at Bytes and other Access forums).

[JustAC]

It means that the data is being pulled from that table to manage integrity and maintain that the fields are identical to maintain their relationship.

Again, I am freshly back to access after several years of non-usage.It was always my understanding to link tables via these common fields to ensure integrity, am I incorrect?

So yes it is a lookup field.

[nico5038]

Did yu try the UNION solution?

Nic;o)

[zmbd]

JustAC it appears as though there has been a change in nomenclature since your last soiree with Access

Table level lookup fields which appear to be combo-boxes in the table, see here: The Evils of Lookup Fields in Tables (read more), are much different than what I think you are referring to which are the related fields between tables that one would create during normalization (read more) -- these are not "look-up" fields
This is why I provided the link in my prior post.

[JustAC]

Nico - I tried but was getting an error... I then had to leave for a business trip that took me out of town for a week, so I am going to look again this week and see if I can rectify it.

ZMBD - I have relationships built... I am sorry if I am a little confused...
The field ClientName is a Lookup Field in the Invoice table and the Payments Table. When looking at the relationships I see them all correctly associated.
In the table Payments table, I have the InvoiceNumber field as a lookup from the Invoice table. This too has a relationship build for the fields.
I then have the field InvoiceType pulling the values from LookUpTable, and again an association is built.

How should I have differed in my approach to building it?

[zmbd]

Ok, lets take a little look here

The field ClientName is a Lookup Field in the Invoice table and the Payments Table

When you open the table directly, is there a drop down list (ie combobox) in the table?

If so this could be why you are getting an error using Nico's method.

[JustAC]

yes it is a drop down

[zmbd]

sigh... then you have the lookup fields that most of us avoid unless creating a SharePoint application. See the link to the evils in my last post.

The normal way to establish the table relationships is thru the {Database Tools}/{Relationships}
Create table relationships (Access basics, part 2) (Video)

I personally avoid the cascade delete and update options:
+ Cascade Delete: IMHO, can really create havoc in your data if a user accidentally deletes a parent record (say a customer) and you need to retain the child records (inventory sold). There are pros and cons to this option and its usefulness will depend on your business model
+ Cascade Update: because I use autonumber fields for my primary keys (I don't believe that primary keys should have any meaning beyond identifying the record and should be numeric) there is no way to change the value; thus, cascade update is useless. However, many people subscribe to the concept that primary keys can have some meaning and cascade update could be helpful in such designs.


You can modify Nico's UNION to work with the actual data behind the lookup field; however, IMHO, you would be better off redesigning your database without these fields in the table. Instead move them to the query or to the form level for the human friendly data input.

Check your Bytes.com Inbox I'll forward you a copy of some references and resources. :)

[JustAC]

now I am just lost...

[zmbd]

Certainly not my intention to have confused you.

++ Reading thru your original post a few times, what I think you need is to create two queries (these are from the attached database):
The first one will sum the values such as:
[q_sum_payments]

SELECT t_Payments.FK_invoice
   , Sum(t_Payments.Payment_Amount) 
      AS SumOfPayment_Amount
FROM t_Payments
GROUP BY t_Payments.FK_invoice;

The second one will take [q_sum_payments], join against [t_payments] on the related field and then subtract the summed payments from the invoiced amount
(now this will look complex here be
[q_BalanceForwar d_simple]

SELECT t_Invoice.Invoice_number
   , t_Invoice.Invoice_Amount AS Billed
   , q_sum_payments.SumOfPayment_Amount
       AS Paid
   , [t_Invoice].[Invoice_Amount]-[q_sum_payments].[SumOfPayment_Amount]
       AS BF
FROM t_Invoice 
   INNER JOIN q_sum_payments 
   ON t_Invoice.pk_invoice = q_sum_payments.FK_invoice
ORDER BY t_Invoice.Invoice_number;

How I read your post, I don't think you need a union query.

++ To clear things up on "lookup" fields, table relationships, and to show you how the above queries work I've bodged together a simple database and attached it to this post.

Tables:
t_clients, t_invocetype, t_invoice, t_payments

I've used autonumber fields in these tables as the primary keys. I've pulled the InvoiceNumber and TransactionID in their own fields ([Invoice_number] and [payment_transac tionid] in their respective tables) and added an index/constraint against these fields to ensure uniqueness). Several reasons for this; however, the main one is that you might change your mind on how these id's are created, if these two are the primary keys in their tables then it is a real mess to change the database structure and legacy records are a pain to deal with; however, using a separate field, legacy entries are retained (making historical searching easier) and it is much easier to deal with any required query, macro, VBA code changes.

Queries:
+ [q_BalanceForwar d_detail], [q_BalanceForwar d_Simple] are based on [q_sum_payments] as discussed previously to show the results.
+ [q_BalanceForwar d_AllInOne] is basically [q_BalanceForwar d_detail]; however, I've pulled the subquery [q_sum_payments] inside the SQL

+ [q_Invoice_User_ GUI] and [q_Payment_User_ GUI] are human readable versions of [t_Invoice] and [t_Payments]. The two tables only store the numeric values for the related table fields and the two "GUI" queries have the "lookup" fields.
This is what I mean by not having the lookup fields at the table level, instead, having them in the query.

Report:
[r_Table_Relatio nships_for_9669 29] ok, simply shows the relationships between the tables that were created by
Ribbon>Database Tools>Relations hips>Relationsh ips

This is what I mean by the normative way of creating the relationships between tables. You do not normally use the "lookup" field at the table level unless there are special needs. The nomenclature is very important here - lookup fields are a special element of the Access table or query object, related fields are an aspect of any relational database. The links in my other posts go into more detail.

When you open the tables in design view in the attached, you'll notice that for the primary and foreign keys I use "PK" and "FK" with the "FK_tablena me" this makes it very easy for someone to follow you later and understand where the data is coming from.

I've not created any nice forms or code for the attached demo, how these are created would depend on the workflow; however, they would mimic the [q_Invoice_User_ GUI] and [q_Payment_User_ GUI]; however, you could do a lot more with the forms than you can in the queries.