Help in generating a report

[Aravind]

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

[Steve]

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.

[Aravind]

How exactly do I do that?

[Steve]

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.

[Aravind]

How do I calculate the sum for the Total Daily Allowance, etc. in the
query??

[Steve]

#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.

[Jana]

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