Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    47

    Unhappy Unanswered: How to display duplicate numbers....?

    If you look at the Access screen shots that I have provided the upper image shows the reference table. I have written a query that gives me the bottom table as output. what I am trying to do is based on the column named "concat" which serves as an Index, pull the values in the columns named "PSMM1PHASE" & PSMM2PHASE....PSMM5PHASE and align them vertically alongside the respective "concat" or index value as shown on the bottom table.
    PROBLEMS:
    The problems that Iam having are.
    1. I do not want it to include the blank cells or blank records.
    2. Access is not showing duplicate records like for the particular "concat" value "100-1-1019" there are three records with the value of "1" but in the result you can see that Access only shows a single "1" instead of
    100-1-1019-----1
    100-1-1019-----1
    100-1-1019-----1
    100-1-1019-----3
    100-1-1019-----0
    100-1-1019-----0
    100-1-1019-----0
    100-1-1019-----0
    100-1-1019-----0
    100-1-1019-----0
    I am doing this cause in a later query i am running counts which count the value like how many times there is a record " =>0 <2 " and ">=2 5< " so on and so forth. The SQL is as follows.(NPhase-just a name to the new column)

    SELECT CONCAT, PSMM2PHASE AS NPHASE FROM TRIAL1
    UNION
    SELECT CONCAT, PSMM3PHASE AS NPHASE FROM TRIAL1
    UNION
    SELECT CONCAT, PSMM4PHASE AS NPHASE FROM TRIAL1
    UNION
    SELECT CONCAT, PSMM5PHASE AS NPHASE FROM TRIAL1;
    Attached Thumbnails Attached Thumbnails Screenshots.bmp  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First create queries excluding the blank values then link them with a UNION query, this should solve your first problem.

    As far as the second problem is concerned (Access not showing duplicate records) I don't see any reason why: i must be missing something here, sorry!

    Have a nice day!

  3. #3
    Join Date
    Feb 2009
    Posts
    47

    Done

    i figured it out it was just Union all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •