saving/archiving reports (1 Viewer)

[mtc67]

i am very new at access so i would appriciate help in this area. My supervisor wishes to save or archive his monthly reports that are generated by queries in the access database. He wishes to have access to them at alternate times through-out the year for comparison purposes. The problem is that I really have no idea how to even start this project. I have seen the information on output to and publishing it with msWord but my supervisor wishes to just press the button and get the information without any additional fooling around.

I am not sure if this explaination is giving the information you need but i would appreciate even getting ideas as to how to solve this problem.

thanks

 

[Rich]

Presumably you have date fields included in your data in which case the easiest way is to use parameter queries which will allow you to produce reports for any given date range "Solutions" database in samples folder has examples.

 

[mtc67]

yes there are in the main tables but not added to the query as he is mostly concerned with a count of members. I will add the date field to the query and see how it works in conjunction with the solutions examples.
thanks for the help

 

[mtc67]

I was just re-looking at the system and the report is coming from a crosstab query. I have never created one of those but have a general idea of how they work.

The problem now is that in the crosstab there is no mention of a date...I tried adding it and it gave a result of each entry with the date instead of a total of members in each category. I only looked at the result of the crosstab not what it would do to my report.

I am at a loss now as to how to make a report that I can pick out the dates when in the query there is no date mentioned and if I add it I do not get the required results.

Any help would be greatly appriciated.

I will continue to plug along with it while awaiting replies.

Thanks all.

 

[Rich]

Sorry I don't understand, how can monthly reports be issued if there's no reference to any dates? Are you sure the crosstab isn't based on other queries which refer to dates?

 

[mtc67]

i'm sorry when i first asked the question I did not state myself clearly enough.

Each month the user prints a report of all member numbers and their different associations within the organization.

It is not based on a date but on all records available.

The query is based on the members table with the following set:
field: council/club member_type value[memberID] total[memberID]
table: membership membership membership membership
total: group by group by count count
crosstab: row heading column heading value row heading


now what my employeer want to do in this preexisting database is to collect the reports that are printed off each month and compare the numbers from all members in month a with the members from all records in month b and see the difference.

I know it is very confusing but this is what he wants and I am having great difficulty in doing this.

I think what I will have to do is add a date field to the query and create the report based on the date of the member signing up...but my main problem with that is how do I keep the crosstab information that is imperitive in the report?

If you need more explanations plase let me know and I will answer as best I can.

Thanks.

[This message has been edited by mtc67 (edited 06-28-2001).]

 

[Rich]

Do you record the date the members join?

 

[mtc67]

yes it is the field called sign up date

i have tried to add that to the crosstab query but my results seem fooled up

i also put the date in using the criteria as the calender control results but an error message came up

 

[Chris RR]

Taking a different tack... Running a date-based report is not necessarily going to give you the same results as saving a report that is a point-in-time picture of your data (what about deleted records???) (what if you change the table structure???)

