microsoft/access/training/courses - Subtotals

The UK's most regular instructor-led training courses.
Training information: microsoft/access/training/courses · Access course microsoft training · Microsoft Access Training
See also · microsoft access training · access vba courses · access database course

Resolved · Low Priority · Version Standard

Subtotals

Andrea has attended:
Excel Advanced course

by - delegate Andrea [1 post] (2007 Mar 7 Wed, 13:08) Reply

I am using subtotals with the function "Max", which displays the highest value in an array. I would also like to display the product the value refers to, rather than just the value itself.
Can anybody help?

Version	Date	Location	Places available	Book	Next place rate:
Access VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Dec 4 Thu + 5 Fri	Southwark	7	book now	£345	£399
2003	2009 Jan 8 Thu + 9 Fri	Southwark	6	book now	£450	£485
2003	2009 Feb 5 Thu + 6 Fri	Southwark	8	book now	£275	£399
2003	2009 Mar 5 Thu + 6 Fri	Southwark	7	book now	£345	£399
2003	2009 Apr 8 Wed + 9 Thu	Southwark	8	book now	£275	£399
2003	2009 May 7 Thu + 8 Fri	Southwark	8	book now	£275	£399
Full Schedule: See all 12 Access VBA course dates. Bookings currently available until 5th November 2009.

RE: subtotals

by - trainer Richard platinum contributer [818 posts] (2007 Mar 12 Mon, 17:01) Reply

hi Andrea

I have emailed you a possible solution to your gmail account.

Basically you can do it by using match & index, similar to Vlookup. The different is that Vlookup only works one way, whereas the index and match can be assembled to your requirements.

Take a look at the attachment and see if you can see how I did it. IF so, apply it to your situation.

Richard

· Why consider Access Visual Basic training?
· How To Find Good Access Database Courses In London
· Want to Become the Office Hero? MS Project Training Could be the Answer
· How Reorganising Your Data Can Take Your Business to the Next Level
· Quick Access Course: Creating a Customer Contact Database in 8 Simple Steps

Access tip:

Deleting duplicate records from a table

You cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table.

1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table or you may loose data.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.

View all Access hints and tips