Need Help for Creating Report Ms Access

[New Comer]

Dear All Honorable,

I am making this database for our milk shop. Tables forms and queries have been made. Now I want to create a report. 4 queries have been created for the report. 1 for Report Customer Queries(main Report). AND 2. Purchase Detail Query, 3. Product Amount Query, and Payment Detail Query.( maybe subreport or what you suggest)

I want to make the report according to this image. But I don't know exactly how it will happen. I sincerely hope that you will definitely help me in this regard. I need a form and an on-click VBA script that searches on monthly bases and not dates. And similarly, the summary report of whatever the customer has purchased throughout the month should be displayed as per the image. Please guide me step by step.
Thank you all so much.

** This is My 1st Time with MS access or any Database Application, Please Help
Thanks

[June7]

The summary data for each product can be generated in report design using Sorting & Grouping features with expressions in textboxes, like:

=Sum(IIf(Product = "Milk", Quantity, Null))

The total is simply =Sum(Quantity)

Those textboxes would be in a group footer section. Do research on building report with Sorting & Grouping. This is basic Access functionality.

The Paid and Remaining calcs will be most complicated aspect.

If you want to provide db for analysis, follow instructions at bottom of my post.

[New Comer]

Thank You @June7
You are so Kind Thank You here is My Access File:

[June7]

A subreport is one way to go but not really necessary in this case. The Paid and Remaining calcs were simple because you are saving payment data in Sales table. Review attached Munasib.zip

If there can be different types of milk/yoghurt/butter, some modifications will be required.

Customer name should not be in Sales table - I deleted field. Calculated data like Total and Remaining should not be saved to table. These should be calculated when needed. Can use Calculated type field in table but calculation in query or textbox is adequate.

Advise not to use exact same field name in multiple tables, such as CustomerID. I would use CustomerID_FK in Sales.

[New Comer]

A subreport is one way to go but not really necessary in this case. The Paid and Remaining calcs were simple because you are saving payment data in Sales table. Review attached Munasib.zip

If there can be different types of milk/yoghurt/butter, some modifications will be required.

Customer name should not be in Sales table - I deleted field. Calculated data like Total and Remaining should not be saved to table. These should be calculated when needed. Can use Calculated type field in table but calculation in query or textbox is adequate.

Advise not to use exact same field name in multiple tables, such as CustomerID. I would use CustomerID_FK in Sales.

Thank You June7 َ
Your kindness and guidance has impressed me a lot. In the light of this, I tried something else through which I got some good results. But there is one problem which is not being solved. The results are getting mixed. February and March (these are the only entries) i.e. the correct data of the customer name is being filtered but the month is getting mixed. Apart from this, the entire result is being repeated 3 times. What should I do sir!!
here is Image...

[Welshgasman]

You need to sort by date as well in report.

[June7]

The report I provided you works. So what have you done differently? I expect you did something in the query. Provide your code or another db.

[New Comer]

The report I provided you works. So what have you done differently? I expect you did something in the query. Provide your code or another db.

Dear Sir! I have learned so much from your guidance, when I posted earlier I did not understand Access. I will always be grateful to you. Sir, even then I had written that I want a summary, Sir, if you look at the first row of the image, you will see that milk is 35.85 kg. This is a summary of a month. Now the summary is created but the results are getting mixed. The customer name and purchase are also correct but the result of both the months is showing together. It is not being resolved by group and sort.

[June7]

Again, it works in the file I provided you. Dates are NOT mixed and NOT repeated within vendor and data is summarized in footer sections. You have changed what I provided but I am not sure what you did. Show your code.

If you don't want to show detail records, the Detail Section can be set to Visible No while you do summary calcs in group footers. Otherwise, the RecordSource SQL would be like:

SELECT Customers.CustomerID, Customers.CustomerName, Customers.CustomerAddress, Customers.CustomerPhone, Products.ProductID, Products.ProductName, 
            Sum([Quantity]*[PricePerUnit]) AS SumTotal, Sum(Sales.PaidAmount) AS SumPaid,
            Format([SaleDate],"yyyymm") AS YearMo, Format([SaleDate],"mmm yyyy") AS MonYear
FROM Products INNER JOIN (
          Customers INNER JOIN 
                 Sales ON Customers.CustomerID = Sales.CustomerID) ON Products.ProductID = Sales.ProductID
GROUP BY Customers.CustomerID, Customers.CustomerName, Customers.CustomerAddress, Customers.CustomerPhone, Products.ProductID, Products.ProductName, Format([SaleDate],"yyyymm"), Format([SaleDate],"mmm yyyy");

or

SELECT Customers.*, Products.ProductID, Products.ProductName, SumQ.*
FROM (Customers 
INNER JOIN (
        SELECT Sales.CustomerID, Sales.ProductID, Format([SaleDate], "mmm yyyy") AS MonYear, Format([SaleDate],"yyyymm") AS YearMo,
                    Sum([Quantity]*[PricePerUnit]) AS SumTotal, Sum(Sales.PaidAmount) AS SumPaid 
        FROM Sales GROUP BY Sales.CustomerID, Sales.ProductID, Format([SaleDate],"yyyymm"), Format([SaleDate], "mmm yyyy"))  AS SumQ 
ON Customers.CustomerID = SumQ.CustomerID) 
INNER JOIN Products ON SumQ.ProductID = Products.ProductID;

If Unit is always KG, don't need to save this into table. If unit can be different, then it becomes meaningless when data is aggregated.

[New Comer]

@JUNE7
Dear Sir, I will be forever grateful for the way you guided me. I am still in the learning phase, what I need to do with the report is, if a customer buys milk 45 times in a month then the report will become very big, so I am taking only one line summary of the data wise instead of the entire month detail. In this way the summary of 10 products will be on one page.

Here is file:

[June7]

I have given you two ways to accomplish that.
First is to use the report I already provided and simply set Detail section to Visible No.
Other is to use a query that aggregates data as report RecordSource. I showed you two queries.

In both approaches, would still use Sorting & Grouping with Sum() calcs in footer section and NO subreport.

Your latest attempt still utilizes a subreport - why?

[New Comer]

Thanks To all,
Solved !!
This is my first MS Access project. So I couldn't understand quickly where the mistake was. I understood the mistake, when I separated the month from the date, I deleted the date column after that, now I haven't deleted it... Now I am getting a single line summary of the entire month, no data mix-up, no duplication. Thank you all.