building a query based on purchases_total and purchases_details

[ethan1701]

Hi,
I just built my first Access DB, to manage my online store's inventory and revenue. The store itself if based on mySQL, but I've never used access before...
I wanted to build a report but got lost there, so I reverted to the following query:
I want to know how much money I spent on a monthly basis.
the two relevant tables are:
purchases_total and purchases_details, with the columns:
purchase_date . . . . . products_quantity
discount . . . . . products_cost
shipping

The general idea being that I might get a discount on all the products in a purchase, and this won't affect the shipping. The tables have a one-to-many relationship

So I tried the following query:
SELECT format(PT.date_purchased,'mmm yyyy') AS [month], Sum(PD.products_quantity*PD.products_cost*(1-PT.discount)+PT.shipping) AS expense
FROM purchases_total AS PT, purchases_details AS PD
WHERE PT.purchases_id=PD.purchases_id
GROUP BY format(PT.date_purchased,'mmm yyyy');
and discovered I have a problem with the calculation. As far as I can tell, it's adding the shipping cost of each order to every product. What I need is to count the shipping only once. How could I do this?

The next step would be putti9ng this into a report...

-Ethan

 

[Jon K]

You can do it with a series of two queries:
A query to calculate the total expense per purchase
A second query to get the monthly total based off the first query

See the attached database (run the second query).

.

 

[GGib7711]

I want to know how much money I spent on a monthly basis.
.......................
.......................
So I tried the following query:
SELECT format(PT.date_purchased,'mmm yyyy') AS [month], Sum(PD.products_quantity*PD.products_cost*(1-PT.discount)+PT.shipping) AS expense
FROM purchases_total AS PT, purchases_details AS PD
WHERE PT.purchases_id=PD.purchases_id
GROUP BY format(PT.date_purchased,'mmm yyyy');

Pat,

Can a report with a subreport, as what you suggested, produce what Ethan wanted, that is, a two-column report showing the money spent on a monthly basis?