Grouping on Continuous Forms

Question

Hi

I have a simple continuous form that would be clearer if I could group the results by the week number field and have a separator between each group.

Is this possible?  I've done some searching but cannot find the solution I'm looking for.

Thanks.

---

Chris | UK

Answer 1

Unlike reports, forms have no grouping mechanism.  You can order the form's RecordSource query by the year and then week number.  To visually differentiate each week's rows you can then apply conditional formatting based on an expression which returns True for each alternate week to change the BackColor of each control in the form.  If the data includes all weeks, with no gaps, then you can do this on the week number itself, e.g.

   [WeekNumber] Mod 2 = 0

If there are gaps in the sequence of weeks, or the dates span the end of the year, then the following query is an example which returns sequential numbers for each group of rows per transaction date, ordered by TransactionDate, T1.TransactionAmount, T1.TransactionID

SELECT COUNT(*) AS GroupCounter, T1.TransactionID
FROM Transactions AS T1 INNER JOIN
    (SELECT DISTINCT TransactionDate
     FROM Transactions)  AS T2
ON T2.TransactionDate<=T1.TransactionDate
GROUP BY T1.TransactionDate, T1.TransactionAmount, T1.TransactionID;

In your case the grouping would be by the year and week number, not the date.  This could then be referenced by the DLookup function in another updatable query to number the rows sequentially, e.g.

SELECT *,
DLOOKUP("GroupCounter", "qryGroupCounter","TransactionID = " & [TransactionID]) AS GroupCounter
FROM Transactions
ORDER BY TransactionDate, TransactionAmount, TransactionID;

and the GroupCounter column used in the expression to conditionally format the rows in a form based on this query.

---

Ken Sheridan, Stafford, England

Answer 2

Thanks for this Ken.

The year won't need to be included as the records included in the query will never last more than a month, so when the years roll over to the next, starting again at week 1 won't matter.

And, with fear of sounding a bit thick, where am I placing the code?

---

Chris | UK

Answer 3

If the form will always have an unbroken sequence of week numbers, then you merely need to use the expression [WeekNumber] Mod 2 = 0 as the basis for the conditional formatting of each control in the form's detail section.  You might need to change WeekNumber to the actual name of the column in the form's recordset of course.

If you can't guarantee consecutive week numbers, then you will first need to create the qryGroupNumber query (the one starting SELECT COUNT(*) AS GroupCounter), adapting it as necessary so that it returns a number for each week, rather than each date as in my example.  In the form's RecordSource query you'd then add a GroupCounter column using the expression DLOOKUP("GroupCounter", "qryGroupCounter","TransactionID = " & [TransactionID]), again adapting it so that it uses your form's recordset's primary key, rather than TransactionID.  You can then conditionally format the controls with the expression [GroupCounter] Mod 2 = 0

---

Ken Sheridan, Stafford, England

Answer 4

is the form just for display and selecting a row to work on? (no editing).

The reason I ask, is I often now use a report for display. So I have a report like this:

Note the grouping.

and if I click on a row, then the form to edit that one row is launched. So often, a lot of continuous forms are used for display, and to pick what to work on, and not actually edit data.

Of course, if the form needs to edit data, then using a report will not work, but Access 2010 added a VERY nice feature that allows you to drop in a report as a sub form (or a form). So, we can now use reports for displaying data, and it really is rather nice.

So in this example (it is a partial screen cap), we wanted a grouping by date.

So the above is actually a report - and you can attach events to controls like a form.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada

Answer 5

Hi Ken and Albert

Ken, I get what you're saying now.  Yes, the form does need to be editable and your solution works.

Albert, I had not considered using a report that has controls on it to open an editable form and yes, that would work too.

Now I just have to decide which idea to go with.

Thank you both.

---

Chris | UK