Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Faulty Relations???

Discussion in 'Information Technology' started by Jeff C, Nov 10, 2005.

  1. Jeff C

    Jeff C
    Expand Collapse
    Guest

    I am fighting this query which keeps giving me duplicate results despite my
    joins. I am guessing it may be the overall design but am trying to work with
    what I have. Could someone point me towards the right direction?



    SELECT qry_01Inside_Calculate.Badge AS Emp_ID, qry_01Inside_Calculate.Name,
    Shifts_Bid.Shift_ID, qry_01Inside_Calculate.[SS#] AS SSN,
    Shifts_Bid.Day_Worked, Shifts_Bid.Shift_Worked, Shifts_Bid.Hours_Worked,
    Shifts_Bid.Bid_Rate, qry_01Inside_Calculate.Productive_Rate AS Base_Rate,
    Shifts_Bid.Processed, qry_01Inside_Calculate.OTR
    FROM Shifts_Bid INNER JOIN qry_01Inside_Calculate ON Shifts_Bid.Emp_ID =
    qry_01Inside_Calculate.Badge
    WHERE (((Shifts_Bid.Day_Worked) Between
    [Forms]![frm_RptPayPeriod]![txt_BeginPay] And
    [Forms]![frm_RptPayPeriod]![txt_EndPay]) AND ((Shifts_Bid.Processed)=No))
    ORDER BY Shifts_Bid.Day_Worked DESC;
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    What's qry_01Inside_Calculate, and what are the details of all of the tables
    involved (especially the PK of each)?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    news:DD127002-BB03-4B8E-8C52-5A80CDB9C93C@microsoft.com...
    >I am fighting this query which keeps giving me duplicate results despite my
    > joins. I am guessing it may be the overall design but am trying to work
    > with
    > what I have. Could someone point me towards the right direction?
    >
    >
    >
    > SELECT qry_01Inside_Calculate.Badge AS Emp_ID,
    > qry_01Inside_Calculate.Name,
    > Shifts_Bid.Shift_ID, qry_01Inside_Calculate.[SS#] AS SSN,
    > Shifts_Bid.Day_Worked, Shifts_Bid.Shift_Worked, Shifts_Bid.Hours_Worked,
    > Shifts_Bid.Bid_Rate, qry_01Inside_Calculate.Productive_Rate AS Base_Rate,
    > Shifts_Bid.Processed, qry_01Inside_Calculate.OTR
    > FROM Shifts_Bid INNER JOIN qry_01Inside_Calculate ON Shifts_Bid.Emp_ID =
    > qry_01Inside_Calculate.Badge
    > WHERE (((Shifts_Bid.Day_Worked) Between
    > [Forms]![frm_RptPayPeriod]![txt_BeginPay] And
    > [Forms]![frm_RptPayPeriod]![txt_EndPay]) AND ((Shifts_Bid.Processed)=No))
    > ORDER BY Shifts_Bid.Day_Worked DESC;
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 9 Nov 2005 12:59:04 -0800, Jeff C
    <JeffC@discussions.microsoft.com> wrote:

    >I am fighting this query which keeps giving me duplicate results despite my
    >joins. I am guessing it may be the overall design but am trying to work with
    >what I have. Could someone point me towards the right direction?
    >
    >
    >
    >SELECT qry_01Inside_Calculate.Badge AS Emp_ID, qry_01Inside_Calculate.Name,
    >Shifts_Bid.Shift_ID, qry_01Inside_Calculate.[SS#] AS SSN,
    >Shifts_Bid.Day_Worked, Shifts_Bid.Shift_Worked, Shifts_Bid.Hours_Worked,
    >Shifts_Bid.Bid_Rate, qry_01Inside_Calculate.Productive_Rate AS Base_Rate,
    >Shifts_Bid.Processed, qry_01Inside_Calculate.OTR
    >FROM Shifts_Bid INNER JOIN qry_01Inside_Calculate ON Shifts_Bid.Emp_ID =
    >qry_01Inside_Calculate.Badge
    >WHERE (((Shifts_Bid.Day_Worked) Between
    >[Forms]![frm_RptPayPeriod]![txt_BeginPay] And
    >[Forms]![frm_RptPayPeriod]![txt_EndPay]) AND ((Shifts_Bid.Processed)=No))
    >ORDER BY Shifts_Bid.Day_Worked DESC;


    If qry_01InsideCalculate has multiple records for a given value of
    Badge, then you'll see multiple copies of the data in Shifts_Bid -
    that's how queries work. Or, vice versa - if each record in the query
    is related to several records in Shifts_Bid, you'll see duplicates.

    What is in fact the relationship between the two?

    What are you seeing duplicated?

    If you pull up the records for that person's Emp_ID in
    qry_01Inside_Calculate how many records do you see? How many records
    are there in Shifts_Bid for that Emp_ID?

    John W. Vinson[MVP]
     

Share This Page