Professional Web Applications Themes

Access Query - Coldfusion Database Access

Hi I have written sql query in access database... SELECT email_info.* FROM email_info WHERE email_id='johntest.com.au' AND email_date + 7 < date(); I want to put MAX(email_date) in email_date field for same id as [email]johntest.com.au[/email] sth like first it finds max (email_date) for john and then it executes 2nd query... I dont wanna execute separate sql statements... any ideas how to achieve this.. thanx dave...

Sponsored Links
  1. #1

    Default access query

    Hi
    I have written sql query in access database...

    SELECT email_info.*
    FROM email_info WHERE email_id='johntest.com.au' AND
    email_date + 7 < date();

    I want to put MAX(email_date) in email_date field for
    same id as [email]johntest.com.au[/email]

    sth like first it finds max (email_date) for john and
    then it executes 2nd query...

    I dont wanna execute separate sql statements...

    any ideas how to achieve this..
    thanx
    dave
    Sponsored Links
    dave Guest

  2. #2

    Default Re: access query

    You need to tell us how the two queries are related to each other. You also
    need to use a spell checker.

    Cheers
    Ken


    "dave" <anonymousdiscussions.microsoft.com> wrote in message
    news:2de5e01c46ae1$97c1c1a0$a401280aphx.gbl...
    > Hi
    > I have written sql query in access database...
    >
    > SELECT email_info.*
    > FROM email_info WHERE email_id='johntest.com.au' AND
    > email_date + 7 < date();
    >
    > I want to put MAX(email_date) in email_date field for
    > same id as [email]johntest.com.au[/email]
    >
    > sth like first it finds max (email_date) for john and
    > then it executes 2nd query...
    >
    > I dont wanna execute separate sql statements...
    >
    > any ideas how to achieve this..
    > thanx
    > dave

    Ken Schaefer Guest

  3. #3

    Default Re: access query

    <<
    Hi
    I have written sql query in access database...

    SELECT email_info.*
    FROM email_info WHERE email_id='johntest.com.au' AND
    email_date + 7 < date();

    I want to put MAX(email_date) in email_date field for
    same id as [email]johntest.com.au[/email]

    sth like first it finds max (email_date) for john and
    then it executes 2nd query...

    I dont wanna execute separate sql statements...

    any ideas how to achieve this..
    thanx
    dave
    >>
    Example of one query (QueryB) based on the results of another query
    (QueryA):

    QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

    QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" &
    QueryA & ")"

    But the following is even faster and allows for more than one field to
    be returned in QueryA:

    QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS
    tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

    So QueryA would include all the CustID's for customers starting with A.

    And QueryB would include more fields in the customers table (i.e. not
    just the CustID field) for the records returned in QueryA (which was the
    customers starting with A).

    I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build
    from the tblSQLA on the left to other tables that have fields you want
    to return.

    Best regards,
    J. Paul Schmidt, Freelance ASP Web Developer
    [url]http://www.Bullschmidt.com[/url]
    Classic ASP Design Tips, ASP Web Database Demo, ASP Bar Chart Tool...


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Bullschmidt Guest

  4. #4

    Default Access Query

    I am having a problem with a report trying sum two fields. I have 2 total
    fields and I want to add those together for another total.
    Instead of it adding the two fields together it concatenates the totals, for
    example, if field A = 2, and field B=7, the result is 27 instead of 9.
    =Sum([Shirt Query]![XL #48734]+[Shirt Query]![TotalSS-XL])
    Is my control source expression, any suggestions on how to make it add? Thanks.

    rmorgan Guest

  5. #5

    Default Access Query

    Hi, I am trying to write a delete query in Access, but not sure where to start.

    I have two tables, request and week1, that share a field, "id".

    I want to keep all of the records in request, but delete any records in week1
    that do not have an id that is also present in request.

    Please help! Thanks.

    semi Guest

  6. #6

    Default Re: Access Query

    One way would be something like this:

    DELETE FROM week1 w
    WHERE NOT EXISTS (SELECT 1
    FROM request r
    WHERE r.id = w.id)

    Phil
    paross1 Guest

Similar Threads

  1. Access 2003 query not same as in CF
    By dtubbs in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 21st, 06:19 PM
  2. MS Access query in MX 7
    By Eric Hung in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 19th, 03:31 AM
  3. Access query help
    By Brandon Taylor in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 12th, 09:52 PM
  4. Access Query in ASP
    By Ambassador in forum ASP Database
    Replies: 2
    Last Post: March 2nd, 06:08 AM
  5. MS access SQL query help!
    By Ralph Sharett in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 28th, 12:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139