Solved

Grouping records in Access 2010 subform in Datasheet View

Posted on 2011-03-16
7
1,194 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
ID: 35146413
0
 
LVL 39

Expert Comment

by:als315
ID: 35146435
May be you can use pivot form?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 35149196
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35149483
Or, ... use a Main/subform

Sample attached...

;-)

JeffCoachman
Database49.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35149516
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
ID: 35155670
Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35157267
OK,

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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