• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

alternating colors for groups of records in Forms / Datasheet view?

hi experts:

I have a duplicates query that returns groups of records.  I would like to alternate between grey and white for each group of records in a datasheet view form.  
my query:
SELECT Staged_Accounts.REQID, Staged_Accounts.FILENO, Staged_Accounts.Email, Staged_Accounts.Collector, Staged_Accounts.Reason, Staged_Accounts.Date, Staged_Accounts.Approve, Staged_Accounts.Deny, Staged_Accounts.Maybe, Staged_Accounts.Comment
FROM Staged_Accounts
WHERE (((Staged_Accounts.FILENO) In (SELECT [FILENO] FROM [Staged_Accounts] As Tmp GROUP BY [FILENO] HAVING Count(*)>1 )))
ORDER BY Staged_Accounts.FILENO;  (it's an access canned duplicates query, sorted Ascending)

I have looked at several solutions you fine folks have given for variations of the same problem, but I can't seem to adapt any of them to my problem.  any help appreciated.
0
Chuckbuchan
Asked:
Chuckbuchan
  • 8
  • 4
  • 2
  • +2
3 Solutions
 
Patrick MatthewsCommented:
> in a datasheet view form

You cannot do this in datasheet view.  If you are willing to go to continuous view, we could do something like
use a subquery to get a "line number" into your select clause, and use conditional formatting to highlight the
odd or even rows.

Patrick
0
 
omgangCommented:
If you are willing to display your query datasheet in a form (datasheet view) you can accomplish your goal using Conditional Formatting.  An example would be to insert an incremented counter field into your query (so the records are numbered) and then set conditional formatting for all the fields to
Expression Is       ([txtCounterField]/2) <> ([txtCounterField]\2)
set the cell back color to be whatever you want when the expression is True (odd numbered records).

I got the expression from an example flavo gave some time ago.  It's True for odd numbers and False for even.

OM Gang
0
 
ChuckbuchanAuthor Commented:
ok omgang:
I know where conditional formatiing is and pasting your expression is simple enough.   can you show me how to insert a incremented counter into my query?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Patrick MatthewsCommented:
SELECT Staged_Accounts.REQID, Staged_Accounts.FILENO, Staged_Accounts.Email, Staged_Accounts.Collector, Staged_Accounts.Reason, Staged_Accounts.Date, Staged_Accounts.Approve, Staged_Accounts.Deny, Staged_Accounts.Maybe, Staged_Accounts.Comment, (SELECT Count(*) FROM Staged_Accounts AS S WHERE S.FILENO <= Staged_Accounts.FILENO) AS Counter
FROM Staged_Accounts
WHERE (((Staged_Accounts.FILENO) In (SELECT [FILENO] FROM [Staged_Accounts] As Tmp GROUP BY [FILENO] HAVING Count(*)>1 )))
ORDER BY Staged_Accounts.FILENO;

YOu will need to go with continuous view, though, to get the CF to work...
0
 
hnasrCommented:
:)
0
 
ChuckbuchanAuthor Commented:
thanks Patrick:

I am still working on it.
0
 
Leigh PurvisDatabase DeveloperCommented:
CF works in datasheets too though.
(Not especially fast or anything - but it runs)

:-)
0
 
ChuckbuchanAuthor Commented:
Patrick:
I gave your suggestion (including setting the form to continous forms) a go and was not able to get it to work exactly right.  Some groupings are getting the color change and others are not.  It's not in the alternat groupings like I was expecting. I spent a little time trying to figure out why.  When I looked at the new "counter" field that you added to the query, it does not group in sequential order.  For example, I was expecting something like:
1
1
1
2  colored
2  colored
3
3
3
3
4 colored
4 colored

But rather it looks like this:
Counter:
5
5
15
15
15
17
17
28  colored
28  colored
36  colored
36  colored
36  colored
42  colored
42  colored
42  colored
48  colored
48  colored
48  colored
50  colored
50  colored
68  colored
68  colored
73
73
76 colored
76 colored
81
81
88 colored
88 colored
126 colored
126 colored
126 colored

I think that if the numbers in the counter field were sequenced and grouped, it would work.  I hope this example made sense.
thanks
0
 
Leigh PurvisDatabase DeveloperCommented:
You'll probably need a slightly more complex statement for the subquery...

SELECT Staged_Accounts.REQID, Staged_Accounts.FILENO, Staged_Accounts.Email, Staged_Accounts.Collector, Staged_Accounts.Reason, Staged_Accounts.Date, Staged_Accounts.Approve, Staged_Accounts.Deny, Staged_Accounts.Maybe, Staged_Accounts.Comment, (SELECT COUNT(*) FROM (SELECT S2.FILENO FROM Staged_Accounts S2 GROUP BY S2.FILENO) AS Tmp1 WHERE Tmp1.FILENO <= Staged_Accounts.FILENO) AS Counter
FROM Staged_Accounts
WHERE (((Staged_Accounts.FILENO) In (SELECT [FILENO] FROM [Staged_Accounts] As Tmp GROUP BY [FILENO] HAVING Count(*)>1 )))
ORDER BY Staged_Accounts.FILENO;
0
 
ChuckbuchanAuthor Commented:
LPurvis:
It looks like you added some extra grouping on the Counter sub-query. It did not work on my Staged_Accounts table.  So here is what I did to get it to work:  I made a duplicates query and changed it to a make-table query.  That put all the dups in a table called Dups.  Then I changed your query to reference the dups table rather than the Staged_Accounts table.  It works like a champ.  

Here is what I think.  The Accounts_Table has 161 records of which 40 are duplicates.  The Duplicates consist of more than 2 records in some cases and are scattered down the record set.  When I ran your's or Patrick's query it seemed to get confused by that--like the query was aware of row numbers or something.  I went back to my table and looked to make sure that there wasn't anthing funky in it's field settings but everything looked normal.    

any thoughts?
0
 
ChuckbuchanAuthor Commented:
The Staged_Accounts table has 161 records of which 40 are duplicates, rather.
0
 
Leigh PurvisDatabase DeveloperCommented:
Hard to know what's gone wrong with "It did not work on my Staged_Accounts table".
DId it actually run - but not returning the desired results?  Numbers not right?
I suppose to be completely accurate - the subquery would have to reflect the duplicate critieria.
Whch might lead to extra overhead of course.  But try just

SELECT Staged_Accounts.REQID, Staged_Accounts.FILENO, Staged_Accounts.Email, Staged_Accounts.Collector, Staged_Accounts.Reason, Staged_Accounts.Date, Staged_Accounts.Approve, Staged_Accounts.Deny, Staged_Accounts.Maybe, Staged_Accounts.Comment, (SELECT COUNT(*) FROM (SELECT S2.FILENO FROM Staged_Accounts S2 GROUP BY S2.FILENO HAVING Count(*)>1) AS Tmp1 WHERE Tmp1.FILENO <= Staged_Accounts.FILENO) AS Counter
FROM Staged_Accounts
WHERE (((Staged_Accounts.FILENO) In (SELECT [FILENO] FROM [Staged_Accounts] As Tmp GROUP BY [FILENO] HAVING Count(*)>1 )))
ORDER BY Staged_Accounts.FILENO;
0
 
ChuckbuchanAuthor Commented:
holy crap! it worked.  I havn't looked at what you did yet. I just pasted it in the form's source control.  But it works.
0
 
ChuckbuchanAuthor Commented:
ahh. you put the having count in clause in the sub-query of the counter field, right?  
0
 
Leigh PurvisDatabase DeveloperCommented:
Yeah - the subquery as originally stated would work - but for a scenario like this with a limited result set on the grouping.
So the subquery needs to match that - to return the same records - by which to count).
0
 
ChuckbuchanAuthor Commented:
Lpurvis:
This was a really good session.  I feel like I learned something.  I appreciate your's and everyone's help.  
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now