Compile stops at db.OpenRecordset (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
No errors in the attempt, per se, but it was asking for a PERSON_ID which, it turns out also is text (why did they create IDs that are text and not numeric? ... anyway)
Hi. We'll just keep trying...
Code:
SELECT PUBLICATION_ID, SIMPLECSV("SELECT PERSON_LASTNAME & " " & PERSON_FIRSTNAME 
FROM PUB_PERSON
INNER JOIN PUB_CONTRIBUTOR
ON PUB_PERSON.PERSON_ID=PUB_CONTRIBUTOR.PERSON_ID
WHERE CONTRIBUTOR_ID='" & [CONTRIBUTOR_ID] & "'") AS AUTHORS
FROM PUB_PUBLICATION_CONTRIBUTOR
WHERE PUBLICATION_ID="00001620"
I am assuming CONTRIBUTOR_ID is TEXT as well.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
Yes, it is.

Just a sec
If it doesn't work, I would really suggest posting a sample copy of your db with test data only, so we can get to a solution faster.
 

jrickards

Registered User.
Local time
Today, 07:26
Joined
Jun 19, 2019
Messages
35
No, there's a syntax error and I'm wondering if there might be a problem with the quotation marks around the text.

I dropped the joining of last and first names and got past that error with the following code but still another error.

Code:
SELECT
PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID, SIMPLECSV("SELECT PERSON_LASTNAME 
FROM PUB_PERSON
INNER JOIN PUB_CONTRIBUTOR
ON PUB_PERSON.PERSON_ID=PUB_CONTRIBUTOR.PERSON_ID
WHERE PUB_PUBLICATION_CONTRIBUTOR.CONTRIBUTOR_ID='" & [PUB_CONTRIBUTOR.CONTRIBUTOR_ID] & "'") AS AUTHORS

FROM (PUB_PUBLICATION_CONTRIBUTOR INNER JOIN PUB_CONTRIBUTOR ON PUB_PUBLICATION_CONTRIBUTOR.CONTRIBUTOR_ID = PUB_CONTRIBUTOR.CONTRIBUTOR_ID) INNER JOIN PUB_PERSON ON PUB_CONTRIBUTOR.PERSON_ID = PUB_PERSON.PERSON_ID

WHERE (((PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID)="00001620"));

Here's a screenshot of the error.

error6.jpg

The following code lists the 3 authors in 3 rows, we're hoping to use your script to bundle them in one row.

Code:
SELECT PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID, PUB_PERSON.PERSON_LASTNAME
FROM (PUB_PUBLICATION_CONTRIBUTOR INNER JOIN PUB_CONTRIBUTOR ON PUB_PUBLICATION_CONTRIBUTOR.CONTRIBUTOR_ID = PUB_CONTRIBUTOR.CONTRIBUTOR_ID) INNER JOIN PUB_PERSON ON PUB_CONTRIBUTOR.PERSON_ID = PUB_PERSON.PERSON_ID
WHERE (((PUB_PUBLICATION_CONTRIBUTOR.PUBLICATION_ID)="00001620"));

So, how do I post/share the db?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
So, how do I post/share the db?
There is a size limit but try compacting and repairing it, then zip it up, and then attach it to your post here. To attach a file to your post, click on the Manage Attachment button.
 

jrickards

Registered User.
Local time
Today, 07:26
Joined
Jun 19, 2019
Messages
35
Oooops, didn't compact and repair but just zipping it was sufficient to bring it down to the right size.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
Here's the db

The data is public, I'm not concerned about sharing real data.

View attachment 75327
Hi. Thanks. I'll work on it some more; but in the meantime, try running the following query in the sample file you posted.
Code:
SELECT TOP 1 Authors_SimpleCSV.PUBLICATION_ID, SimpleCSV("SELECT FullName FROM Authors_SimpleCSV") AS Authors
FROM Authors_SimpleCSV;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
Hah! Absolutely perfect!!
Hi. Okay, here's what I came up with. Please let me know if this is okay:
Code:
SELECT PUB_PUBLICATION.PUBLICATION_ID,
  SimpleCSV("SELECT PERSON_LASTNAME & ' ' & PERSON_FIRSTNAME 
    FROM PUB_PERSON 
    INNER JOIN (PUB_CONTRIBUTOR 
    INNER JOIN  PUB_PUBLICATION_CONTRIBUTOR 
        ON PUB_CONTRIBUTOR.CONTRIBUTOR_ID=PUB_PUBLICATION_CONTRIBUTOR.CONTRIBUTOR_ID) 
        ON PUB_PERSON.PERSON_ID=PUB_CONTRIBUTOR.PERSON_ID 
    WHERE PUBLICATION_ID='" & [PUBLICATION_ID] & "'") AS Authors
FROM PUB_PUBLICATION
WHERE (((PUB_PUBLICATION.PUBLICATION_ID)="00001620"));
 

jrickards

Registered User.
Local time
Today, 07:26
Joined
Jun 19, 2019
Messages
35
That's it! Wow, that was quite the effort and I'd be amiss if I didn't attribute 99.99% of the effort to you.

Many, many thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
That's it! Wow, that was quite the effort and I'd be amiss if I didn't attribute 99.99% of the effort to you.

Many, many thanks.
Hi. You're very welcome. Glad we got there in the end. Having the sample db helped a lot in figuring it out sooner. Thanks for that. Good luck with your project.
 

jrickards

Registered User.
Local time
Today, 07:26
Joined
Jun 19, 2019
Messages
35
There was just one minor change I had to make to the script, I don't know if you want to fold it into your web page but I wanted ", " between names, not just the "," so I modified the separator to include the space and tested it, it worked fine.
 

jrickards

Registered User.
Local time
Today, 07:26
Joined
Jun 19, 2019
Messages
35
The other script that I tried to make work had an interesting option (that I wasn't going to use) but the last separator was " and " instead of ", " so the results might be "author1, author2 and author3".

Like I said, that wasn't an option I needed now but I guess the script author thought that it might be useful in some cases.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:26
Joined
Apr 27, 2015
Messages
6,286
I have been following this thread from the get-go. Jrickards, you need to send DBG a box of cigars and/or case of scotch. He really stuck it out and got you to the finish line!

I know you know this and I was just kidding about the stogies and booze, but please do give his reputation a boost for his efforts.

DBG, excellent job with sticking with it. It is this type of effort that makes AWF the cream of the crop...
 

jrickards

Registered User.
Local time
Today, 07:26
Joined
Jun 19, 2019
Messages
35
I have been following this thread from the get-go. Jrickards, you need to send DBG a box of cigars and/or case of scotch. He really stuck it out and got you to the finish line!

I know you know this and I was just kidding about the stogies and booze, but please do give his reputation a boost for his efforts.

DBG, excellent job with sticking with it. It is this type of effort that makes AWF the cream of the crop...

You're absolutely right, he deserves more than just a thanks. And, sticking to it, me, a newbie (with some basic knowledge, enough to make me dangerous), I couldn't believe that he was still stuck on the scent until the rabbit was found!

How do I give his reputation a boost for this?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:26
Joined
Apr 27, 2015
Messages
6,286
Under one of his posts, click on the scales icon, make sure you check “approve” and then type something nice. That last bit is optional but encouraged! Welcome to AWF
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
There was just one minor change I had to make to the script, I don't know if you want to fold it into your web page but I wanted ", " between names, not just the "," so I modified the separator to include the space and tested it, it worked fine.
Hi. For the delimiters, it's an optional argument of the function. Did you see it? If you wanted a comma and a space between the combined records, then you supply the second argument, which could take almost anything you want to use. For example:
Code:
SimpleCSV("SELECT...", ", ")
or
SimpleCSV("SELECT...", "; ") 'usually used for email addresses
or
SimpleCSV("SELECT...", Chr(13) & Chr(10)) 'usually used to display a history of comments or notes
or
SimpleCSV("SELECT...", "' OR [FieldName]='") 'could be used to build multiple OR criteria for a query

etc...
Cheers!
 

jrickards

Registered User.
Local time
Today, 07:26
Joined
Jun 19, 2019
Messages
35
I used to do a bit of basic SQL 15yrs ago but INNER JOINs were always my stumbling block: I understood what they did, but I could never get the coding right. And, of course, anything more than basic functions and my scripting knowledge and experience failed.

@theDBGuy, thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,358
I used to do a bit of basic SQL 15yrs ago but INNER JOINs were always my stumbling block: I understood what they did, but I could never get the coding right. And, of course, anything more than basic functions and my scripting knowledge and experience failed.

@theDBGuy, thanks again
Hi. We're all happy to assist here. Cheers!
 

Users who are viewing this thread

Top Bottom