Selasa, 13 Desember 2016

Re: [MS_AccessPros] Re: Input Form and Input Template Table Design

 

David-


You can do it in the Immediate Window (CTRL+G) by copying the string and putting a question mark in front of it, then press enter:

?"INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) " & _
            "SELECT " & 5 & ", SamplePointID, ParameterID " & _
            "FROM tblServiceTemplates " & _
            "INNER JOIN tblServiceTemplateDetails " & _
            "ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
            "WHERE tblServiceTemplates.ServiceTemplateID = " & 5 & ";"

The window will then return it all as one string if the syntax is correct (note that I replaced the references to ServiceReportID and ServiceTemplateID with constants to make it work):

INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) SELECT 5, SamplePointID, ParameterID FROM tblServiceTemplates INNER JOIN tblServiceTemplateDetails ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID WHERE tblServiceTemplates.ServiceTemplateID = 5;

You can then copy and paste that into the SQL window of a new query to see if it parses OK.

But I had problems when I went to test using a direct copy and paste from email.  Note that SOME of your double quotes are "smart quotes" - not the simple double-quote character.  Notably the quote at the beginning of every line except the INNER JOIN line and at the end of the first, third, fifth, and sixth lines.  You're also missing a space after the last ampersand.

Fix those quote problems and try again!

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Dec 13, 2016, at 4:29 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John, I have been reading several sites about SQL looking for the source of the syntax error.  I thought i had found the error when one place said the SQL has to end with a semi colon.  I added that and still get the syntax error when I attempt to compile.  Here is my SQL now:
  strSQL = "INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) " & _
            "SELECT " & Me.ServiceReportID & ", SamplePointID, ParameterID " & _
            "FROM tblServiceTemplates " & _
            "INNER JOIN tblServiceTemplateDetails " & _
            "ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
            "WHERE tblServiceTemplates.ServiceTemplateID = " & Me.ServiceTemplateID &";"
 


---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :

I copied your code into the module and I am still getting a "syntax error" code.

I see one thing that you did differently, is that the form controls are not within quotes.  I also see that I had a couple of places where I failed to have a space between concatenated strings.

Someone needs to create a tool that will take strSQL compound strings and strip out the quotes so you can see if the SQL statement actually makes sense or not.  Or does that already exist somewhere?

Anyway, still have the syntax error and the entire block of code with strSQL is highlighted.


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

Try this:

strSQL = "INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID) " & _
            "SELECT " & Me.ServiceReportID & ", SamplePointID, ParameterID " & _
            "FROM tblServiceTemplates " & _
            "INNER JOIN tblServiceTemplateDetails " & _
            "ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
            "WHERE tblServiceTemplates.ServiceTemplateID = " & Me.ServiceTemplateID


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France)




On Dec 12, 2016, at 11:04 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John, I am trying to get the templates to work using the SQL code you provided and I am really struggling (hours).  Since the combination key of CustomerID and EquipmentID is not unique I didn't see how the SQL could work when there was more than one template for Customer/Equipment.  So, to make it easier and better, I placed another field in tblServiceReports - ServiceTemplateID.  So now, each time that a new service report record is created, the user has to select the template which it is based upon.  

Here is the strSQL that I am putting into the AfterUpdate procedure:
strSQL = "INSERT INTO tblServiceReportDetails (ServiceReportID, SamplePointID, ParameterID)" & _
            "SELECT Me.ServiceReportID,SamplePointID,ParameterID" & _
            "FROM tblServiceTemplates" & _
            "INNER JOIN tblServiceTemplateDetails" & _
            "ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID" & _
            "WHERE tblServiceTemplates.ServiceTemplateID = Me.ServiceTemplateID"

When I try to compile, the error "Syntax Error" appears and the entire block of code above is highlighted.  Sometimes the INTO keyword is highlighted and the error "expected end of statement" shows up.

I have spent hours and don't know how to correct it.

Thank you, David

 


---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :

Hi, I am new to the group and this is my first post so hopefully I will get this done in a manner that allows for easy response.
Access 2016

 

I am working on a database to record and report lab data.  I need to create an input form to enter the results of lab tests.  I have these tables:
1.       Table of analyses (many different analytical procedures may be performed)
2.       Table of sample points (many different sample points may be sampled)
3.       Join table for the two tables above since there is a many to many relationship
4.       Table of lab report headers (customerID, sample collection date, and Sample Collector ID)
5.       Table of lab report details (HeaderID, Analysis date, sample point ID, analysis ID, result)

 

Any of the analyses may be performed on any of the samples.  However, I have several sets of samples and analyses that are frequently repeated.  That is, certain sample points are collected every week and the same 6-8 analyses are performed on those samples each time.  I know how to make an input form based on the join table to enter data, but that requires the data entry to be a little time consuming, having to select the sample pointID and analysis ID each time to enter the result.  To simplify data entry for the repeated samples, I would like to make a form for each of those routine sample points that already includes the "normal" analyses so that the only manual data entry required is entering the result. 

 

I have no idea how to start.  Do I need to create another table? Table of " testing templates" or something of that nature?  And if so, what do I include in the table?

 

Is this too vague or too complex a question for this forum?

 

Thank you,
David  







__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (25)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)