I have the following sql that returns my sample records:
SELECT QryAllEnrolments.[Person Id], QryAllEnrolments.[Course Code], QryAllEnrolments.[Course Name], QryAllEnrolments.GLH, QryAllEnrolments.Date
FROM QryAllEnrolments LEFT JOIN QryWithdrawls ON (QryAllEnrolments.[Person Id] = QryWithdrawls.[Person Id]) AND (QryAllEnrolments.[Course Code] = QryWithdrawls.[Course Code])
WHERE (((QryAllEnrolments.[Person Id])=1950165) AND ((QryWithdrawls.[Person Id]) Is Null));
and wrote the following hoping it would return a running sum of the guided learning hours (GLH) utilising the above:
SELECT Pro.[Date], Pro.[Person Id], Pro.[Course Code], Pro.[Course Name], Pro.[GLH],
(Select Sum(Pro1.[GLH] FROM [QryAllEnrolments Without Matching QryWithdrawls] Pro1 WHERE Pro1.[Date]<=Pro.[Date]) AS [Running Total], Pro.[Date]
FROM [QryAllEnrolments Without Matching QryWithdrawls] AS Pro
ORDER BY Pro.[Date]
It squeels of a syntax error at the inner select sum clause.
your assistance to finish this off would be greatly appreciated.
I get a syntax error when I run this query from an Access Module but I don't get an error when I run it as a straight query. Can anyone see what's wrong with it? I am using Access 2003
Dim sqlStmt As String
sqlStmt = "SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight " sqlStmt = sqlStmt + "FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight " sqlStmt = sqlStmt + "FROM CCCWords " sqlStmt = sqlStmt + "WHERE Word IN ( Select word from CBCWords where vendor = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO,COUNT(*) * 25 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPostalCode " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "]. AS dupWeight " sqlStmt = sqlStmt + "WHERE dupWeight.ESTBLMT_NO = [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "GROUP BY [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "HAVING SUM(subWeight) >= 60 " sqlStmt = sqlStmt + "ORDER BY SUM(subWeight) DESC"
DoCmd.OpenForm "Show Probabilities", , , sqlStmt
Here is the code that runs ok in a straight query:
SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight FROM CCCWords WHERE Word IN ( Select word from CBCWords where vendor = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 25 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPostalCode WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO ]. AS dupWeight WHERE dupWeight.ESTBLMT_NO=[CCC Companies].ESTBLMT_NO GROUP BY [CCC Companies].ESTBLMT_NO HAVING SUM(subWeight)>=60 ORDER BY SUM(subWeight) DESC;
I am really stuck. I have spent two days searcinh different forums trying to solve my problem. I am trying to create an UPDATE q to my Access database. But I get either the: "Syntax error in query. Incomplete query clause" or "Syntax error in UPDATE query".
First of all here's the URL: www.innotec-as.no/login/Kunder Login U/P either: "alfen" or "thomas".
The page opening up shows the user info, U/P and adress. viewing the information is working perfectly - but editing it..no way.
When editing and submiting the data the above errors occour. Try that and you'll also see the SQL I am trying to execute. The CODE is as follows:
I keep on getting a syntax error on the first [MU_ID]. Can anyone tell me what am I doing wrong please? You can e-mail me at Frank.Cappas@CIGNA.Com
Site: IF [MU_ID] BETWEEN ((SELECT [MU_Start] FROM [Sites] WHERE [Site] = “BRB”) AND (SELECT [MU_End] FROM [Sites] WHERE [Site] = “BRB”), “BRB”, {IF [MU_ID] BETWEEN ((SELECT [MU_Start] FROM [Sites] WHERE [Site] = “BRI”) AND (SELECT [MU_End] FROM [Sites] WHERE [Site] = “BRI”},"BRI")
I'm trying to get my head round this query and not having much luck.
I have table that contains. amongst others, two fields I need to use - Date and Amount. I already have a query that returns me the number of entries for a given month, along with the total value.
SELECT Count(*) AS [Number of Entries], Sum(NBReferral.[Amount]) AS [Total Amount], NBReferral.Date FROM NBReferral GROUP BY NBReferral.Date;
What I want is to also provide a running total (year to date). Using this:
SELECT Count(*) AS [YTD Number of Entries], Sum(NBReferral.[Amtount]) AS [YTD Total Amount] FROM NBReferral;
Combing the two queries works as long as there is only one month. Unfortunately, I'd like to see each month's YTD figure displayed as shown
Date Number of Entries Total Amount YTD Number of Entries YTD Total Amount Nov 2006 5 10000 5 10000 Dec 2006 3 5000 8 15000 Jan 2007 6 12000 14 27000 etc.
What I currently get, as you would expect, are the same YTD totals applied to each month:
Date Number of Entries Total Amount YTD Number of Entries YTD Total Amount Nov 2006 5 10000 14 27000 Dec 2006 3 5000 14 27000 Jan 2007 6 12000 14 27000 etc.
Probably a fairly straight-forward query for someone with a bit more experience. Any ideas?
I need to make one modification to it, but I am getting lost in the syntax. Right now this code is matching the text string "txtsearch" to anything in the "Engine Name" Field.
It works fine, but I need it to also look in the "type" field. I need results if the string is in "Engine Name" OR if it is in "Type"
I want to run a query that allows the user to enter the beginning date and the ending date to produce the results for all items within those dates. I've written this before using "Between", but I can't get the syntax correct.
I've become aware that if I create a variable in the select statement like this in Access:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum
That I cannot reliably use said variable later in the same statement:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum FROM Table1 GROUP BY Table1.ID ORDER BY TotSum DESC;
It will ask me to "enter a parameter value" for TotSum. When I do, in this example, it still sorts in correctly, but in a larger more detailed query it gets a little confused. I instead have to re-use the equation like this:
SELECT Table1.ID, Sum([A]+[B]+[C]) AS TotSum FROM Table1 GROUP BY Table1.ID ORDER BY Sum([A]+[B]+[C]) DESC;
Is there a way around this? It seems inefficient to recompute the sum 2 times where I think I only need to do it once.
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
I'm new to Access and have been learning how to use it for the better part of a year. I've done all the Microsoft online tutorials relevant to the work I need to do with it, and gotten a few books out of the library besides. In general, I self-teach very well given a good resource.
Here's my problem: the main area in which I need to be proficient is running queries, and I cannot find a good, comprehensive explanation of how to construct expressions or set up calculated fields.
In case I'm not clear (I've had quite a time just figuring out what to call what I need), I'll give you an example. I was able to arrange a short tutorial with someone in another department. As part of a query, she used the following statement in the "Field" section to convert date information stored as mm/dd/yyyy into just the year:
Year: IIf([referraldate]<#1/1/2003#,"2002",IIf([referraldate] Between #12/31/2002# And #1/1/2004#,"2003",IIf([referraldate] Between #12/31/2003# And #1/1/2005#,"2004",IIf([referraldate] Between #12/31/2004# And #1/1/2006#,"2005",IIf([referraldate] Between #12/31/2005# And #1/1/2007#,"2006")))))
This is the kind of thing I want to learn how to do. Unfortunately, the Microsoft tutorials don't do much more that give examples of different expressions and functions; I feel like I've been given a handful of sample sentences, a few nouns, and a few verbs--and then told to go speak English.
What I need is a comprehensive guide that not only gives me the building blocks of expressions, but tells me how to combine them into a syntactically meaningful statement--so I know what order things go in, where commas and parentheses should be, etc. Both online or print materials are fine--I've been looking on my own, but with no luck.
I have a select query with a number of expressions and I cannot seem to get the expressions to work. Access keeps giving me syntax error on the following code:
SELECT Run.Test_Case,
Sum(IIf(DateValue([Attempted_Actual]) < DateValue([Attempted_Actual]),[Points],0))/Sum(IIf(Not IsNull([Attempted_Actual]),[Points],0))*100 AS ActualAttempted, Sum(IIf(DateValue([Completed_Actual]) < DateValue([Completed_Actual]),[Points],0))/Sum(IIf(Not IsNull([Completed_Actual]),[Points],0))*100 AS ActualCompleted, Sum(IIf(DateValue([Verified_Actual]) < DateValue([Verified_Actual]),[Points],0))/Sum(IIf(Not IsNull([Verified_Actual]),[Points],0))*100 AS ActualClosed,
Sum(IIf(DateValue([Attempted_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Attempted_Planned]),[Points],0))*100 AS PlannedAttempted, Sum(IIf(DateValue([Completed_Planned]) < DateValue(Date()]),[Points],0))/Sum(IIf(Not IsNull([Completed_Planned]),[Points],0))*100 AS PlannedCompleted, Sum(IIf(DateValue([Verified_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Verified_Planned]),[Points],0))*100 AS PlannedClosed
FROM Run INNER JOIN Task ON Run.Run=Task.Group GROUP BY Run.Test_Case; HAVING (((Run.Test_Case)=IIf(IsNull([Forms]![Status]![ComboStatusTestCase]),[Test_Case],[Forms]![Status]![ComboStatusTestCase])));
I am sort of confused on where the error could because it just says there is a syntax error. My assumption is that I am using DateValue() in the wrong context. If anyone has any pointers or sees my error please let me know.
Can someone tell me where I might be going wrong here. The following query works in SQL, but somewhere in the LEFT JOINS area, Access gets a little confused and says I'm missing an operator.
SELECT Tariffs.TariffCPUCID, Tariffs.TariffID, AdviceLetters.ALCPUCID, Tariffs.ALID, Tariffs.ScheduleID, Schedules.SheetTitle, AdviceLetters.[Filing Date], Tariffs.[C&E], SheetsCancelling.CancellingID, SheetsCancelling.CancellingCPUCID FROM Tariffs LEFT JOIN Schedules ON Tariffs.ScheduleID = Schedules.ScheduleID LEFT JOIN AdviceLetters ON Tariffs.ALID = AdviceLetters.ALID LEFT JOIN SheetsCancelling ON SheetsCancelling.TariffID = Tariffs.TariffID WHERE Tariffs.Type="E" ORDER BY Tariffs.TariffCPUCID DESC;
Hello, first time posting, not sure what info you all need. I'm looking for some help with an if/then statement in access query involving dates. Below is the query I am trying to use, along with the error message. Any ideas?
Domestic Violence CME Required?: IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")
syntax error (comma) in query expression 'IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")'
The error seems to be in relationship to the number 6. I want a "yes" to show up in the field if the difference is greater then 6 years.
Hello I'm having trouble getting my nested Iif statement to run. Can anyone help??? I've attached a screen shot of the syntax error that I'm receiving. The example code below needs tweaking.
Update [Goodrec-copy3] set [Goodrec-copy3].shortname = Iif (Not Null([shortname]),[Goodrec-copy3].shortname Like "*,JR*" Or ([Goodrec-copy3].[shortname] Like "*, SR*" Or ([Goodrec-copy3].[shortname]) Like "*, II*" Or [Goodrec-copy3].[shortname]) Like "*, III*", InStrRev([shortname])," ",InStr([shortname]," ,")+1,50) &" "& Left([shortname],InStrRev([shortname])," ,")-1) WHERE ((([Goodrec-copy3].[ctype])="I"));
What I'm attempting to accomplish with this query is to keep the field shortname the same if not null and if it doesn't meet the criteria of having a string value of "JR", "SR", "II", or "III". If the field does have a string value of "JR", "SR", "II", or "III" reverse the string (example John Gissom JR) to reflect shortname as such for example: "Gissom JR John".
why this query comes back as invalid syntax?I am attempting to find all records older than two quarters, Month of Contact actually displays the date as ##/##/####
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
Morning all The problem whihc i have is that, when trying to open the form "Create Timetable", through the switch board.... a message box appears saying "Syntax error (missing opertor) in query exprssion" Any ideas on why it is showing this message box??? Help would be appricated Thank you
After adding a radio type field in the form called Blocco (with yes or no values, working like the existing field "Bozza") I've modified the code adding something to update the db:
The page itself is loaded with no errors but when the form takes action I get this: Syntax error (missing operator) in query expression 'True Articoli.Bozza = False'.
why? I've just used the same code that worked with the field "Bozza" (which is a checkbox field too)
Access and receive the following message after trying to run a query: Syntax error (comma) in query expression, followed by the formula I wrote on the Query Builder. I use 4 tables to run the query, but only need to trim some stuff from one of them. This table is called BD_lamosa_corregida and have already selected Expression on the Total row in Design View. This is the formula:
The formula is trying to trim the left side of an expression (Part description) which may contain a code number in parenthesis or not, it might also have this parenthesis separated by a space or not.
