Page 1 of 1

Help With SQL Code and Access Database

PostPosted: Wed Dec 19, 2012 12:50 pm
by Dawn
What I’m trying to do is get the field from the Audits table named AuditID to automatically carry over into the Audits Details table in the AuditID field


Name of Access Database: AUDITS
1st Table Name: Audits with the fields:
AuditID (Primary Key)
PlantLocation
Shift
Area
AuditDate
AuditTime

2nd Table: AuditDetails with the fields
DetailID (Primary key)
AuditID
RiskFactor
BCFactor
BCDesc
ESResolution


Below is the code:

"AUDITS" "UPDATE* "AuditDetails" INNER JOIN "Audits" ON AuditDetails.AuditID = Audits.AuditID Set AuditDetails.AuditID
= Audits.AuditID, AuditDetails.sAuditID = Audits.AuditID WHERE Audits.AuditID = "[AuditID]"

Re: Help With SQL Code and Access Database

PostPosted: Thu Dec 20, 2012 11:34 am
by Neosoft Support
Have you tried executing those SQL commands with DBPro's dbpExecSQL action?

What type of field is AuditID?

Re: Help With SQL Code and Access Database

PostPosted: Thu Dec 20, 2012 1:50 pm
by Dawn
I have tried to use that but I keep getting a missing quotations error. The AuditID field is the primarykey in the table named Audits.

Re: Help With SQL Code and Access Database

PostPosted: Thu Dec 20, 2012 5:39 pm
by Neosoft Support
Can you post the dbpExecSQL code you're using?

Re: Help With SQL Code and Access Database

PostPosted: Sat Dec 22, 2012 8:06 am
by Dawn
"AUDITS" "UPDATE* "AuditDetails" INNER JOIN "Audits" ON AuditDetails.AuditID = Audits.AuditID Set AuditDetails.AuditID
= Audits.AuditID, AuditDetails.sAuditID = Audits.AuditID WHERE Audits.AuditID = "[AuditID]"

Re: Help With SQL Code and Access Database

PostPosted: Wed Dec 26, 2012 2:27 pm
by Neosoft Support
I'm not a SQL expert and some of those commands can be pretty difficult to get right. However, since double quotes are used as delimiters for NeoBook's scripting language, you can't use them in your SQL commands. Instead of double quotes, try using single quotes. For example:

dbpExecSQL "AddrBook" "SELECT * FROM Contacts WHERE State = 'NY'" ""

Re: Help With SQL Code and Access Database

PostPosted: Thu Jan 10, 2013 8:23 am
by Dawn
Looks as though I'm stuck in neutral on this! I can't get anything to work.

Re: Help With SQL Code and Access Database

PostPosted: Thu Jan 10, 2013 2:10 pm
by Neosoft Support
You could manually populate the AuditDetails table. For example:

SetVar "[Audit.AuditDetails.DetailID]" "[Audit.Audit.AuditID]"

This will copy the contents of the Audit table's current record AuditID field to the AuditDetail table's current record DetailID field.

I'm sure it's possible to get your SQL code to work also, but without actually having access to your database, pub file, etc. it's difficult for us to make any specific suggestions.