I am scratching my head with the required quotes and parentheses.
I started with an existing working Query with Running Total
by date.
Now I need to produce a running total by quarter day, so I converted my
dates in serial increments of 6 hours intervals.
I verified that these serial dates have the expected values.
I run into repeated syntax error in the adapted Query.
'Original working Query taken as sample, where dates are in Date type.
SELECT master2.WBS AS WBSAlias, master2.[Pricing Date] AS DateAlias,
Sum(master2.amo unt) AS [Weekly Amount],
Val(DSum("[amount]","master2" ,"[WBS]= '" & [WBSAlias] & "' And
[Pricing Date]<= #" & [dateAlias] & "#")) AS [Running Sum]
FROM master2
GROUP BY master2.WBS, master2.[Pricing Date]
ORDER BY master2.WBS, master2.[Pricing Date];
'New Query adapted from previous. Now dates are Serial values.
'Syntax error in string in Query expression. Probable cause : Quotes &
Parentheses around GDate ?
SELECT Sc8T.HTRPhase AS WBSAlias, Sc8T.GDate AS DateAlias,
Sum(Sc8T.QAC) AS Amount,
Val(DSum("[QAC]","Sc8T","[HTRPhase]= '" & [WBSAlias] & "' And [GDate]<=
[dateAlias])) AS [Running Sum]
FROM Sc8T
GROUP BY Sc8T.HTRPhase, Sc8T.GDate
ORDER BY Sc8T.HTRPhase, Sc8T.GDate;
Help would be very much appreciated,
Thank you.
J.P. 1 3244
Are you surrounding the value of WSBAlias with single quotes because it is a
text Field? What type of field is DateAlias? Is it absolutely necessary to
keep the Running Sum in the Query? If not, it is dead easy to specify
Running Sum on a Control in a Report when you are presenting the data.
Larry Linson
Microsoft Access MVP
"u473" <u4**@aol.com > wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. . I am scratching my head with the required quotes and parentheses. I started with an existing working Query with Running Total by date.
Now I need to produce a running total by quarter day, so I converted my dates in serial increments of 6 hours intervals. I verified that these serial dates have the expected values.
I run into repeated syntax error in the adapted Query.
'Original working Query taken as sample, where dates are in Date type.
SELECT master2.WBS AS WBSAlias, master2.[Pricing Date] AS DateAlias, Sum(master2.amo unt) AS [Weekly Amount],
Val(DSum("[amount]","master2" ,"[WBS]= '" & [WBSAlias] & "' And [Pricing Date]<= #" & [dateAlias] & "#")) AS [Running Sum]
FROM master2 GROUP BY master2.WBS, master2.[Pricing Date] ORDER BY master2.WBS, master2.[Pricing Date];
'New Query adapted from previous. Now dates are Serial values. 'Syntax error in string in Query expression. Probable cause : Quotes & Parentheses around GDate ?
SELECT Sc8T.HTRPhase AS WBSAlias, Sc8T.GDate AS DateAlias, Sum(Sc8T.QAC) AS Amount,
Val(DSum("[QAC]","Sc8T","[HTRPhase]= '" & [WBSAlias] & "' And [GDate]<= [dateAlias])) AS [Running Sum]
FROM Sc8T GROUP BY Sc8T.HTRPhase, Sc8T.GDate ORDER BY Sc8T.HTRPhase, Sc8T.GDate;
Help would be very much appreciated, Thank you. J.P. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Peter Bailey |
last post by:
I have a query that creates a graph of bookings from the course start date
looking back 20 weeks based on a running sum.
I also have a query that counts the number of bookings before that 20 week
date for that particular course start date.
What they want is to start the 20 week running sum from the previous total.
for ex
|
by: James Conrad StJohn Foreman |
last post by:
Have found
http://www-128.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html
which is quite helpful, but doesn't quite tell me what I want.
I have a table, advertising_spend with two columns, date and spend.
SELECT date, spend, sum(spend) over (order by date) as cumulative_spend
FROM advertising_spend
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'
|
by: Gandalf186 |
last post by:
I need to create a query that produces running totals for every group within my table for example i wish to see: -
Group A
1
5
9
15
Group B
|
by: Jana |
last post by:
Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. There are also several other
subreports in the main report's footer. The rptTrustDetails subreport
has two grouping levels MasterClientID and ClientID, with headers and
footers for each grouping level. In the ClientID footer, I have an...
|
by: Zairay |
last post by:
Hi All,
I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS , " & _
"Biographies.first_name AS ,
Biographies.sonof AS , " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
| |
by: mebrabham |
last post by:
Hello,
I am trying to create a running subtotal in a query and then graph the
running subtotal for each city group. The data looks like this in the
table (for illustration):
My City My Date Cumulative Sum Daily Sum
DC 03-Apr-07 6 6
DC 04-Apr-07 20 14
DC 05-Apr-07 0 0
DC 06-Apr-07 24 4
|
by: JJM0926 |
last post by:
I'm trying to create a running totals query in access 97. I have
followed the directions on how to do it from Microsofts website
article id 138911. I took their code they had and replaced it with my
fields. When I try to run it I get #errors in my RunTot column. I'm
kinda new to this. Not sure if maybe I mistyped something wrong or is
there a better way to do this? I have pasted the code. Any help would
be greatly appreciated....
|
by: dougans |
last post by:
Hey there people,
Need help on this one if you can spare me a second!!!
No idea why, been trying for 2 hours to fix this now and I feel like a complete loser haha.
Anyone have any idea why my UPDATE statements seem to be running 4 times when footer.inc.php is included but when I comment it out the UPDATE statement does not multiply the amount by 4????
It's the craziest thing I've ever seen, and the first ever time I'm seriously...
|
by: sstidham |
last post by:
I have a query which calculates the data from two table and then yields the number of points based on various calculations for each category.
Essentially we are trying to rank our Agents based on performance, and each category (Senority, Sales, occurances etc) has a points value. We have a final query that combines all these points values for the different categories to yeild the final total.
What I need is a query that will show the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |