Derived/computed values in reports?

[RhinoCan]

I'm fairly new to Access but I've been looking at some of the tutorials and find one rather critical issue unanswered so far. I'm trying to determine if a report can contain data derived or computed based on other information in the report?



Specifically, I want to make a summary report of sales that summarizes sales by item and then calculates the value of those sales based on a lookup table.

Here's what the Sales table looks like:

Order_Date	Order_No	Item	Quantity
2015-12-01	123456	Widget	2
2015-12-14	123467	Spanner	5
2015-12-15	123478	Goblet	3
2015-12-16	123489	Widget	1
2015-12-18	123490	Widget	8
2015-12-18	123491	Widget	2
2015-12-18	123495	Spanner	1
2015-12-19	123499	Goblet	5
2015-12-30	124502	Widget	2

Here's what the Bonus table looks like:

Item	Rate_Begin_Date	Rate_End_Date	Item_Bonus	Quantity_Bonus
Goblet	2000-01-01	9999-12-31	$4.00	$2.00
Spanner	2000-01-01	9999-12-31	$2.00	$1.00
Widget	2000-01-01	9999-12-31	$3.00	$3.00

I'd like the summary report to look like this:

SALES SUMMARY REPORT for Week of December 13, 2015 through December 20, 2015

Item: Goblet
Total Orders: [count of rows referring to Goblets in that week] = 2
Total Units: [sum of quantities for all Goblet rows for that week] = 8
Total Item_Bonus: [Total Orders x Item_Bonus for Goblets] = 2 x 4.00 = $8.00
Total Quantity_Bonus: [Total Units x Quantity_Bonus for Goblets] = 8 x 2.00 = $16.00
Total Bonuses = [Total Item_Bonus + Total Quantity_Bonus] = 8.00 + 16.00 = $24.00

Item: Spanner
Total Orders: [count of rows referring to Spanners in that week] = 2
Total Units: [sum of quantities for all Spanner rows for that week] = 6
Total Item_Bonus: [Total Orders x Item_Bonus for Spanners] = 2 x 2.00 = $12.00
Total Quantity_Bonus: [Total Units x Quantity_Bonus for Spanners] = 6 x 1.00 = $6.00
Total Bonuses = [Total Item_Bonus + Total Quantity_Bonus] = 12.00 + 6.00 = $18.00

Item: Widget
Total Orders: [count of rows referring to Widgets in that week] = 3
Total Units: [sum of quantities for all Widget rows for that week] = 11
Total Item_Bonus: [Total Orders x Item_Bonus for Widgets] = 3 x 3.00 = $9.00
Total Quantity_Bonus: [Total Units x Quantity_Bonus for Widgets] = 11 x 3.00 = $33.00
Total Bonuses = [Total Item_Bonus + Total Quantity_Bonus] = 9.00 + 33.00 = $42.00


I'll probably lay out the summary report in a more tabular fashion; I'm just trying to be clear on what I want for now.

I'm not seeing anything in Access that allows you to add derived data like this but I have to believe there is some way to do it. Can anyone tell me what I need to do?

[June7]

Why are your dates showing yyyy-mm-dd structure?

Ideally, the Bonus table record ID would be saved into the Sales table. Lacking that, alternative methods involve:

1. DLookup() expression in query or textbox

2. Cartesian query

[RhinoCan]

Why are your dates showing yyyy-mm-dd structure?

That's pretty much how I always store them; it's the ISO (International Standards Organization) format. Is that a problem? Do I need to use a different format to make things work in Access?

Ideally, the Bonus table record ID would be saved into the Sales table. Lacking that, alternative methods involve:

1. DLookup() expression in query or textbox

2. Cartesian query

Can you elaborate on these three techniques? I'm not sure I understand what you mean for any of them in sufficient detail to do them.

A tutorial or video describing how to do derived values would be very helpful but I'm not sure what terms to search on to find one or who does a good video/tutorial.

[June7]

Access has a Date/Time field type. The value is actually stored as a number but Access defaults the display to mm/dd/yyyy. Any variation from that can be an issue.

What is it you do not understand about saving the PK of Bonus table as FK in Sales? This would mean selecting Bonus record from a combobox or running code to retrieve the ID based on other user inputs.

DLookup is a domain aggregate function. Suggest you do some research on their use.

A Cartesian query is a query that does not have a JOIN clause or is a limited JOIN - all records of each table are associated with all records of other table. Can't do a join on the date fields because the Bonus table has a date range defined by two fields. Do some experimenting with the query builder, something like:

SELECT Sales.*, Bonus.* FROM Sales INNER JOIN Bonus ON Sales.Item = Bonus.Item WHERE [Order_Date] BETWEEN Rate_Begin_Date AND Rate_End_Date;


Reports can do aggregate (summary) calculations using Sorting & Grouping features with aggregate calculations in textboxes.