Solved

Grouping records in Access 2010 subform in Datasheet View

Posted on 2011-03-16
7
1,172 Views
Last Modified: 2012-05-11
I know this can be done, but I don't remember how:

I need to group records in a subform in datasheet view, so that records with the same value in the grouping field will be displayed as one with the 'plus' sign to expand the containing records.

I tried doing it by SQL group by in the recordsource property, but it doesn't work. Any ideas?
0
Comment
Question by:Hikarus
  • 4
  • 2
7 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
May be you can use pivot form?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
Depending on the structure of your data, you may be able to do this with "Subdatasheets"
A Subdatasheet will allow you to expand/contract the related Child records of any one Parent record.

An example would be One customer with many Orders.
Create the two tables and relate them on CustomerID .

Then when you open the Parent table (Customers), the subdatasheet will appear (as a small black Plus symbol (+))
Clicking this small "+" will expand and contract the Child/Orders.

You can set up the same layout with what you are calling "groups" as long as the two tables are related on a common field.
ex.
tblColors
ColorID( PK)
ColorName

tblProducts
ProductID (PK)
ColorID (FK)
Price

Here , each "Color" would be what you are referring to as a "Group".
So in relating these two tables on ColorID, you will be able to open the Color Table and click the subdatasheet to see all the products with that color...

;-)

JeffCoachman
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Or, ... use a Main/subform

Sample attached...

;-)

JeffCoachman
Database49.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
The disadvantages of using SubDatasheets are:
1. They give your users direct, *Unlimited* access to the Raw table.
This means they can do whatever they like (Delete Record/Fields, change table properties, ...etc)
:-O

2. They are a drag on performance:
http://support.microsoft.com/kb/275085

0
 

Author Closing Comment

by:Hikarus
Comment Utility
Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK,

Did you note the disadvantages of using this approach...?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now