Convert DbVisualizer code into Access

Hello,

How can I convert this code from DbVisualizer, into Access?  (Assuming this is even possible...!!!)  I don't know anything about this code, so my only hope is something that will convert it for me.

SELECT L.WORKGROUP,T.DATE_OPENED,T.TICKETID,C.NAME,T.TROUBLE_TYPE,L.ASSIGNED_TO,T.DESCRIPTION,T.TROUBLE_CODE
,l.action, fs.status, fs.dateassigned, fs.assignedtousername
FROM OSS_TROUBLE_TICKET T, OSS_TROUBLE_TICKET_LOG L, OSS_CUSTOMER C
,fsdc_flowassignments fs
WHERE TRUNC(T.DATE_OPENED) <= '01-oct-04'
AND (TRUNC (T.DATE_CLOSED) IS NULL OR TRUNC (T.DATE_CLOSED) > '01-oct-04')
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRUNC(L.STARTED) <= '01-oct-04'
and t.hasticketflow = 'Y'  --new style
and fs.status = 'Live' --Active in what stage
AND (TRUNC (L.ACTION_DATE) IS NULL OR TRUNC (L.ACTION_DATE) > '01-oct-04')
UNION
SELECT L.WORKGROUP,T.DATE_OPENED,T.TICKETID,C.NAME,T.TROUBLE_TYPE,L.ASSIGNED_TO,T.DESCRIPTION,T.TROUBLE_CODE
,l.action, fs.status, fs.dateassigned, fs.assignedtousername
FROM OSS_TROUBLE_TICKET T, OSS_TROUBLE_TICKET_LOG L, OSS_CUSTOMER C
,fsdc_flowassignments fs
WHERE TRUNC(T.DATE_OPENED) <= '01-oct-04'
AND (TRUNC (T.DATE_CLOSED) IS NULL OR TRUNC (T.DATE_CLOSED) > '01-oct-04')
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid (+)
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRUNC(L.STARTED) <= '01-oct-04'
and t.hasticketflow = 'Y'  --new style tickets
and l.assigned_to = 'Unassigned' --belongs to unassigned
and l.action = 'Assessment'  --sitting in assessment  
and fs.status = 'Live' --Active in what stage
UNION
SELECT L.WORKGROUP,T.DATE_OPENED,T.TICKETID,C.NAME,T.TROUBLE_TYPE,L.ASSIGNED_TO,T.DESCRIPTION,T.TROUBLE_CODE
,l.action, NULL "ASSIGNMENT_STATUS", sysdate "DATEASSIGNED", null "ASSIGNEDTOUSERNAME"
FROM OSS_TROUBLE_TICKET T, OSS_TROUBLE_TICKET_LOG L, OSS_CUSTOMER C
WHERE TRUNC(T.DATE_OPENED) <= '01-oct-04'
AND (TRUNC (T.DATE_CLOSED) IS NULL OR TRUNC (T.DATE_CLOSED) > '01-oct-04')
AND T.TICKETID = L.TICKETID
AND T.CUSTOMERID = C.CUSTOMERID
and t.hasticketflow = 'N' --old style tickets
AND T.TROUBLE_TYPE ='Issue'
AND TRUNC(L.STARTED) <= '01-oct-04'
AND (TRUNC (L.ACTION_DATE) IS NULL OR TRUNC (L.ACTION_DATE) > '01-oct-04')
ORDER BY 2 ASC

Thank you,
Jay
xp310Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
change TRUNC to TRIM
use US format  and wrap in # for dates ... from '01-oct-04' to #10/01/2004#
remove all comments like ...     --new style
NULL "ASSIGNMENT_STATUS"   ...   NULL AS "ASSIGNMENT_STATUS"
sysdate "DATEASSIGNED"         ...    Now() AS "DATEASSIGNED"
null "ASSIGNEDTOUSERNAME"   ... NULL AS "ASSIGNEDTOUSERNAME"

see if that gets you closer, if you [paste it into an Access query it should tell you where it has trouble.

Steve
0
xp310Author Commented:
Steve,

I didn't have much luck.  This is what the error says...

In operator without () in query expression 'TRIM(T.DATE_OPENED)<=#10/01/2004#
AND (TRIM(T.DATE_CLOSED) IS NULL OR TRIM (T.DATE_CLOSED)>#10/01/2004#)
AND T.TICKETID = L.TICKETID
and l.workflowstageid = fs.workflowstageid
AND T.CUSTOMERID = C.CUSTOMERID
AND T.TROUBLE_TYPE ='Issue'
AND TRIM'.

Any ideas?
0
xp310Author Commented:
On thing I'm not entirely sure of, is if I have all the tables I need.  I am linking to:

OSR_OTIS_OSS_CUSTOMER
OSR_OTIS_OSS_TROUBLE_TICKET
OSR_OTIS_OSS_TROUBLE_TICKET_LOG

is that correct?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stevbeCommented:
Looks like you have the correct tables ...
I would break it up into the pieces between the UNION statements to make it easier to debug each part


try specifically converting to a date ...
    CDate(TRIM(T.DATE_OPENED))<=#10/01/2004#

found another piece ...
   FROM OSS_TROUBLE_TICKET AS T, OSS_TROUBLE_TICKET_LOG AS L, OSS_CUSTOMER AS C

Steve
0
xp310Author Commented:
Um, I'm unclear as to what I should be doing?
0
stevbeCommented:
make a new query and put the SQL in ...

start with getting the tables and fields

SELECT
  L.WORKGROUP,
  T.DATE_OPENED,
  T.TICKETID,
  C.NAME,
  T.TROUBLE_TYPE,
  L.ASSIGNED_TO,
  T.DESCRIPTION,
  T.TROUBLE_CODE,
  L.ACTION,
  FS.status,
  FS.dateassigned,
  FS.assignedtousername
FROM
  OSS_TROUBLE_TICKET AS T,
  OSS_TROUBLE_TICKET_LOG AS L,
  OSS_CUSTOMER AS C,
  fsdc_flowassignments FS

Steve
0
xp310Author Commented:
The Microsoft Jet database engine cannot find the input table or query 'OSS_TROUBLE_TICKET'.  Make sure it exists and that its name is spelled correctly.
0
stevbeCommented:
you need anlother linked table ...   fsdc_flowassignments

SELECT
  L.WORKGROUP,
  T.DATE_OPENED,
  T.TICKETID,
  C.NAME,
  T.TROUBLE_TYPE,
  L.ASSIGNED_TO,
  T.DESCRIPTION,
  T.TROUBLE_CODE,
  L.ACTION,
  FS.status,
  FS.dateassigned,
  FS.assignedtousername
FROM
  OSR_OTIS_OSS_TROUBLE_TICKET AS T,
  OSR_OTIS_OSS_TROUBLE_TICKET_LOG AS L,
  OSR_OTIS_OSS_CUSTOMER AS C,
  fsdc_flowassignments FS

0
xp310Author Commented:
I added it but I still get the same error.
0
stevbeCommented:
ok ... lets try to get 1 table to work ...

SELECT
  L.WORKGROUP,
  L.ASSIGNED_TO,
  L.ACTION
FROM
  OSR_OTIS_OSS_TROUBLE_TICKET_LOG AS L

Steve
0
xp310Author Commented:
Yup, that worked.  Wether or not the information is right, I don't know.  But it did return results.
0
stevbeCommented:
have you tried adding the tables and their respective fields back into the SQL to see what part(s) are goofy?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xp310Author Commented:
stevbe,

I'm going to close out this question as I was able to excalate this issue with a department here & they will be recoding the query.  Thank you for your help :)

Jay
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.