Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Unanswered: A challange for the MS Access studs

    SELECT DISTINCT All_Docs_Distinct.Name,
    Bh_series2000.Dr_Num AS Bh_Dr_Num,
    SFS_DOCS.[CORP#] AS Sf_Dr_num,
    brsdoco_Full_Name.Dr_Num AS Bh_Lab_Op_Num,
    sfhdoco_Full_Name.Dr_Num AS Sf_Lab_Op_Num,
    sfhdoci_Full_Name.Dr_Num AS Sf_Lab_Ip_Num,
    brsdoci_Full_Name.Brsdoci_Num AS Bh_Lab_Ip_Num,
    SFS_DOCS.STATE_LIC AS Sf_Lic_Nun,
    Bh_series2000.[State Lic #] AS Bh_Lic_Num,
    SFS_DOCS.UPIN AS Sf_Upin,
    Bh_series2000.[Unique Physician ID Number] AS Bh_UPIN,
    Bh_series2000.[Medical Service],
    SFS_DOCS.Prov_Type, Bh_series2000.City
    FROM Bh_series2000 RIGHT JOIN
    (SFS_DOCS RIGHT JOIN
    (brsdoco_Full_Name RIGHT JOIN
    (sfhdoco_Full_Name RIGHT JOIN
    (sfhdoci_Full_Name RIGHT JOIN
    (brsdoci_Full_Name RIGHT JOIN
    All_Docs_Distinct ON
    brsdoci_Full_Name.Name = All_Docs_Distinct.Name) ON
    sfhdoci_Full_Name.Name = All_Docs_Distinct.Name) ON
    sfhdoco_Full_Name.Name = All_Docs_Distinct.Name) ON
    brsdoco_Full_Name.Name = All_Docs_Distinct.Name) ON
    SFS_DOCS.Name = All_Docs_Distinct.Name) ON
    Bh_series2000.Name = All_Docs_Distinct.Name
    WHERE (((SFS_DOCS.STATUS) Is Null) AND ((Bh_series2000.[Active/Inactive])="A"));


    Expected resutlts:

    Name,#,#,#,#,#,#,fielda,fieldb,fieldc,fieldd,field e,fieldf

    Actual results:
    the query is multiplying the records. I am getting total number of recrods for the "name" than there is in the total from all tables.

    Attached also is an image for the query in the design view.

    Please help.

    Thanks a lot.
    Attached Files Attached Files
    Mo

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: A challange for the MS Access studs

    Originally posted by mkasem
    SELECT DISTINCT All_Docs_Distinct.Name,
    Bh_series2000.Dr_Num AS Bh_Dr_Num,
    SFS_DOCS.[CORP#] AS Sf_Dr_num,
    brsdoco_Full_Name.Dr_Num AS Bh_Lab_Op_Num,
    sfhdoco_Full_Name.Dr_Num AS Sf_Lab_Op_Num,
    sfhdoci_Full_Name.Dr_Num AS Sf_Lab_Ip_Num,
    brsdoci_Full_Name.Brsdoci_Num AS Bh_Lab_Ip_Num,
    SFS_DOCS.STATE_LIC AS Sf_Lic_Nun,
    Bh_series2000.[State Lic #] AS Bh_Lic_Num,
    SFS_DOCS.UPIN AS Sf_Upin,
    Bh_series2000.[Unique Physician ID Number] AS Bh_UPIN,
    Bh_series2000.[Medical Service],
    SFS_DOCS.Prov_Type, Bh_series2000.City
    FROM Bh_series2000 RIGHT JOIN
    (SFS_DOCS RIGHT JOIN
    (brsdoco_Full_Name RIGHT JOIN
    (sfhdoco_Full_Name RIGHT JOIN
    (sfhdoci_Full_Name RIGHT JOIN
    (brsdoci_Full_Name RIGHT JOIN
    All_Docs_Distinct ON
    brsdoci_Full_Name.Name = All_Docs_Distinct.Name) ON
    sfhdoci_Full_Name.Name = All_Docs_Distinct.Name) ON
    sfhdoco_Full_Name.Name = All_Docs_Distinct.Name) ON
    brsdoco_Full_Name.Name = All_Docs_Distinct.Name) ON
    SFS_DOCS.Name = All_Docs_Distinct.Name) ON
    Bh_series2000.Name = All_Docs_Distinct.Name
    WHERE (((SFS_DOCS.STATUS) Is Null) AND ((Bh_series2000.[Active/Inactive])="A"));


    Expected resutlts:

    Name,#,#,#,#,#,#,fielda,fieldb,fieldc,fieldd,field e,fieldf

    Actual results:
    the query is multiplying the records. I am getting total number of recrods for the "name" than there is in the total from all tables.

    Attached also is an image for the query in the design view.

    Please help.

    Thanks a lot.
    The best way of identifying the problem is to remove one table, run the query and see the results, then repeat this for each of the tables. By elimination you will find out where the problem is. You may have to create multiple layers of queries to get the result you want.

    As an observation in your image you don't seem to have any primary keys on the table (normally indicated by bold name in the table in query view) and no relationships between the table enforcing referential integrity.

  3. #3
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Re: A challange for the MS Access studs

    Hi, thanks for the help. I will try that. But the reason they don't have PK's because those are queries already. I guess I have to normalize those queries even more.

    Thanks again.
    Mo

Posting Permissions

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