Access Report to Compare Sales in This and Previous Year

[Joe Y]

I am working on a sales report that needs to compare sales (in dollars and in cases sold) grouped by Customer and by Month. The report format is as attached image (created by MS Word for demonstration).

The report opened with criteria using invoice date specified on two text boxes (txtFromDate and txtToDate) from form “F_Sales_Date”. Another two hidden text boxes “txtFromDate2” and “txtToDate2” are calculated same date period for previous year.

Learning from an example, my approach to this task is to create two separate queries then join the two queries with Customer No field "ordh_cust_no". The results of join query looked fine. I can see all information I needed and can easily manipulate the data in a spreadsheet. However, I cannot put them in Access report with the format I wanted.

The involved tables are listed below.

Relevant fields from Table "DBA_order_header"

ordh_cust_no
ordh_last_inv_no
ordh_time_inv_printed
ordh_ord_no
 

Relevant fields from Table "DBA_order_detail"

ordd_stock_no
ordd_qty_ordered
ordd_ord_no
 

Relevant fields from Table "DBA_customers"

cust_lname
cust_no
 

Relevant fields from Table "DBA_inv_payment"

pymt_invoice_amt
pymt_ord_no
 

Relevant fields from Table "DBA_inventory"

stock_desc
 

Two involved queries are listed below.

Query 1: Q_Sales_Report_2Yrs1

SELECT DBA_order_header.ordh_cust_no, DBA_customers.cust_lname, DBA_order_header.ordh_last_inv_no, DBA_order_header.ordh_time_inv_printed, DBA_inv_payment.pymt_invoice_amt, DBA_order_detail.ordd_stock_no, DBA_inventory.stock_desc, DBA_order_detail.ordd_qty_ordered
FROM (((DBA_customers INNER JOIN DBA_order_header ON DBA_customers.cust_no = DBA_order_header.ordh_cust_no) INNER JOIN DBA_inv_payment ON DBA_order_header.ordh_ord_no = DBA_inv_payment.pymt_ord_no) INNER JOIN DBA_order_detail ON DBA_order_header.ordh_ord_no = DBA_order_detail.ordd_ord_no) INNER JOIN DBA_inventory ON DBA_order_detail.ordd_stock_no = DBA_inventory.stock_no
WHERE (((DBA_order_header.ordh_time_inv_printed)>=[Forms]![F_Sales_Date]![txtFromDate] And (DBA_order_header.ordh_time_inv_printed)<=[Forms]![F_Sales_Date]![txtToDate]));
 

Query 2: Q_Sales_Report_2Yrs2

SELECT DBA_order_header.ordh_cust_no, DBA_customers.cust_lname, DBA_order_header.ordh_last_inv_no, DBA_order_header.ordh_time_inv_printed, DBA_inv_payment.pymt_invoice_amt, DBA_order_detail.ordd_stock_no, DBA_inventory.stock_desc, DBA_order_detail.ordd_qty_ordered
FROM DBA_inventory INNER JOIN (((DBA_customers INNER JOIN DBA_order_header ON DBA_customers.cust_no = DBA_order_header.ordh_cust_no) INNER JOIN DBA_inv_payment ON DBA_order_header.ordh_ord_no = DBA_inv_payment.pymt_ord_no) INNER JOIN DBA_order_detail ON DBA_order_header.ordh_ord_no = DBA_order_detail.ordd_ord_no) ON DBA_inventory.stock_no = DBA_order_detail.ordd_stock_no
WHERE (((DBA_order_header.ordh_time_inv_printed)>=[Forms]![F_Sales_Date]![txtFromDate2] And (DBA_order_header.ordh_time_inv_printed)<=[Forms]![F_Sales_Date]![txtToDate2]));
 

These two queries are essentially the same except the invoice date (ordh_time_inv_printed) from each query pointed to the period of this and previous year respectively as mentioned above using criteria listed below.

Criteria of invoice date (ordh_time_inv_printed) in Query “Q_Sales_Report_2Yrs1”:

>=[Forms]![F_Sales_Date]![txtFromDate] And <=[Forms]![F_Sales_Date]![txtToDate]
 

Criteria of invoice date (ordh_time_inv_printed) in Query "Q_Sales_Report_2Yrs2":

Criteria of field “ordh_time_inv_printed”:   >=[Forms]![F_Sales_Date]![txtFromDate2] And <=[Forms]![F_Sales_Date]![txtToDate2]
 

The joined query "Q_Sales_Report_Comp_Cust":

SELECT Q_Sales_Report_2Yrs1.ordh_cust_no, Q_Sales_Report_2Yrs1.cust_lname, Q_Sales_Report_2Yrs1.ordh_last_inv_no, Q_Sales_Report_2Yrs1.ordh_time_inv_printed, Q_Sales_Report_2Yrs1.pymt_invoice_amt, Q_Sales_Report_2Yrs2.ordh_cust_no, Q_Sales_Report_2Yrs2.cust_lname, Q_Sales_Report_2Yrs2.ordh_last_inv_no, Q_Sales_Report_2Yrs2.ordh_time_inv_printed, Q_Sales_Report_2Yrs2.pymt_invoice_amt, Q_Sales_Report_2Yrs1.ordd_stock_no, Q_Sales_Report_2Yrs1.stock_desc, Q_Sales_Report_2Yrs2.ordd_stock_no, Q_Sales_Report_2Yrs2.stock_desc, Q_Sales_Report_2Yrs1.ordd_qty_ordered, Q_Sales_Report_2Yrs2.ordd_qty_ordered
FROM Q_Sales_Report_2Yrs1 INNER JOIN Q_Sales_Report_2Yrs2 ON Q_Sales_Report_2Yrs1.ordh_cust_no = Q_Sales_Report_2Yrs2.ordh_cust_no
ORDER BY Month([Q_Sales_Report_2Yrs1].[ordh_time_inv_printed]), Month([Q_Sales_Report_2Yrs2].[ordh_time_inv_printed]);
 

As for the report, my problem is grouping based on month. When I grouped month from each query, one following the other one, the result is that all monthly sales of 2nd year listed under each month of 1st year and repeated every month from 1st year.

In addition, I cannot figure out the way to arrange items sold in each month the way I wanted in format as shown in attachment. If nothing sold of an item in certain month I still want the item listed with Invoice as $0 and case as 0.

Can someone help me with this report design? Is it query design that needs to be worked on or report design that can solve the problem?

Thanks in advance!

[zmbd]

if the query is returning the correct data then we're looking at the report formatting and there are some limitations to the Access reporting.

I am unfortunately not in a position to open your attachment. If it has an image, please pull that image out and attach it directly using the advanced editor.

The months without data is a secondary question and may be solvable using an expansion query or conditional.