Results 1 to 6 of 6
  1. #1
    andy295 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Location
    United Kingdom
    Posts
    3

    How to create an itemised Sales Report with month x year

    I'm trying to work out the best way to create a particular sales report.



    I've attached a mockup of how I'd really like the report to look, if possible. (Please note, any duplicated data is just because it's a mockup).

    As you can see from the mockup , the rows are grouped by month with each order listed within that month and a sum for the month. I'd like to have this year and last year side-by-side with a total for each year at the bottom. Does that make sense?

    All the data comes from one table: ORDER(order_id*, order_date, client, ref, price). There are additional fields but I've removed them here to keep it simple

    I have some experience in the past of creating reports but I have no idea how to do this one. I've managed to avoid pivot tables so far but would I need them for this?

    Thanks for any advice!

    Click image for larger version. 

Name:	access-table-v1.png 
Views:	12 
Size:	88.6 KB 
ID:	38438

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Have you got a junk database you can upload (compress your sample database, zip it up, then upload it here). I should think you could do this with the columnar layout of reports but the ragged number of individual records per month might be a problem.

  3. #3
    andy295 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Location
    United Kingdom
    Posts
    3
    Thanks, I've created a sample database with some sample data. See attached.
    Attached Files Attached Files

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm not seeing a great way to do this, how good are you with code (VBA)? I can see a way to do this but you'll have to adapt any code I give you to your actual data.

    EDIT: also...
    can you have multiple orders from the same client on the same day?
    what happens if you have orders from different clients on the same day, do you list those in alpha order by client name?

  5. #5
    andy295 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Location
    United Kingdom
    Posts
    3
    Thanks! I'm not bad at vba though I must admit I hadn't considered that approach. I'm not in front of my PC right now but just had a quick Google. It looks like the way to do it is to create a report on the fly and then insert a text box with absolute positioning for each field. I can certainly write the required loops to do that though it might take some thought to get the months aligned for each year.

    Am I on the right lines?

    These are high value orders (up to $100,000) and repeat orders are rare, even rarer in the same year let alone the same day! We don't have a separate table for clients, each order is treated as if a separate client. Not the recommended approach I know but works for us in this context.

    The ordering of orders on the same day doesn't really matter.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    report-demo.zip

    I actually found a way to do it without code, I'm enclosing an example done a couple of different ways, once with list boxes, one with sub reports. The query driving everything uses domain functions (Dsum) so if you have a ton of data in your actual database this will run pretty slowly over time depending on how much data you have. All in all I think I prefer the subreport method, even though it's a pain to set up the first time (I only did 2 months but you can basically copy everything just modify the SQL statements driving each of the subreports). Note in each subreport there is a TOT control in the report footer where the visible property is set to false, then on the main report I'm just carrying through the value in that TOT field to the main report. Also note each sub report is named in a logical fashion i.e. PY01 (previous year january), CY01 (current year january), PY02 (previous year february), etc. Then all you have to change in each of the subtotal formulas is the name of the control it's pulling from.

    You could probably set the rowsource of each subreport with code as well but I didn't go that far.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-14-2018, 08:20 AM
  2. create date with only month and year
    By kwooten in forum Queries
    Replies: 5
    Last Post: 05-12-2015, 10:01 AM
  3. Replies: 4
    Last Post: 12-13-2013, 01:10 PM
  4. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  5. Month and Day only not Year to create query
    By ssalem in forum Queries
    Replies: 3
    Last Post: 02-28-2013, 02:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums