Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with SQL Statement!! (2000)

    Hi,
    I've attached an Image of an SQL Query that is in a database.
    This is the SQL statement:
    SELECT tblMedia.MedID, tblMedia.MedIntID, "Movement details for: " & [MtpName] & " " & [IntURN] & " - " & Format([MedCopyNo],"000") AS MedDescription
    FROM tblMediaTypes INNER JOIN (tblInterviews INNER JOIN tblMedia ON tblInterviews.IntID = tblMedia.MedIntID) ON tblMediaTypes.MtpID = tblMedia.MedMtpID;
    .

    What I'd like to do is with the MedDescription: "Movement details for: " & [MtpName] & " " & [IntURN] & " - " & Format([MedCopyNo],"000"), I'd like to add the first letters of the fields Forename & Surname in the table TblInterviews. So for someone called Joe Mann, the MedDescription would be JM001, JM002 and so on for each MedCopyNo.

    I'm not too sure how to do this.

    I also have another query with the same database, but I'll post that later.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with SQL Statement!! (2000)

    Did this query give what you want ?

    SELECT tblMedia.MedId, tblMedia.MedIntID, tblMediaType.MtpName, "Movement details for: " & [MtpName] & " " & [IntURN] & " - " & Left([Surname],1) & Left([Forename],1) & Format([medcopyno],"000") AS Expr1
    FROM tblMediaType INNER JOIN (tblInterviews INNER JOIN tblMedia ON tblInterviews.IntID = tblMedia.MedIntID) ON tblMediaType.MtpID = tblMedia.MedMtpID;
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with SQL Statement!! (2000)

    That done the trick, thanks for that.
    My other question (and I'll try posting it here) is I have tblMedia that has the fields MediaID (PK), MedIntID, MedmtpID, and MedCopyNo. All numerical fields apart from MediaID which is a Autonumber.
    This table is then displayed on a form. Similar to the previous post I'd like the MedCopyNo to have the initials of Surname and Forename from TblInterviews. What is the best way to do this? I'd ideally like this value displayed in the actual table but I'm unsure of how to get the data from the tblInterviews to tblMedia.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with SQL Statement!! (2000)

    I suppose the table is displayed in a subform. Use a query as the record source for that subform and include the expression in the previous post to that query.
    Francois

Posting Permissions

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