I would like to find out whether theres an SQL function that combines two fields (in the same table) and processes them as one? So for example, I have two fields - cake & sweets.
I would like to combine the values contained within those fields. So my SQL function would output both values as one; ‘cake_sweets’.
Is this possible - could it be done by combining the two fields and outputting them as an ‘AS’?
I’ve tried the following SQL query (part of it taken from the help you guys were nice enough to give me) but it’s throwing up an error, pointing to the ‘by’ (highlighted in bold - right at the end of my SELECT line):-
strSQL = "SELECT sculptures.ID, sculptures.Name, artist.Firstname, sculptures.condition, sculptures.literature1, sculptures.literature2, sculptures.literature3, sculptures.literature4, sculptures.ImageIndivMain, sculptures.articlenumber, sculptures.circadate, sculptures.Description, artist.Surname, sculptures.artistID, sculptures.weight, sculptures.price, sculptures.depth, sculptures.width, sculptures.height, literature.title & " by " & literature.author AS literaturecombine" & strCRLF & _
" FROM ((sculptures" & strCRLF & _
" INNER JOIN artist ON artist.NameURL = sculptures.artistID)" & strCRLF & _
" INNER JOIN literature ON literature.literaturecombine = sculptures.literaturecombine1)" & strCRLF & _
" INNER JOIN sculpturetype ON sculpturetype.TypeURL = sculptures.sculpturetypeID" & strCRLF & _
" WHERE sculptures.NameURL='" & lngRecord & "'"
Is there anyway I can get this successfully formatted so it works OK
you can’t use double-quotes inside of a double-quoted string. based on your use of str at the beginning of your variable name, i’m assuming you’re using ASP or VB. i don’t know proper quoting technique for VB and ASP, but i know you can’t use double quotes inside a double-quoted string.
Yes I am using ASP. Would you suggest I try another SQL string format (one which doesn’t include the double quotes)?
EDIT: I’ve tried running my query directly in Access and it’s showing an error - it’s saying that the join expression is not supported. I’m guessing that it’s referring to the field, literaturecombine which is declared immediatly after the ON clause. I think that it may be because I need to declare it as literature.literaturecombine . I’ve tried that but it then says that the format of my ON clause is wrong. Any ideas on what I’m doing wrong?
SELECT sculptures.ID, sculptures.Name, artist.Firstname, sculptures.condition, sculptures.literaturecombine1, sculptures.literaturecombine2, sculptures.literaturecombine3, sculptures.literaturecombine4, sculptures.ImageIndivMain, sculptures.articlenumber, sculptures.circadate, sculptures.Description, artist.Surname, sculptures.artistID, sculptures.weight, sculptures.price, sculptures.depth, sculptures.width, sculptures.height, literature.title & " by " & literature.author AS literaturecombine
FROM ((sculptures
INNER JOIN artist ON artist.NameURL = sculptures.artistID)
INNER JOIN literature ON literaturecombine = sculptures.literaturecombine1)
INNER JOIN sculpturetype ON sculpturetype.TypeURL = sculptures.sculpturetypeID
WHERE sculptures.NameURL='Tennis-Player'
Sorry for the delay in replying - some idiotic builders cut through my phone cable so no internet access for most of the day!
Thanks R937 for your invaluable help on this and past issues!
My complete database structure is below:-
literature.ID (primary key) – Autonumber
literature.author – Text
literature.title – Text
sculptures.ID (primary key) – Autonumber
sculptures.Name – Memo
sculptures.LiteratureCombine1 – Text
sculptures.LiteratureCombine2 – Text
sculptures.ArtistID (foreign key to Artist.NameURL) – Text
sculptures.sculpturetypeID (foreign key to sculpturetype.TypeURL) – Text
artist.FirstName – Text
artist.Surname – Text
artist.NameURL (primary key) – Text
images.ID (primary key) – Autonumber
images.ImgURL – Text
images.sculpturesID (foreign key to sculptures.ID) – Number
You’ll notice that my ‘literature’ table isn’t joined to another within Access as it is joined by concatenating the two fields, literature.title and literature.author by SQL within an Access drop down box (not an ideal way of doing it, but that’s what the client asked for) - this drop down box populates the fields sculptures.LiteratureCombine1 and sculptures.LiteratureCombine2 within Access.
So basically by concatenating the two fields within my SQL string within my ASP statement, the result gives me the same value as the sculptures.LiteratureCombine1 field - these two fields I want to join together in one of the JOIN clauses.
I think I’m a bit out of my depth as to what I’m doing but I’d like to think of myself as a fast learner and would be more than happy for someone to tell me where I’m going wrong!
Again, thanks Sitepoint members for helping me out on this matter, especially r937
You should know me by now, r937!. In examples that I don’t understand, I like to take them out of context, so that I can apply them to the certain situation that I’m wanting to use it in. I feel this gives me a better understanding of what’s actually going on within the example.
Well, in the SELECT clause, ‘literature.title & " by " & literature.author AS literaturecombine’ successfully bides both fields together to make one- e.g if the literature.author field had a value of ‘dickens’ and the literature.title had a value of ‘famous five’ both in the same record, the result from the concatenate function would be ‘famous five by dickens’.
In my drop down menu in my form in Access I am using exactly the same concatenating function to output the same string (combines the two fields together) into the sculptures.literaturecombine1 field. So that it outputs the same result, ‘famous five by dickens’. This is where I want to JOIN literature ON literaturecombine (created by concatenating function in SQL string) = sculptures.literaturecombine1 (created by concatenating function in Access drop down menu)
Alternatively, like you suggested in your last post, could I do something in my JOIN statement like;
JOIN literature ON literature.author AND literature.title = sculptures.literaturecombine1
The way I see it- this would ultimately give me the same result that I am looking for, but not having to use the extra field created by my concatenating function in my AS clause, ‘literaturecombine’
Would this work?
EDIT - Sorry yes I did mean literature.title combine with literature.author = sculptures.literaturecombine1
Hope this makes sense, and thanks again for all the help
