Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append Query w/Duplicate records (2002, SP-2)

    I'll try and make this clear. I'm getting some duplicate/unwanted entries to a query I'm building.
    A table [Clients] contains Client info (name, ID, etc), a second table [AuthCode] contains Services authorized for that client. Third table down [AuthUnits] contains date ranges that the services are authorized for, and 4th table down [BillUnits] contains the dates that the service was provided. That part works great, I'm pulling data based on the [BillUnits] service date. Now the hard part. For each Service Authorized [AuthCode], there is a period where Insurance has issued a waiver. If [BillUnits].[ServiceDate] falls within the range of the [Waiver] dates, I want to include the waiver information. If the waiver expired when we provided services, I do not want to include the waiver info.

    Currently table [Waiver] is related to [AuthCode]. What's happening is one of two things depending on my criteria that I'm changing to make it work. 1. I get the [BillUnits].[ServiceDate] listed for each waiver, even if expired (duplicate entries) when I don't check the service date against the waiver date, or 2., when I check the waiver end date, the [BillUnits].[ServiceDate] doesn't show up if the waiver expired.

    Any insight on where to go would be greatly appreciated.
    Thank you,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query w/Duplicate records (2002, SP-2)

    Could you post a stripped down copy of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it. (Of course, this only helps if you're using Access 2000 or later.)
    <LI>Attach the zip file to a reply.[/list]That would enable Loungers to look at the problem directly, and try things out.

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query w/Duplicate records (2002, SP-2)

    Here is a zipped Access 97 version as small as I could get it. It contains only 5 tables and 2 queries. I've hard coded some parameters into the queries. One query is for checking billing data only for current month (1/04). The second query is for Rebilling Companies, and it searches for data for the fiscal year (7/1/03 -1/31/04). When the tests for SvcDate within Waiver Date range are added, I lose data for those people that have old waivers, but not new ones. (see Tejeda on NewBills, Houser on Rebills). If the tests are removed, I pick up duplicate service date entries (see Bailey-OCT on NewBills, Gunn on Rebills).
    Please let me know if you need anything else.
    Thank you
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •