Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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]
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page