Total Spent over chosen timeframe

[metrodub]

I have three databases that interact with each other. One is a vendor asset management db that houses vendors that my company uses, their contact information as well as contract data. Another db contains data on Purchase Orders that we use to pay these Vendors. The third database is an invoice database that houses data on invoices paid to the vendors.

I want to build a query that will allow me to see how much we've spent on all vendors for a certain time frame. I am able to do a query that pulls cumulative spending, but when I put in a date range (based on the invoice date), the query returns a dataset that has multiple entries of certain vendors who have submitted multiple invoices during that period.

Here is how the relationships work:

From vendor db:
tblCompany
CompanyID
CompanyName
...

From PO db:
POID
CompanyID
...

From Invoice db:
InvoiceID
POID
AirCost
DeliveryCost
...more Cost fields

Here is the SQL (with the summation narrowed down for brevity's sake):

SELECT tblCompany.CompanyName, Sum([tblInvoices.AirCost]+[tblInvoices.DeliveryCost]) AS [Total Spent], tblInvoices.InvoiceDate
FROM tblCompany INNER JOIN (tblInvoices INNER JOIN tblPO ON tblInvoices.POID = tblPO.POID) ON tblCompany.CompanyID = tblPO.CompanyID
GROUP BY tblCompany.CompanyName, tblInvoices.InvoiceDate
HAVING (((tblInvoices.InvoiceDate)>=#1/1/2006# And (tblInvoices.InvoiceDate)<=#1/31/2006#));

How do I go about getting the multiple entries to combine?

 

[Melagan]

Upon first reading, your questions looks like you're wanting to link multiple databases with relational information. According to your SQL, you actually have ONE relational database with multiple tables.

Anyway, according to many other posts that I've read here, the HAVING clause is more for restricting data in aggregate functions. Since your HAVING clause is restricting dates, you should replace HAVING with WHERE in your SQL. See what that does for you.

Next, I would try removing tblInvoices.InvoiceDate from your GROUP BY clause. So your final SQL could be something like:

SELECT tblCompany.CompanyName, Sum([tblInvoices.AirCost]+[tblInvoices.DeliveryCost]) AS [Total Spent], tblInvoices.InvoiceDate
FROM tblCompany INNER JOIN (tblInvoices INNER JOIN tblPO ON tblInvoices.POID = tblPO.POID) ON tblCompany.CompanyID = tblPO.CompanyID
GROUP BY tblCompany.CompanyName, 
WHERE (((tblInvoices.InvoiceDate)>=#1/1/2006# And (tblInvoices.InvoiceDate)<=#1/31/2006#));

...typed, not tested. Good luck!



~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

except if you are going to include InvoiceDate in your SELECT statement, you have to have it in your Group By Clause. Do you really want it to sum by Date and Company or just company?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Melagan]

Oops..didn't catch that myself; Leslie is right on the money. You can use that field to restrict your data in the WHERE clause without having it in your SELECT and GROUP BY clause.


~Melagan
______
"It's never too late to become what you might have been.

 

[metrodub]

I am now getting an "Undefined function 'WHERE' in expression" error when I try to run this SQL:

SELECT tblCompany.CompanyName, Sum([AirCost]+[DeliveryCost]) AS [Total Spent]
FROM tblCompany INNER JOIN (tblInvoices INNER JOIN tblPO ON tblInvoices.POID = tblPO.POID) ON tblCompany.CompanyID = tblPO.CompanyID
GROUP BY tblCompany.CompanyName,
WHERE (((tblInvoices.InvoiceDate) >=#01/01/06# And (tblInvoices.InvoiceDate) <=#01/31/06#));

 

[lespaul]

the WHERE comes before the GROUP BY:

SELECT tblCompany.CompanyName, Sum([AirCost]+[DeliveryCost]) AS [Total Spent]
FROM tblCompany INNER JOIN (tblInvoices INNER JOIN tblPO ON tblInvoices.POID = tblPO.POID) ON tblCompany.CompanyID = tblPO.CompanyID
WHERE (((tblInvoices.InvoiceDate) >=#01/01/06# And (tblInvoices.InvoiceDate) <=#01/31/06#))
GROUP BY tblCompany.CompanyName;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[metrodub]

Wow...is it Monday? Time to turn my brain on. Thanks Leslie!