Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2014
    Posts
    5

    Smile Unanswered: formatting a query

    hi Everyone

    I would like to ask if there is any way i can adjust my query so that table assembly num will only display once for each unique value.

    here is my query

    SELECT distinct(INDEX.ASSBLYNUM), Data.SEQ, Data.DRAWNUM, Data.TYPE
    FROM [INDEX] left outer JOIN Data ON Data.ASSMBLYNUM = INDEX.ASSBLYNUM
    WHERE (((INDEX.ASSBLYNUM) In ("RC713DR","YSFC2D2536L","")))
    group by INDEX.ASSBLYNUM, Data.SEQ, Data.SEQ, Data.DRAWNUM, Data.TYPE;
    Attached Thumbnails Attached Thumbnails 1.jpg  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do formatting (including hiding of values in the user interface (the form or report that consumes the data)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2014
    Posts
    5
    Hi healdem

    i display that info on a subform so technically yes? is there a way to just have one entry on the assembly num? so it dooesnt look like its repearting?
    i want to make it look like something like this

    Assblynum seq part# partdec
    seq part# partdec
    seq part# partdec
    Assblynum seq part# partdec
    seq part# partdec
    seq part# partdec
    seq part# partdec
    thanks a lot!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The value RC713DR appears in 5 rows. Which one should be kept and according to which criteria?
    Have a nice day!

  5. #5
    Join Date
    Mar 2014
    Posts
    5
    Just the 1st entry? the one beside 1 on the sequence column?
    is it doable?

    thanks!!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if these are in a sub form, then
    have a parent form which controls the assembly number (and everything relevant to that instance of the assembly BUT NOT the parts that comprise the assembly)
    have a child form which displays the part (and everything relevant to that part number)

    because the link between the parent and child form is the assembly number you dont' need to show the assembly number in the child form, as its common data

    As said before you can control this sort of detail in the front end/user interface, BUT it needs to be there in the back end so you can manipulate the data
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2014
    Posts
    5
    HI Thank,

    Is it possible to show me an example? Im sorry im a student trying to learn

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no I don't have an example to hand
    create a form with your assemblies on it
    create a form with your parts on it
    embed the parts form into the assembly form linking on assembly no
    save both
    edit the parts form and remove the assembly from display

    otherwise Id suggest dong a googl eon embeddign forms in access
    or sub form in access
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the value of SEQ for the rows to keep in the query is always 1, you can use:
    Code:
    SELECT DISTINCT (INDEX.ASSBLYNUM), Data.SEQ, Data.DRAWNUM, Data.TYPE
    FROM [INDEX] left outer JOIN Data ON Data.ASSMBLYNUM = INDEX.ASSBLYNUM
    WHERE (((INDEX.ASSBLYNUM) In ("RC713DR","YSFC2D2536L","")))
    group by INDEX.ASSBLYNUM, Data.SEQ, Data.SEQ, Data.DRAWNUM, Data.TYPE
    HAVING Data.SEQ = 1;
    Have a nice day!

Tags for this Thread

Posting Permissions

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