Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: Providing Summary by Product group

    Following is a detail representation of a report I have (each field is a column and the report has a lot more detail; I am simplifying it for here):

    Customer / Contract# / SaleMonth / Product / Sale
    ABC / 001 / Jan / widget1 / $500
    ABC / 001 / Jan / widget2 / $100
    ABC / 001 / Feb / widget1 / $200
    BCD / 002 / Jan / widget2 / $300
    BCD / 002 / Feb / widget1 / $100

    I'd like to have a summary for each customer, by product, totaling the sales from contract inception to present. This is how I conceptualize it:

    SUMMARY Report of Sales By Customer and By Product:

    ABC / 001 / widget1 / $700
    ... / ... / widget2 / $100
    BCD / 002 / widget1 / $100
    ... / ... / widget2 / $300

    This would be a total for each product for a customer for the full time period when the products were sold.

    I can provide the above totals in a group footer of the above detail report, but have had to specify each product. I would like it to determine what product was bought by a customer and automatically record the summary for each.

    Any help greatly appreciated.

    Best,

    Jabo

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Use report wizard and add groupings by Customer, contract, and then widget. You may need to create your own qry to be the record source.

  3. #3
    Join Date
    Mar 2004
    Posts
    82
    Hi,

    I have avoided the idea you suggest since I've already got too many groupings - geographical area, division, purchasing group, and customer and it would get terribly busy. Plus, creating a group on product would break up line items in the detail section. I would like to see a month-wise detailing of all products purchased.

    What I have now are several summary fields in the Customer footer that gives me sum, avg, etc for the fields I am interested (sales, contract commitment, etc). But, these are for each product I physically specify in the control source IIF statement (eg: [Product]="widget1" etc).

    I am seeking a generic control source specification (in the footer) which would automatically compute the summary only for the products featuring in a customer's purchases and indicate the name of the product as well. If ABC bought widget1 and widget2, I would like the summary to show aggregates for widget1 and widget2 in the ABC grouping and other products for other customers.

    I'd like to be able to do this in the form of an expression since my knowledge of VB is still basic.

    Jabo

  4. #4
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Here's a sample query to get things in order by month:

    SELECT Format([ModifiedDate],"yyyy mm") AS YrMon, First(Format([ModifiedDate],"mmm yyyy")) AS Display, Count(FileList.Filename) AS CountOfFilename, Sum(FileList.FileSize) AS SumOfFileSize, Sum([FileSize]/1000000) AS FileSzMB
    FROM FileList
    WHERE (((FileList.DomainName)="US-CT-EB01" Or (FileList.DomainName)="EB-NNS" Or (FileList.DomainName)="BUILTIN") AND ((FileList.FileType)<>"lnk"))
    GROUP BY Format([ModifiedDate],"yyyy mm")
    ORDER BY Format([ModifiedDate],"yyyy mm");

    Notice the "Format" statements combine the year and month.

    I then used a second query to do a left join matching the YrMon field:

    SELECT qryFilesByDate_NNS.Display, qryFilesByDate_NNS.FileSzMB AS NGNN, qryFilesByDate_EB.FileSzMB AS EB
    FROM qryFilesByDate_NNS LEFT JOIN qryFilesByDate_EB ON qryFilesByDate_NNS.YrMon = qryFilesByDate_EB.YrMon
    WHERE (((qryFilesByDate_NNS.YrMon)>"2003 01"))
    ORDER BY qryFilesByDate_NNS.YrMon;

    This allowed me to match files in one table and in the second. I also had two more mirror image queries that did it with respect to the other variable. I was then able to use these later queries to drive a chart. The two chart I put in one report side by side as I wanted.

    My recommendation is to shape your queries using the format function and snag the year and month to differential between the correct monthly data. Then you could probably secondarily sort on product or what have you.

    Then your IIF statement can use this YrMon variable to do the subtotals. (I think).

  5. #5
    Join Date
    Mar 2004
    Posts
    82
    Hi:

    Thanks for the suggestion. Could you "explode" the sql script? Why do you specify the file name and size? Does this refer to the MDB file? I also have trouble understanding the where clause.







    Originally posted by poliarci
    Here's a sample query to get things in order by month:

    SELECT Format([ModifiedDate],"yyyy mm") AS YrMon, First(Format([ModifiedDate],"mmm yyyy")) AS Display, Count(FileList.Filename) AS CountOfFilename, Sum(FileList.FileSize) AS SumOfFileSize, Sum([FileSize]/1000000) AS FileSzMB
    FROM FileList
    WHERE (((FileList.DomainName)="US-CT-EB01" Or (FileList.DomainName)="EB-NNS" Or (FileList.DomainName)="BUILTIN") AND ((FileList.FileType)<>"lnk"))
    GROUP BY Format([ModifiedDate],"yyyy mm")
    ORDER BY Format([ModifiedDate],"yyyy mm");

    Notice the "Format" statements combine the year and month.

    I then used a second query to do a left join matching the YrMon field:

    SELECT qryFilesByDate_NNS.Display, qryFilesByDate_NNS.FileSzMB AS NGNN, qryFilesByDate_EB.FileSzMB AS EB
    FROM qryFilesByDate_NNS LEFT JOIN qryFilesByDate_EB ON qryFilesByDate_NNS.YrMon = qryFilesByDate_EB.YrMon
    WHERE (((qryFilesByDate_NNS.YrMon)>"2003 01"))
    ORDER BY qryFilesByDate_NNS.YrMon;

    This allowed me to match files in one table and in the second. I also had two more mirror image queries that did it with respect to the other variable. I was then able to use these later queries to drive a chart. The two chart I put in one report side by side as I wanted.

    My recommendation is to shape your queries using the format function and snag the year and month to differential between the correct monthly data. Then you could probably secondarily sort on product or what have you.

    Then your IIF statement can use this YrMon variable to do the subtotals. (I think).

  6. #6
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    I use file name and size because my DB tracks files. You would use Customer / Contract# / SaleMonth / Product / Sale/format(orderdate,"yyyy mm"), etc. in your query. I pasted it cause it was late on friday and quick to do as an example. My where clause was used to get specific files by domain name which is another file spec. If you do a dir /q on a small folder on your hard drive using a dos prompt you will see the file owner and domain name of each file/folder.

    Sorry, for being lazy on late friday.

    Build your query to get the results in a table as you want to see the data, then build a report off of that qry to make it pretty.

    HTH

  7. #7
    Join Date
    Mar 2004
    Posts
    82
    Thank you. I'll check it out.


    Originally posted by poliarci
    I use file name and size because my DB tracks files. You would use Customer / Contract# / SaleMonth / Product / Sale/format(orderdate,"yyyy mm"), etc. in your query. I pasted it cause it was late on friday and quick to do as an example. My where clause was used to get specific files by domain name which is another file spec. If you do a dir /q on a small folder on your hard drive using a dos prompt you will see the file owner and domain name of each file/folder.

    Sorry, for being lazy on late friday.

    Build your query to get the results in a table as you want to see the data, then build a report off of that qry to make it pretty.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •