By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,730 Members | 1,524 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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.