Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query Question (2002)

    Hi,

    I have a form bound to a query that is based off of tblfacesheet. There is a field [Physician Name] that gets it data from tblPhysicianDemographics. This works fine. There is also a field for the physician phone number. This data is also stored in tblPhysicianDemographics. I'm trying to keep the user from entering the data twice. Is there a way to set up the form or query so that when the physician name is chosen the corresponding phone number is also entered automatically.

    Thanks,
    Leesha

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

    Re: Query Question (2002)

    You don't need the phone number field in tblfacesheet. Add tblPhysicianDemographics to the query and join it to tblfacesheet on the appropriate field. Double click the join line and select the option to return ALL records from tblfacesheet. Add the phone number field from tblPhysicianDemographics to the query.

    Note: it would be better to have a PhysicianID field in tblfacesheet and tblPhysicianDemographics and join on that field than to join on the Physician Name field.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Question (2002)

    As always I'm amazed! After making the changes you suggested I see more that I will do as well.

    The physician phone number is working fine now. The only problem is that a patient can also have specialty or secondary physicians. The form contains a field for the primary md and corresponding phone number and the specialty md and corresponding phone number. Whenever the primary md is chosen, both phone number fields are updated to that phone number even though there is no information in the specialty md field. I tried linking the specialty md id in tblfacesheet to mdid in tblPhysicianDemographics the same way I did for primary MD but then the form doesn't allow me to make changes. Is there a way to do this without adding a second phone field to frmPhysicianDemographics?

    Thanks,
    Leesha

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

    Re: Query Question (2002)

    Open your query in design view.
    Select Query | Show Table... (or click the Show Table button on the toolbar).
    Add the tblPhysicianDemographics table. You will have two copies of this table in the query now.
    Join the new copy to the patient table on the specialty md field.
    Add the phone number field from the new copy to the query grid, and give it a suitable alias, e.g.

    SpecialtyMDPhone: PhonePhield

    where SpecialtyMDPhone is the display name (alias) and PhonePhield is the name of the field.
    Close and save the query.
    Open the form in design view.
    Set the Control Source of the second phone text box to the name of the new field.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Question (2002)

    Thank you!! I had no idea you could put a table into a query twice like that! Here we again, another whole wealth of opportunity!

    Leesha

Posting Permissions

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