Grouping records in Access 2010 subform in Datasheet View

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?
HikarusAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
als315Commented:
0
 
als315Commented:
May be you can use pivot form?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonCommented:
Or, ... use a Main/subform

Sample attached...

;-)

JeffCoachman
Database49.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
HikarusAuthor Commented:
Thanks!
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK,

Did you note the disadvantages of using this approach...?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.