Solved

Grouping records in Access 2010 subform in Datasheet View

Posted on 2011-03-16
7
1,240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 40

Expert Comment

by:als315
ID: 35146413
0
 
LVL 40

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 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