If you want to archive a report, you can do an "output to" .rtf format and write it out to disk. The problems are that .rtf will not capture all of your pretty formatting, and that you need to have some way to change the report name that is saved (so you aren't repeatedly overwritting your previously saved data.) You'd also want to figure out how to have this archive run regularly. And, you need a process (maybe manual) to clean up the folder that holds all these reports...unless you've got a whole lotta room on your drives.
This code creates a report name in the format R2001_Jun_28.rtf:
Dim Archivepath As String
Dim Archivename As String
Dim Archivemm As String
Dim Archivemonth As String
Dim Archiveday As String
Dim Archiveyear As String
Dim Archivetemp As String
Dim strDayNumber As String
Dim strRunFor As String
Dim dtOverride As Date

'------------------------------------------------------------
' mDaily_Archive
'
'------------------------------------------------------------
Function mDaily_Archive()
On Error GoTo mDaily_Archive_Err
Archivepath = "\\something\myfolder\Report_Archives\"

dtOverride = Date
Archivemm = Month(dtOverride)
Archivemonth = Format(Archivemm & "/1/99", "MMM")
Archiveday = Day(dtOverride)
Archiveyear = Year(dtOverride)
Archivename = "R" & Archiveyear & "_" & Archivemonth & "_" & Archiveday & ".rtf"

Archivepath = Archivepath & Archivename


' Write out the daily report to the report archives
DoCmd.OutputTo acReport, "rMyReport", "RichTextFormat(*.rtf)", Archivepath, False

mDaily_Archive_Exit:
Exit Function

mDaily_Archive_Err:
Debug.Print "in mDaily_Archive_Err"
MsgBox Error$
Resume mDaily_Archive_Exit

 

[mtc67]

Thanks, I'll give it a try and let you know if I fool it up to badly...

I only have another hour here today but will attempt it in the morning.

But in the meantime other suggestions would be welcome...just in case



[This message has been edited by mtc67 (edited 06-28-2001).]

 

[Rich]

Then you may as well just print and stick it in a file. I thought the object of a database was to access records quickly at any given point in time, having been already stored with the minimum file size.Guess I'll bin mine.

 

[mtc67]

could i take the crosstab and put it in a sub report while refering on the main report to the date...

I am probably thinking of what you can do with a form and sub form and this may not even be acceptable for this type of report.

I may just redesign the whole thing and use calculations to create the numbers but that would take a long time to do and I would rather not if I had another option.

 

[Rich]

Since your only interested in numbers why can't you just use a monthly totals query? I'm sure it will do what you want.

 

[mtc67]

i was hoping to be able to use the existing system and modify it...

how would i go about doing the monthly totals query?

The difficulty i am having with the totals is that across the top the want the clubs (of which there are aprox 15) and down the sides the member types ( again about 15)...now in the calculations they want to put the total of each club with the member type and fill in the report that way

I am at a loss as to how to do this on my own...The cross tab query gave all the information needed but does not include the date...thus making the monthly total a pain

Would it be done like a regular report based on a query and if so what information would i have to put in the query to get the totals for all these fields?

Also the last programmer did not use very much documentation causing me confusion as to what exactly went thought his mind when creating this query. Being a student means that I do not have the knowlege base to figure out what some things do...but i am going to keep trying and not give in...Thnks goodness I'm stubborn or I would have chucked it in long ago.

Thanks for your patience.

 

[Rich]

No don't chuck it in, we know how frustrating Access can be at first but once you can start getting it to do what you want it becomes rewarding.
Make a copy of your report and crosstab query renaming both. Change the datasource of the copied report to the new query, add the date field, open the parameter box, enter Forms![Dtes]![StartDate] ,select Date/Time as data type, And Forms![dtes]![EndDate], again Date/Time, close par box, in the criteria cell for the date field put
Between [Forms]![Dtes]![StartDate]! And [Forms]![Dtes]![EndDate]!
Open the crosstab query now and you should get the prompts for the dates, which should give the initial result, if it does then we can change it to a monthly one.
HTH

 

[mtc67]

Please be patient I will let you know if your suggestion worked for me hopefully today.
I kind of had a mishap and stupidly overwrote my new database with my old one instead of visa-versa. Luckily I do have documentation and can replace all new things fairly quickly.
I look forward to attempting your solution.
Again thanks for all your help!

 

[mtc67]

rich;
i'm still a bit confused about your solution...I am not following some of your directions (i have a problem with that sometimes)...so here goes

please explain:
1) by datsourse i assume you mean recordsource
2)where do I add the dae field- to the report or the query?
3)how do you open the parameter box ( doesn't that open when you put it in the criteria porion of the query)

Thanks for all your help

 

[Rich]

Ok I assume you've made a copy of the original crosstab query and are now working on that, forget the report for the moment, add the date field to the query, don't select it as a row/column header, add the criteria, now it may actually restrict the data to the dates you type in,if it doesn't right click the qbe grid and the parameters box will open, add the parameters, just open the query for now and see if you can restrict the data returned.
HTH

 

[mtc67]

I have followed your suggestions and played with them to see if i could get the results desired but it did not happen.

The variations I have tried are:

1)Between [Forms]![Dtes]![StartDate]! And [Forms]![Dtes]![EndDate]!
result: error message about there being too may bang operators

2)Between [Forms]![Dtes]![StartDate] And [Forms]![Dtes]![EndDate]
result: error message about the jet database engine not recognizing '[forms]![dtes]![startdate]' as a valid field name or expression.

3)Between [Forms]![frmDate]![BeginDate] And [Forms]![frmDate]![EndDate]
(this refers to the form that has the active x contol on it and is taken verbatem from my working query)
result: same error message as above

4)Between [Forms]![frmDate]![BeginDate] And [Forms]![frmDate]![EndDate]
along with the additional check: [forms]![frmdate]![ch_all].[value] criteria Or "1" that was on my other query dealing with dates.

5)added column heading as suggested in the help portion of microsoft reguarding error above (error 3070)
result: message stating that only one column heading should be there

6)added row heading
result: erro meassage as seen in number 1


the field i am referencing is called "signedup"
the table it comes from is "membership"
and the between statement is added to "criteria"

as well I added the information to the parameters box ( thanks for the info I did not know it existed!) and changed it when I changed from form![dtes]![startdate] to form![frmdate]![begindate]

I was very careful with the syntax and did it myself once and copied and pasted a second time with the exact same results.

if you can think of any other reasons why this would not work it would be appriciated.

 

[Rich]

Send me a zipped copy in 97vers and I'll take a look at it for you.