Also doing Sales Data Analysis

[audenino]

We have a sales database that lists Product#, FiscalMonth, Fiscal Year, $, and Units for each sale made.&nbsp;&nbsp;We'd like to create a query that will give us YTD sales, per product#, per month.&nbsp;&nbsp;For example, for a given product#, the $ value at JuneYTD would be a total of all sales for that product through June.<br><br>Additionally, we want to do a chart showing a comparison of YTD data for 1999 and 2000, by month.<br><br>I am having trouble calculating the data and getting it into the format needed to create the chart.<br><br>Any ideas? <p>Roxanne Audenino<br><a href=mailto:roxanne_audenino@agilent.com>roxanne_audenino@agilent.com</a><br><a href= > </a><br>

 

[harryrich]

You might try first pulling the data together by year and month in a summation query.&nbsp;&nbsp;You can use the Year and Month functions to break these out from the transaction date.&nbsp;&nbsp;It you have a lot of data and this query take time, you can save some by makeing a make table query and retaining your summaries in a table.<br><br>Next build a query joining your table or query to itself on the month.&nbsp;&nbsp;Select the year current year from one instance and the prior year from the other.&nbsp;&nbsp;You will probably want to rename each instance in its property so you can keep it straight.&nbsp;&nbsp;This will put comparison data on the same line.<br><br>Next on this last query, build a simple report, just column headers and values lined up underneath.&nbsp;&nbsp;You can generate you year to date fields using the running sum property. <br><br>Finally, the report can be exported as an Excel spreadsheet which you can chart directly in Excel, or import back into Access as tablular data.