Method of report generation

[Waheed2008]

Hi all,
I am working on a database and want to generate reports of different types. I have a table Expenses having different fields. The requirement is that I want to generate reports Year wise, Month wise, Week wise and Department wise. This is the requirement of project. What I understand is that when user select month July and Year 2008, total expenses of this month will be displayed. Or when user select Year 2007, its full year report is displayed. Same might be the case with other options.

I have never done such reporting and dont know how to manage that. I am attatching sample file. Can any body tel me how should I do this.

Thanks

 

[JohnLee]

Hi,

I've had a look at your sample DB, firstly, I've learnt that it's good practice when setting up your tables to identify the data type when naming your fields for instance:

ExpenseID because it's an AutoNumber should be lngExpenseID
InvoiceNo because it's a text type data field should be strInvoiceNo
Date because it's a date type data field should be dtmDate
Expense because its a currency type data field should be curExpense

and so on. This will help when you develope your application further and make life much easier.

Now to your question. Firstly create a copy of your existing query and name it something like qryExpensesMonthly.

Open up the query in design view and add the date field to the grid.

In the field row you will now have dtmDate, click in this field and change to read as follows:

Month: Month([dtmDate])

Then staying in the same column, move your mouse the field named "Criteria", which sits about 5 rows below your current field. in section enter the following:

Month(Now())

Then run the query, You will then have information pertaining to the current month [month specific] including your calculations. Base your report on this query.

Do the same method for your Yearly query replacing Month with Year in each case as shown above. This query will be based on the current year to date when ever you run it. so as each month passes by, you will progressively see your year to date figures.

I'm looking into the weekly one, as for me that's proving a bit more tricky.

Point to note is that whenever either query is ran, they will display data for the current month or year only, so for instance when the month of september comes along and you run the month query, only data pertaining to the month of september will be displayed.

Hope this assists.

John

 

[JohnLee]

Hi,

Further to my last response with regards to the Monthly and Yearly queries in the criteria fields if you replace:

Monthly(Now()) with [Enter Month] whenever you run the query it will prompt you to enter the required month which will be a number from 1 - 12, i.e. 1 for January 2 for February etc.

Yearly(Now()) with [Enter Year] then whenever you run the query it will prompt you to enter the required year in yyyy format i.e. 2007, 2008 etc.

Base your reports on these queries.

Now for the weekly query, Create a query a previous described in my earlier post, add in a new columns as follows:

Year: Year([dtmDate]) in the field row
Year(Now()) in the criteria row

Week: Format([dtmDate],"ww") in the field row
[Enter Week Number] in the criteria row.

Whenever you ran this query you it will always be for the current year and will prompt you to enter the current week number.

Base your report on this query.

There is most likely a much better way of doing this, but my knowledge hasn't reached those great depths yet.

I hope this is of assistance.

John

 

[Waheed2008]

Firstly I would like to thanks JohnLee for his so much valueable time and so much detailed response. All of the suggestions are worthwhile and I think it will do the trick. Again, thanks a lot to Lee.

Now the problem is of Department wise report. I have presented very simple scenario, infect, in my actual project Department Name will range from 15 to 60 characters and it will be quite difficult for a user to remember full string and paste in querybox. Is it possible that a form is presented to user which will have a combobox populated with distinct departments from Expenses table, and user select some department from that list to generate report.

I dont know whether it is possible or not, and if possible - then - how!

Can any budy help me in this regard.

Thanks in Anticipation