'group/combine' similar query results with a + symbol in datasheet view (1 Viewer)

Shecky

New member
Local time
Today, 08:56
Joined
Jul 29, 2021
Messages
25
history.png

I have a simple query that lists order history, one record for each Order Detail. I want to group all the same customer orders with a '+' (that acts as a dropdown) so that it will shorten the list and make it easier to view. In the above example All those records would combine into one and clicking on the + will expand the list. I know access does a similar thing when you have a sub table such as order details. All the Details are combined under one under number with a +.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:56
Joined
May 7, 2009
Messages
19,175
you put it in a Form, so it will be easy to code what you need to accomplished.
or you can put (insert) the "summary" in a table. the summary table must have relation
to your "detail" table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:56
Joined
Oct 29, 2018
Messages
21,358
Hi. So, why can't you use subtables (as you called it)? Don't you have a separate Customers table?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 19, 2002
Messages
42,981
Queries don't work like this. They have no events.
You can use subdatasheets which will work exactly as you described but are visually confusing or you can use two subforms side by side on an unbound form. As you click on one row in the left side table, the right side table fills with the related items.

But, regardless of which method you want to use, you need to create a summary table as arnel said if it doesn't already exist. It is also possible to bind the left subform to a query that does the summarization for you. However, if that is what you choose, then your tables are not properly normalized and you should think about fixing the table structure before moving on.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
If you do not have a table so can simply do a query that returns each company name and anything unique to that company. From that query you can add "saleshistoryquerybyregion" as a subdatasheet. Set the link to companyname. You can add a sub data sheet to any query just like you can to a table. Since this happens often automatically, many people do not know how to add them manually. See bottom right if not familar. It is the same as linking a subform.
datasheet.png
 

Users who are viewing this thread

Top Bottom