Results 1 to 5 of 5
  1. #1
    Ran is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    36

    Cool Need Criteria added SQL Statement

    Having difficulty figuring this issue out.

    I have two different queries built for Hospital Departments and time periods, one is called "PBS Worklist A-Z" and second one is "Chicago Worklist A-Z." Basically I have a patient who was admitted and seen in one specialty (SURG/ENT) and was then taken for surgery in another specialty (SURG/PEDI). The same patient account is in two different queries because of the criteria I put in the query which separates the worklist by specialty. I need to pull the lines of data for specific patient account from the "Chicago Worklist A-Z" query to the "PBS Worklist A-Z" query without changing my criteria for the field "Dept / Spec."
    The reason being, I cant have all the data from "Chicago Worklist A-Z" query with specialty "SURG/PEDI" included in the "PBS" query just specific accounts that are demanded by my director. So is there a way to write something in the SQL statement to pull certain accounts from another criteria even though there is criteria mismatch in specialties?



    Please see SQL statement below:

    SELECT [HIM RAW DATA].Coder, [HIM RAW DATA].[Charge Status], [HIM RAW DATA].[RCA Edits], [HIM RAW DATA].[CM Edits], [HIM RAW DATA].[Research: Details], [HIM RAW DATA].[Charge ID], [HIM RAW DATA].[File ID], [HIM RAW DATA].FC, [HIM RAW DATA].[FC Groups], [HIM RAW DATA].[S ins], [HIM RAW DATA].[Payor Dtls], [HIM RAW DATA].[Pt Acct], [HIM RAW DATA].[MR No], [HIM RAW DATA].[Pt Last Name], [HIM RAW DATA].DOB, [HIM RAW DATA].Provider, [HIM RAW DATA].[Provider Name], [HIM RAW DATA].[Claim Info], [HIM RAW DATA].[Addl Description], [HIM RAW DATA].[Note Description], [HIM RAW DATA].Auth, [HIM RAW DATA].[PBS Flag], [HIM RAW DATA].[Dept / Spec], [HIM RAW DATA].Department, [HIM RAW DATA].[CM Mcal], [HIM RAW DATA].Inpt, [HIM RAW DATA].Started, [HIM RAW DATA].[Dif Add 1], [HIM RAW DATA].[Dif Add 2], [HIM RAW DATA].[RVU-RCA], [HIM RAW DATA].[CPT-RCA], [HIM RAW DATA].CPT, [HIM RAW DATA].[TM ID], [HIM RAW DATA].Units, [HIM RAW DATA].Mod1, [HIM RAW DATA].GP, [HIM RAW DATA].Mod2, [HIM RAW DATA].Mcal50, [HIM RAW DATA].Dx, [HIM RAW DATA].Dx2, [HIM RAW DATA].Dx3, [HIM RAW DATA].Dx4, [HIM RAW DATA].[CPT Description], [HIM RAW DATA].AssistNo, [HIM RAW DATA].AssistName, [HIM RAW DATA].AttendNo, [HIM RAW DATA].AttendName, [HIM RAW DATA].[CPT Type], [HIM RAW DATA].OLIED, [HIM RAW DATA].[OLIED DATE], [HIM RAW DATA].[DO NOT BILL], [HIM RAW DATA].CODED, [HIM RAW DATA].REDO, [HIM RAW DATA].[OLIE RPT ID]
    FROM [HIM RAW DATA]
    WHERE ((([HIM RAW DATA].[Dept / Spec]) In ("ANES","MED","OB/GYN","SURG/DENT","SURG/ENT","SURG/PODI")) AND (([HIM RAW DATA].Started)>#4/30/2012#) AND (([HIM RAW DATA].OLIED)=Off) AND (([HIM RAW DATA].[DO NOT BILL])=Off) AND (([HIM RAW DATA].CODED)=Off));

  2. #2
    Ran is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    36
    just a thought, would an update or append query work in this case?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, UPDATE and APPEND add and change records of table.

    Why do you have multiple identical queries when one query can be used and different criteria applied as needed?

    Otherwise, options:

    1. Build another query of the source table to filter by patient ID

    2. UNION the two specialty queries and apply filter for patient ID
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Ran is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    36
    Yes I understand the reason for one query applied with different criteria to return data as needed. But the reason for two similar queries is, there is a backlog of uncoded surgeries and we have two teams of 6 working on different specialties which splits up the backlog. The 1st team of 6 work out of the database physically (Alpha split), so that query is untouched unless different criteria is needed to be applied. The other team is far away and work on a different list and I have to keep track of who is coding the surgeries.
    Thanks again June7, I will try the union of the two queries. I will post back if there is an issue

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I guess those are good reasons from your view but I still don't fully understand. Both queries have the same data source so they pass data back to the same table. How does multiple queries help keep track of who is coding what?

    Do you have multiple identical forms as well for the different users?

    If the users are working through form bound to query then the filtering can be managed by the form.

    However, if there are only the two queries, that is not so bad. I have seen projects with oodles of identical queries. I suppose if you ever catch up on the backlog the multiple objects will no longer be needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. using two criteria in one case statement
    By chessico in forum Access
    Replies: 5
    Last Post: 03-14-2012, 03:25 PM
  2. Iif statement with multiple criteria
    By coach32 in forum Queries
    Replies: 1
    Last Post: 09-07-2011, 01:28 AM
  3. Replies: 2
    Last Post: 02-17-2010, 09:53 PM
  4. nested if statement with two criteria
    By kendra in forum Queries
    Replies: 5
    Last Post: 06-16-2009, 04:07 PM
  5. Criteria added to Query - Nothing showing
    By eabtx in forum Queries
    Replies: 1
    Last Post: 03-02-2009, 10:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums