Need Help Making Report with Points Fields

[ableco]

Okay. So for my A2 ICT Coursework I have to create a database and I've ran into some trouble with my final report close to the deadline. Below is the report itself and I need to fill in the blanks output boxes using the query and the two tables.

On the report there are 5 sections: 1 for total and 4 for each of the 4 products. Each product has 3 subproducts - A, B and C. And I have a field with either S, L, I or P so they can be grouped.

I need a calculation of some kind to add up all the points for all 4 products that have been sold and I then need to be able to do this for each individual product.


Report - http://www.dbforums.com/attachments/...-progress1.png
Query - http://www.dbforums.com/attachments/...rogressqry.png
Table - http://www.dbforums.com/attachments/...ields-prod.png
Table - http://www.dbforums.com/attachments/...dcatalogue.png

A person on another site said I should do one of these but I don't know what any of it means or how to do it. If somebody could clarify or say which is the best option for me (an in-experienced database user and maker) or explain it then that would be very helpful.

what you could do is use a dsum for each of the categrories, and assign the value of that to a control, it could even be the source for that control
limit the rows processed to whatever the group is

failinmg that I'd resoprt to some VBA behind the relevant events

declare 4 variables at the top of the report
zero those variables in each group header (if you have more than one header/footer pairing then you'd need another set of 4 variables per header footer pairing

add the points value in the reports format event

in the footer fromat event assign the value of the variabels to the relavnt controls

or
I'd drop 4 hidden control onto the reports detail section, one for each type of point
assign a value to each of those controls
eg
=iif(<whateverinidicatesthisissavings>,15,0)
so say you had a column called RowType and it could be one of "I"nvestment,"S"avings and so om
=iif(RowType="S",15,0)
you could use the expression builder to set the control source if you wished

then in the footer add four controls one for each category and set the source for those controls to be the sum of the detail controls defined above
add another control to add those 4 values to give a total points score for this group
you can then repeat the sum bit in other footers

or you coudl use the IIF in the underlying query so thart uyou have the points tally in the query and use as normal on a report

Thanks.

[ableco]

My post disappeared? What happened?

[June7]

Thread was moderated I think because of the numerous url links. I approved it so it should now be available for review by all.

One must become member of DBforums to view those referenced threads.

Making report look exactly like that example will require creativity which is what your other responder was offering.

Alternative is to do a conventional report using intrinsic Grouping & Sorting with aggregate calcs in group and report footers. Access Help has guidelines on this basic functionality.

[ableco]

Can you link me please?

[June7]

Link you to what? Do what I would: search Access Help (or Google) on topic 'Grouping and Sorting report' or 'Create a Grouped or Summary Report'

[ableco]

I'm trying to add up 4 Annual Totals but it doesn't seem to be working. The report accepts the expression but in the box it says '#Name?'.

The expression:

=([a_qry_AnnualTarget AllProducts]![a_qry_AnnualTarget S]![SumOfPoints_Annual_Target]+[a_qry_AnnualTarget AllProducts]![a_qry_AnnualTarget P]![SumOfPoints_Annual_Target]+[a_qry_AnnualTarget AllProducts]![a_qry_AnnualTarget L]![SumOfPoints_Annual_Target]+[a_qry_AnnualTarget AllProducts]![a_qry_AnnualTarget I]![SumOfPoints_Annual_Target])

Anybody know a fix?

I also have 1 more thing:
I've made a query that is used as the field source by 4 queries. Now in my final query which adds up the sums of those 4 queries only 1 of them shows their fields when I'm trying to make it. When I first create the final query none of the queries showed their fields but then 1 did when I tried it again. Now I keep doing it but nothing works :/ Anybody know what's going on?

Thanks!

[June7]

Is [a_qry_AnnualTarget AllProducts] the RecordSource of report? Try:
=[a_qry_AnnualTarget S].SumOfPoints_Annual_Target + [a_qry_AnnualTarget P].SumOfPoints_Annual_Target + [a_qry_AnnualTarget L].SumOfPoints_Annual_Target + [a_qry_AnnualTarget I].SumOfPoints_Annual_Target


Don't understand last question. If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved in issue.

[ableco]

Is [a_qry_AnnualTarget AllProducts] the RecordSource of report? Try:
=[a_qry_AnnualTarget S].SumOfPoints_Annual_Target + [a_qry_AnnualTarget P].SumOfPoints_Annual_Target + [a_qry_AnnualTarget L].SumOfPoints_Annual_Target + [a_qry_AnnualTarget I].SumOfPoints_Annual_Target


Don't understand last question. If you want to provide db for analysis, follow instructions at bottom of my post. Identify object involved in issue.

That is the source, yes.

I've added the database. There's no confidential stuff in there. It's the Progress Report that all the queries are for.
http://i.imgur.com/JPvZjXF.png

[June7]

Do this with the report:

1. name the Annual Target textboxes like: tbxAnnI, tbxAnnP, tbxAnnS, tbxAnnL

2. expression to sum referencing the textbox names: = tbxAnnI + tbxAnnP + tbxAnnS + tbxAnnL


I always give texboxes meaningful names if I need to use them in expressions.


Reports can be funny about when you can and cannot reference fields in expressions. Usually safe to reference textbox.


c_qry_3 doesn't have in fields in the field grid. I can see all fields in the Field droplist.

[ableco]

Do this with the report:

1. name the Annual Target textboxes like: tbxAnnI, tbxAnnP, tbxAnnS, tbxAnnL

2. expression to sum referencing the textbox names: = tbxAnnI + tbxAnnP + tbxAnnS + tbxAnnL


I always give texboxes meaningful names if I need to use them in expressions.


Reports can be funny about when you can and cannot reference fields in expressions. Usually safe to reference textbox.


c_qry_3 doesn't have in fields in the field grid. I can see all fields in the Field droplist.

Thanks for the help, I'll do that now. That's really weird. The fields are in the droplist for me too now. First there was none and then only one. Oh well, at least it's fixed

EDIT: The renaming text boxes really helped and it works great!

EDIT2: I'm trying to make that final report but when I run it, no values are produced. :/ Do you know why?

[June7]

rpt_Progress works for me with the suggested edits.

What report are you talking about?