Pass parameter and calculate YTD values

[Sidney Lynn]

This is a great website, folks! Here is my situation:
Records are being downloaded from a mainframe into an Access table. Unfortunately, the field names are AugCurrSales, AugPriorSales, etc. for current year and prior year sales. I started creating reports, but already have SOOO many queries, reports, and subreports.
To get the data into a more “normal” structure, I did the following: I created 12 queries to extract the data for each month. For example, qryAugust has (among others) a Month field, Mo# field, CurrSales field, and PriorSales field. I ‘manually’ enter “August” in the month field and “8” in the Mo# field for each record.
Then I created a Union Query that merges the 12 month queries together. So now my Union Query has the same data in it as the original table, but instead of one record and multiple fields for months of Sales and PriorSales, in this Union Query each Location now has 12 records - one for each month. (PS: I can’t put an actual date in the Date field in my 12 month queries because as each new year occurs the same fields are used … otherwise I could use ‘Between’ parameter prompts).

Here’s the problem. Management wants to be able to choose a month from a form and click ‘print’ and 10 different reports will print for that month. However, most reports show (among other values and in different groupings) CurrSales and PriorSales for the month, AND Year-to-Date values! I have successfully created a Dialog Form that lets the user choose from a list of months and then send that selection to the query that the report is based upon and one report prints the month’s values. But I need help figuring out how to calculate the YTD values, how to get them on the same report as the monthly values, and how to print all 10 reports at the same time.
The fiscal year is July 1 - June 30. I don’t know VBA but want to learn it. I’ve spent 5 days reading and looking on the Internet for answers - and have learned a lot - but I need help.
Q1: To calculate YTD values, would I create Select Case code that - depending on which month the user selected in the drop-down listbox - & have the code sum the values in the CurrSales and PriorSales fields “? I.e. If Mo#=9, then YTDCurrSales = (CurrSales where Mo#=7 + CurrSales where Mo#=8 + CurrSales where Mo#=”9”). That looked like a possibility from my research. What do you recommend?
Q2: If Q1 is accomplished with VBA code, I think from my reading I can figure out how/where to type it in, but where do I point to the code so that it will run ‘after’ the selection is made on the form and pass the month and calculated values to the 10 reports? (Another Switchboard form gives the manager the option of previewing any report for any month, so I don’t want to hard-code an event in the reports if I can help it)
Q3: To automatically print the 10 reports with one “OK” command button, it appears I just need multiple DoCmd Open Report lines. If I didn’t have the YTD challenge in Q1, I’d guess that this commands could go in a macro attached to the “OnClose” event of the form or something. I’ve not tried to print multiple reports with one command button before. Please advise in this instance.
Q4: Lastly, how do I put monthly data and YTD data in the same report? I’ve not done a Cross-Tab report before but saw some references to that. But since I’m not sure how to calculate the YTD values, I can’t make headway here. Please advise.

I hope my description above is clear. I had hoped to figure this out all by myself, but am running out of time and just lost my mind. Please help. Thanks in advance.

 

[Cosmos75]

I usually create a Month Table to allow user to select months.

tblMonth
MonthID (1, 2, 3, 4, ...)
Month (JAN, FEB, MAR, APR, ...)

Then create a form that has a combobox populated with the fields from tblMonth and a textbox where the user enters the year. Use that to create you query to select records that match you need. You'll have to play around with your criteria for that.

 

[Cosmos75]

This might help you get started.

Access 2000 version. Let me know if you need an Access 97 version.

 

[Sidney Lynn]

Thanks for reply. I got a dialog form to work as indicated, but I used a listbox bound to a Month field. (I thot of adding Mo# field later thinking any 'sum' code would be so much easier) I can change to combo box. But it's my Q1, Q2, Q3, and Q4 I need help on. I thought of entering multiple posts, but it all has to work together & I can't believe any of my 4 Qs are that uncommon. (Perhaps the moderator will suggest a diff forum...?) Anyway, hope someone can find the time to get the big picture of what I'm trying to do, how it all would work together, and advise on the particular Q's.

 

[Fornatian]

Sorry to but in...

Cosmos, you can achieve the same as your table structure with this expression:

Month("01-" & YourCombo & "-1999")

Where Me.Combo is a value list "Jan","Feb","Mar", etc... I find it unlikely that they are going to increase/decrease the number of months in a year so this works for me.

 

[Cosmos75]

Cosmos, you can achieve the same as your table structure with this expression:

Month("01-" & YourCombo & "-1999")

I am not sure what you mean by that? What is a Value List? Maybe it's because I don't know what that is that I am using a table instead??

Do you mean I can have some kind of value list (1-12) and format the combobox to Month("01-" & ValueList & "-1999")??

I find it unlikely that they are going to increase/decrease the number of months in a year so this works for me.

What I do is to keep that table (tblMonth) hidden and enter the numbers and months manually, from 1 to 12, JAN to DEC. Keeping it in a table allows me to quickly build the ListBox of ComboBox since the recordsource is a table, saves me from having to type 1,2,3,....12. The downside is that someone could muck up the table which may make it worthwhile to use your approach but I don't know how to implement what you are suggesting.


Shelly_UK, for your Monthly reports, all you have to do is set the criteria to

Monthly
Month(DateField)=frmCriteria.cboMonth
AND
Year(DateField) = frmCriteria.txtYear

Year To date (Jan to current month?)
Month(DateField) Between 1 and frmCriteria.cboMonth
AND
Year(DateField) = frmCriteria.txtYear

If you are trying to show a YTD and Monthly summary of the same data, just use the YTD query to pull all the indivdual records that fit your YTD criteria.
Create a that is grouped my Month (by making the the date your first grouping and it should automatically group it by month) and ask it to SUM your data which should end up being in you report footer.

Is your data all numbers?

If you could list out your table structure and let us know what you are trying to do with it, that would help. Even better, if you could post a sample of your database here (leave in some test data and zip it - I think it needs to be less than 100 kb?).

 

[Fornatian]

Cosmos..

Instead of getting your combo box to look at a table select the rowsource as 'Value List' and add "Jan","Feb","Mar" etc.. as the list. You can then use the Month() expression above to return the Month number.