How do I create a query with totals?

I am trying to create an outstanding invoice report. Each invoice per customer can have many invoice details. Each detail may be taxable. I started by creating three queries one contains the customer data and invoice information to include the terms, tax rate, and so on. The second query contains details of the invoice, each item they are billed for and if it is taxable. The last query contains the payment information. What I am trying to do is create a finally query that puts it all together. I would like to see each invoice with a grand total minis the payment. QueryTest.accdb QueryTest.accdb
seanlhallAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
1. OK, Tax is now included, but there is still a discrepancy between your total(s) and mine.

2. Your table structure here does not appear to be properly "Normalized".
Hence I had to go through a lot of machinations to get this all to work.
3. Your naming convention need to be tightened up a bit.

For example:
a. The tax rate would normally be associated with the customer directly, or the geographic location of the transaction.
b. Your tax rate seems somewhat haphazardly placed in the invoice table.
c. Your queries were strangely lacking in calculations, so I had to resort to aggregate functions.
d. None of your tables have primary keys or permanent relationships established.

Perhaps these issues are a function of this being a sample database...

I have created systems similar to this I can post a very basic example if you wish, when this question is resolved.

In any event, here is the new db:

JeffCoachman
QueryTest.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
To avoid ambiguity, can you post a static table illustrating the *Exact* output you are looking for?
0
 
seanlhallAuthor Commented:
Companyname
invoicenumber
date
detailtotal (that includes tax)
Paymenttotal (all payments made)
Gradtotal(detailtotal - paymenttotal)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jeffrey CoachmanMIS LiasonCommented:
What happened to posting a static table with the *exact output* you want.
(Include the *exact* values)

In other words post *EXACTLY* exactly what you want to see.


Perhaps another expert expert will be willing to work from your previous posts...
0
 
seanlhallAuthor Commented:
I'm sorry,I must have misunderstood.. Do you mean using the data I have in test database?
0
 
Jeffrey CoachmanMIS LiasonCommented:
yes
0
 
Jeffrey CoachmanMIS LiasonCommented:
I just want to minimize any possible ambiguity.

I also wanted to avoid excessive bandying about to tweak the output.

Jeff
0
 
seanlhallAuthor Commented:
I made a table called tblsampleresults. This is the results I am l.ooking for. Sorry I misunderstood what you were looking for.  QueryTest.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Double check your calculations carefully, I got slightly different numbers than you.

Compare my QuerySampleResults, with your tblSampleResults

JeffCoachman
QueryTest.accdb
0
 
seanlhallAuthor Commented:
The first record is off by .65 I found that that was my fault the payment was entered with .65 short. The second records does not have the tax included. The two detailID 338 and 339 taxed is true so the rate tax rate in tblinvoicetable of .06 should be applied, this invoiceID 355. Also sometimes only some of the details are taxed.
0
 
seanlhallAuthor Commented:
Looks great. I made some changes and fixed the PK and the relationships. One problem I cam accross and is I get ERROR# in the calculations when the record in the tblinvoicetable has been deleted but the related record is in the tbldetails. I realize this is my fault and I see how to correct this for the future. The problem is my current real data how can I make the query work when I have many deleted records in the tblinvoicetable with related records left behind in tbldetails. Also if there is no payment record for an invoice I get an error. Again thanks so much, calculations give me difficulties. QueryTest-ver3.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
The issue of the "Deleted" data should not be an issue with your current design.

With referential integrity enforced you *should not* be able to delete a parent record if a child record still exists.
So I am curious as to how you are deleting records...
0
 
seanlhallAuthor Commented:
The sample database has referentail integrity enforced. The acually database did not have this enforced. So the user was able to delete and invoice which removed the record from tblinvoicetable but the related records in tbldetails . were left. How can I get around this issue?

Also I was able to add this to the query to take care of the possiblity of a null payment record.

PaymentTotal: CCur(Nz(DSum("[Payment]","tblPayment","InvoiceID = " & Nz([InvoiceID],0)),0))
0
 
Jeffrey CoachmanMIS LiasonCommented:
So, ...problem solved?
0
 
seanlhallAuthor Commented:
Almost. I'm stuck. The QueryInvoiceDetail in the ExtTotal I get an error when the record in tblinvoicetable has been deleted. I tried putting Nz in but I cant get it correct. This is what I entered.
"The expression you entered contains wrong number of arguments"

ExtTotal: (([quantity]*[Rate])*(CDbl(Nz(DLookUp("[TaxRate]","tblInvoiceTable","InvoiceID=" & ([InvoiceID],0))),0)))+([quantity]*[Rate])
0
 
Jeffrey CoachmanMIS LiasonCommented:
Why was the recorfd deleted...?

Again, you get this because you have orphaned records.

The solution seems to be to delete the child records too.
0
 
seanlhallAuthor Commented:
How can I delete the orphaned records, I am dealing with thousands of records.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Again, this is all new information..., not related to your main question.

First press Shift+F9 to requery the data and see if this clears things up.

Is the data on a server?, try reducing the refresh rate (refresh more often)

I really hate to make suggestions on this because I don't know your data, or what the consequences might be.
...but you can *make a backup of the data* and try this:
http://support.microsoft.com/kb/247766
0
 
Jeffrey CoachmanMIS LiasonCommented:
oh!

One other not, I think you may need to add in the "IsTaxed" criteria to some formulas.

Something like this, *roughly*:
dlookup("TaxRate","SomeTable","InvoiceID=" & InvoiceID & " And " & "IsTaxed=-1")

Jeff
0
 
seanlhallAuthor Commented:
TaxTotal: (([quantity]*[Rate])*(CDbl(DLookUp("TaxRate","tblInvoiceTable","InvoiceID=" & [InvoiceID] & "And" & "Taxed=-1"))))
I tried several diffent ways but I keep getting #ERROR
0
 
Jeffrey CoachmanMIS LiasonCommented:
This is why I feel that the tax rate is in the wrong table...

In any event, this should work:
IIF(Taxed=-1,(([quantity]*[Rate])*(CDbl(DLookUp("TaxRate","tblInvoiceTable","InvoiceID=" & [InvoiceID]))))+([quantity]*[Rate]),[quantity]*[Rate]

...*remember* this is a patchwork solution...
In a truly normalized system all of these machination would probably not be needed.

Again, I would advise you to post a new, related question to validate your design here.


;-)

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.