Compile stops at db.OpenRecordset (1 Viewer)

[theDBguy]

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...

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.

 

[jrickards]

Yes, it is.

Just a sec

 

[theDBguy]

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]

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.

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.



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

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]

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]

Here's the db

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

View attachment SimpleCSV.zip

 

[jrickards]

Oooops, didn't compact and repair but just zipping it was sufficient to bring it down to the right size.

 

[theDBguy]

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.

SELECT TOP 1 Authors_SimpleCSV.PUBLICATION_ID, SimpleCSV("SELECT FullName FROM Authors_SimpleCSV") AS Authors
FROM Authors_SimpleCSV;

 

[jrickards]

Hah! Absolutely perfect!!

 

[theDBguy]

Hah! Absolutely perfect!!

Hi. Okay, here's what I came up with. Please let me know if this is okay:

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]

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]

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]

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]

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]

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]

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]

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]

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:

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]

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 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!