473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

I cannot get this Access query to run. Can anyone help?

Expand|Select|Wrap|Line Numbers
  1. SELECT queue_assigned_to, current_queue, Completed_Date, COUNT(record_id) As "Count" FROM
  2. (SELECT queue_assigned_to, current_queue, Completed_Date, RECORD_ID FROM 
  3. (SELECT     t.queue_assigned_to,
  4.     T.current_queue,
  5.     SUBSTR(T.queue_completed_dt,1,10) AS "Completed_Date",
  6.     t.record_id
  7. FROM DRW_500409333 as T
  8. INNER JOIN (
  9. SELECT '500409333' AS "Project ID", record_id,
  10.   MAX(instance_id) AS max_instance
  11. FROM DRW_500409333
  12. WHERE CAST(SUBSTR(queue_completed_dt,1,10) as DATE format 'MM/DD/YYYY') <= CURRENT_DATE - 1
  13. group by '500409333', record_id) as mv
  14. ON (t.instance_id = mv.max_instance AND t.record_id = mv.record_id)
  15. INNER JOIN (
  16. SELECT '500409333' AS "Project ID", record_id, instance_id, current_queue,
  17.         MAX(CREATE_DT) AS max_DAT
  18.         FROM DRW_500409333
  19.         WHERE CAST(SUBSTR(queue_completed_dt,1,10) as DATE format 'MM/DD/YYYY') <= CURRENT_DATE - 1
  20.         GROUP BY '500409333', record_id, instance_id, current_queue) as mv2
  21. ON (t.record_id=mv2.record_id AND t.create_dt = mv2.max_DAT)
  22. WHERE user_action = 'Completed') AS TABLE1
  23. GROUP  BY 1,2,3,4) AS TABLE2
  24. GROUP BY 1,2,3
May 9 '16 #1
1 752
zmbd
5,501 Expert Mod 4TB
ProgrammerCF:

Posting code/script without any explanation as to what it is that you are expecting to happen and what is happening along with any specific details (for example the EXACT error message(s)) will not normally result in any answers.

This is a mess of SQL
1) Are you running this in VBA or from Query
2) if in query, if malformed, you should receive an error that may help you pinpoint the error
3) if in VBA, then cut and paste the resolved string into a query and attempt to run. If malformed you should receive an error that may help you pinpoint the error
4) I would break out the individual SELECT queries within this mess. Run each one, make sure they are returning the correct information. Once that is done you can start to create the stepwise query build until things "broke". This way you have an idea as to where the issue may be located.
May 10 '16 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Casper's Friend | last post by:
It's been a while since I've had to build anything in Access, so forgive me if this is simple...I'm probably just having quite the mental block here. I have a fairly simply query...3 fields which...
2
by: lairds | last post by:
I've built a query that tells me clients, dates they had sessions and if they are Amish or English. I need to know during a certain time period how many of these clients were Amish, etc. My...
1
by: syl | last post by:
Hi I have a huge loto data and i am trying to come up with the total count of all total combination...the manual process to tiring in excel. e.g the possible combinations range from 1 up to 49. so...
1
by: wintonsl | last post by:
Would anyone know how to convert this Access Query to SQL Server Query? Format(((+++)/(IIf(>0,1,0)+IIf(>0,1,0)+IIf(>0,1,0)+IIf(>0,1,0))),"Standard") What this query is doing in Access is...
7
by: narpet | last post by:
Okay... here's another query that I need to write that I just can't seem to figure out. first the table layout (table name TEST): ID# PreID# PostID# Dollars 1 2 1...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
1
by: natalieo | last post by:
Hi everyone , Hope all is well with you guys and girls :) I saw someone had a similar posting in this forum and need some help also with a Access Query Expression. I need an expression to...
1
by: AccessBeetle | last post by:
SELECT DISTINCT .Contact1Index, Contacts., Contacts., Contacts., Contacts., Contacts.prefix, Contacts.Suffix, Contacts., Contacts., Contacts.city, Contacts.state, Contacts.zip, ., tblStatusCloseout.,...
10
by: AlexMwells | last post by:
Hi, I have a access database table with sales data in it, detailing sales rep, customer, date of sale, sale item etc. I need to generate a query which will list which customers haven't brought...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.