Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Access-SQL programming question

    I have a SQL script below that gives me recoded values into separate columns, one column per specific recode. Is there any type of SQL program that will put all recodes in just "1 column"?

    SELECT DISTINCT tblDemographics.lname, tblDemographics.pid, vw_HFS_SIPS.ID, tblDemographics.frmdate, First(DateDiff("m",[frmdate],[examdate])) AS [The Value], IIf([The Value] Between 3 And 8.99,"1"," ") AS Six, IIf([The Value] Between 9 And 14.99,"1"," ") AS OneYear, IIf([The Value] Between 15 And 20.99,"1"," ") AS 18, IIf([The Value] Between 21 And 26.99,"1"," ") AS TwoYear, IIf([The Value] Between 27 And 32.99,"1"," ") AS 30, IIf([The Value] Between 33 And 38.99,"1"," ") AS ThreeYear, IIf([The Value] Between 39 And 45.99,"1"," ") AS 42, IIf([The Value] Between 46 And 51.99,"1"," ") AS FourYear, IIf([The Value] Between 52 And 57.99,"1"," ") AS 54, IIf([The Value] Between 58 And 300,"1"," ") AS 5ormore
    FROM tblDemographics INNER JOIN vw_HFS_SIPS ON tblDemographics.id_num = vw_HFS_SIPS.ID
    GROUP BY tblDemographics.lname, tblDemographics.pid, vw_HFS_SIPS.ID, tblDemographics.frmdate, vw_HFS_SIPS.EXAMTYPE
    HAVING (((tblDemographics.pid)=11 Or (tblDemographics.pid)=12 Or (tblDemographics.pid)=13) AND ((First(DateDiff("m",[frmdate],[examdate])))>=3))
    ORDER BY tblDemographics.pid DESC;


    Rus

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Access-SQL programming question

    How about several unions each union with one of your recode fields i.e.

    SELECT DISTINCT IIf([The Value] Between 3 And 8.99,"1"," ") AS Six
    FROM tblDemographics INNER JOIN vw_HFS_SIPS ON tblDemographics.id_num = vw_HFS_SIPS.ID
    GROUP BY tblDemographics.lname, tblDemographics.pid, vw_HFS_SIPS.ID, tblDemographics.frmdate, vw_HFS_SIPS.EXAMTYPE
    HAVING (((tblDemographics.pid)=11 Or (tblDemographics.pid)=12 Or (tblDemographics.pid)=13) AND ((First(DateDiff("m",[frmdate],[examdate])))>=3))
    UNION
    SELECT DISTINCT IIf([The Value] Between 9 And 14.99,"1"," ") AS OneYear FROM tblDemographics INNER JOIN vw_HFS_SIPS ON tblDemographics.id_num = vw_HFS_SIPS.ID
    GROUP BY tblDemographics.lname, tblDemographics.pid, vw_HFS_SIPS.ID, tblDemographics.frmdate, vw_HFS_SIPS.EXAMTYPE
    HAVING (((tblDemographics.pid)=11 Or (tblDemographics.pid)=12 Or (tblDemographics.pid)=13) AND ((First(DateDiff("m",[frmdate],[examdate])))>=3))
    .
    .
    .
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Talking Re: Access-SQL programming question

    Sorry its so belated but thanks!!!!


    Originally posted by SCIROCCO
    How about several unions each union with one of your recode fields i.e.

    SELECT DISTINCT IIf([The Value] Between 3 And 8.99,"1"," ") AS Six
    FROM tblDemographics INNER JOIN vw_HFS_SIPS ON tblDemographics.id_num = vw_HFS_SIPS.ID
    GROUP BY tblDemographics.lname, tblDemographics.pid, vw_HFS_SIPS.ID, tblDemographics.frmdate, vw_HFS_SIPS.EXAMTYPE
    HAVING (((tblDemographics.pid)=11 Or (tblDemographics.pid)=12 Or (tblDemographics.pid)=13) AND ((First(DateDiff("m",[frmdate],[examdate])))>=3))
    UNION
    SELECT DISTINCT IIf([The Value] Between 9 And 14.99,"1"," ") AS OneYear FROM tblDemographics INNER JOIN vw_HFS_SIPS ON tblDemographics.id_num = vw_HFS_SIPS.ID
    GROUP BY tblDemographics.lname, tblDemographics.pid, vw_HFS_SIPS.ID, tblDemographics.frmdate, vw_HFS_SIPS.EXAMTYPE
    HAVING (((tblDemographics.pid)=11 Or (tblDemographics.pid)=12 Or (tblDemographics.pid)=13) AND ((First(DateDiff("m",[frmdate],[examdate])))>=3))
    .
    .
    .

Posting Permissions

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