459,730 Members | 1,524 Online
Need help? Post your question and get tips & solutions from a community of 459,730 IT Pros & Developers. It's quick & easy.

# Help in generating a report

 P: n/a I have a query, like the following: (Assumption: an ID will not have an entry in both "Meal" and "Daily" on the same date; it can only have an entry in either "meal" or "Daily" per date) (Calculation: Total = Meal * 15 OR Total = Daily * 9) (Autonumber as primary key [hidden]) ================================================== ================= ID | Name | Date | Meal | Daily | Total | | | Allowance | Allowance | Allowance ================================================== ================= 13738 | Ismail | 2/11 | | 1 | 9 15899 | Sugu | 3/11 | | 1 | 9 16210 | Musa | 3/11 | | 1 | 9 16210 | Musa | 3/11 | | 1 | 9 16213 | Ithnin | 4/11 | 1 | | 15 16213 | Ithnin | 4/11 | 1 | | 15 16213 | Ithnin | 5/11 | 1 | | 15 ================================================== ================= I have to generate a report such as below (the user first have to select a date range): (Example) Date Range: Begin: 3/11 End : 5/11 ================================================== ========================== No | Name | ID | Total Daily | Total Meal | Total | | | Allowance | Allowance |Allowance ================================================== ========================== 1 | Sugu | 15899 | 1 | | 9 2 | Musa | 16210 | 2 | | 18 3 | Ithnin | 16213 | | 3 | 45 | | | | | ================================================== ========================== Total | 3 | 3 | 72 ================================================== ========================== The reports is supposed to select the IDs and its associated name amongst the date range from the query , then sum each IDs respective Daily Allowances or Meal Allowances, and then display the Total Allowance (based on the calculations above). It will also display a grand total of Daily Allowances, Meal Allowances and Total Allowances. How do I create this report? Any ideas? (Also let me know if I did not explain anything properly.) Thank you Dec 19 '05 #1
6 Replies

 P: n/a If it were me, I'd first write a query to select the data with the parameters you discussed. Once your query is retrieving the data you wish to have in your report, you can base the report off of your query. At that point all you will have to do is format the report's fields the way you want them to look. Dec 19 '05 #2

 P: n/a How exactly do I do that? Dec 20 '05 #3

 P: n/a Once you have your query written, you can create a report and set its Record Source to your query's name. Then you drag the fields from the Field List to where you want them to appear on your report. Dec 20 '05 #4

 P: n/a How do I calculate the sum for the Total Daily Allowance, etc. in the query?? Dec 23 '05 #5

 P: n/a #1: Use the Sum() function. #2: Write your own database. I'm happy to answer questions, but I'm not going to write your entire application for you. Use Google. Dec 23 '05 #6

 P: n/a Aravind: Try this query: SELECT ID, Name, Nz(Sum([MealAllowance]),0) AS MealTotal, Nz(Sum([DailyAllowance]),0) AS DailyTotal, Nz(Sum(TotalAllowance),0) AS SumOfTotalAllowance FROM tblAllowances WHERE (((Date) Between [Enter Begin Date] And [Enter End Date])) GROUP BY ID, Name; I'm using the NZ function to convert empty fields to 0 so you can use them to make grand totals on your report. You'll have to change 'tblAllowances' to the name of your query that built the table you showed us. For information on how to do these types of queries, you should look in help for 'customizing queries' and find out about creating a totals query. Once your query is grabbing and totalling the way you want, then write a report that's based on your new query. If you use the wizard, it should give you guidance on how to create grand totals on the report. HTH, Jana Dec 23 '05 #7

### This discussion thread is closed

Replies have been disabled for this discussion.