Results 1 to 2 of 2

Thread: combine (2000)

  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combine (2000)

    Hi, you have been such a good help. Thanks so much. I have ordered two different books and have read a lot of your access post. I have run into another problem. We have over 100,000 patients. Excel only goes up to around 65,000, so we split Excel into 3 different spreadsheets. Excel with patients last name A-L, M-P, and Q-Z the headings named A1-last name, B1-First Name, C1-DOB, D1-City. We have a separate spreadsheet for the clients with bad debt. Most of the Clients with bad debt are names from the patient spreadsheet, A11 being the same but adding E1 saying bad debt. The problem is the boss has combined all 3 patient spreadsheets into an access table. He wants us to add a field in the new Access database and call it bad dept. If John Smith was listed in the Bad Dept spreadsheet then type in the E1 field bad dept. We printed off the bad dept list and we are doing edit find to find the name then typing bad dept. If the name is not in the Database then we are typing it in and putting bad debt in E1. There are over 2000 patients with bad dept. There has got to be a better way.
    Please help us

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

    Re: combine (2000)

    In the first place, I would not use a text field in Access for this, but a Yes/No field. (Yes = bad debt, No = no bad debt).

    In the second place, you may be able to automate this mostly or entirely by using queries. Since your spreadsheets do not have a patient number, SSN or something like that, there may be a problem with duplicates - patients with the same first name, last name, DOB and city. You will have to check for this.

    WARNING: to be on the safe side, make a copy of the database before doing the following, and make an extra copy after each major step. If you do something wrong, you can go back to the previous version.

    Create a Yes/No field named BadDepth in the patients table (in Access)

    Import the Bad Debt spreadsheet into the database.

    Create a new query, and add both the patients table and the Bad Debt table to the query.
    Join them on Last Name, First Name, DOB and City.
    Select Query | Update Query.
    Add the BadDepth field from the patients table to the query grid.
    In the "Update to" line for this field, enter Yes.
    Select Query | Run or click the Run button on the toolbar.
    Access will warn you how many records will be updated. Confirm.
    This will set the BadDepth field to Yes for all records in the patients table that also occur in the Bad Debt table.
    Although you won't need this query again, it's best to save it as documentation of what you have done.

    Create a new query, and add both the patients table and the Bad Debt table to the query.
    Join them on the Last Name, First Name, DOB and City fields.
    Double click each of the join lines in turn, and select the option to return ALL records from the Bad Debt table (depending on how you created the joins, it will be the second or third option.)
    Add the four fields from the Bad Debt table to the query grid, and the Last Name field from the patients table.
    In the criteria line, enter Is Null under the Last Name field.
    If you like, you can switch to datasheet view now to check that only records are displayed that are not yet in the patients table.
    Add a new column BadDepth: Yes
    Select Query | Append Query..., then select the patients table as the target.
    Important: clear the "Append to" box for the Last Name column from the patients table. The "Append to" for the other fields should be filled in correctly automatically.
    Select Query | Run or click the Run button on the toolbar.
    Access will warn you how many records will be added. Confirm.
    This will add the records from the Bad Debt table that do not occur in the patients table.
    Although you won't need this query again either, it's best to save it as documentation of what you have done.

Posting Permissions

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