Hide Duplicates on a form instead of a report

Posted on 2012-09-05
Last Modified: 2012-09-05
I need to create a continuous form. One of the fields identifies the asset being considered and so what I would like is to have the first time a new asset appears in the list to have it highlight (different colour, non-blank, whatever) and all the records below it associated with that asset to be a different fromat. I know I can do it easily with a report but this needs to be a form view because the user needs to be able to select a record and I don't think you can detect which record has been selected on a report.

The form's data source is a Union query that knocks out duplicate records automatically. The first component of my union query will always contain the first record in the list to be displayed but if I include an indicator field in that query then the union will not automatically exclude duplicate records because the indicator field in the first query will make the record no longer a duplicate.

I don't think it is going to be possible to do what I am asking but I have to ask in case.
Question by:Rob4077

    Author Comment

    BTW, even if I can do this on a report, my preference is to use a form because I can more easily, and dynamically change other values on the form like column headings etc
    LVL 84
    The form's data source is a Union query that knocks out duplicate records automatically
    If this is true, then how could you possibly hide duplicates (since there would be none)?

    It would be best is you could provide some sample data to show us what you mean.

    Author Comment

    I try to explain what I mean but it doesn't always work.

    Source1 is a query that selects every asset in the Asset table AND the most recently entered record in the linked Messages table, whether the message has been actioned or not. If there are no records in the linked messages table it still includes the record.  
    UNIONed with
    Source 2 is a query that selects all the records in the table that have not been actioned.

    Thus it is almost inevitable that at least one record in Source 2 also exists in Source 1. By grouping them in a UNION query it gets rid of those duplicate records.

    What I need to display is all the records in the resultant output. However it will often happen that there will be more than one record there for each asset. On the form I want to highlight somehow when the next asset listing begins.

    Asset       RecordNum
    0001        12345
    0002        12456
    0003        12567

    The application is going to mean that I am going to be adding more and more fields in the Queue table as time goes by, hence I want to stick with Queue.* rather than redefining the query every time I add a field. And the user wants to have the data display in datasheet view or one big wide continuous form.

    As I say, I don't think what I want to do is going to be easily possible but I am looking for ideas.

    The actual union query follows:
    SELECT Queue.*, tblAssets.*, tblMessageStatus.MessageStatus
    FROM (qryLatestMessageByAsset LEFT JOIN (Queue LEFT JOIN tblMessageStatus ON Queue.FolderId = tblMessageStatus.pkFolderid) ON qryLatestMessageByAsset.MaxOfMessageId = Queue.MessageId) LEFT JOIN tblAssets ON qryLatestMessageByAsset.pkCellNo = tblAssets.pkCellNo
    SELECT Queue.*, tblAssets.*, tblMessageStatus.MessageStatus
    FROM tblAssets RIGHT JOIN (Queue LEFT JOIN tblMessageStatus ON Queue.FolderId = tblMessageStatus.pkFolderid) ON tblAssets.pkCellNo = Queue.CellNo
    WHERE  tblMessageStatus.pkFolderid Between 20 And 21 AND Queue.InAlert=True
    ORDER BY Description, pkCellNo, MsgDate DESC;
    LVL 84

    Accepted Solution

    You'd have to do this with some sort of Temporary table, I'd suspect. You could use your Union query to build that temp table, and then loop through it to remove the Asset duplicates, then present that data to the user.

    Author Comment

    Thanks for confirming. I want to explore an alternative before I take that route. I will raise another question to see if I can somehow modify one of my queries. Thanks for your help.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now