Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Unanswered: Merging two tables using date bookends

    I am having zero luck trying to get a meaningful query out of a pair of tables that I have.

    Table 1: ProductLotStartEnd
    Fields -
    - Lot Number: A unique production lot number
    - Start: The date & time that the lot was started
    - End: The date & time that the lot was finished

    Table 2: OvenTemps
    Fields -
    - DateTime: The date & time the temperature reading was taken
    - Probe01: The temperature data taken from probe #1
    - Probe02: The temperature data taken from probe #2
    ...etc...

    I am trying to create a query that includes the Lot Number of the product being cooked with the ProbeXX readings from while it was cooking. I know that I need to get the DateTime values that are between Start and End, but everything that I have tried either faults out, goes off into calculation neverland, or gives me a giant table with the same LotNumber and DateTime for thousands of rows.

    Does anybody have any suggestions?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presumably you'd expect multiple oven temps for a product lot
    its not clear if there are multiple product lots running concurrently during the same time period.

    on what you have provided so far this should get you close to wear you want
    Code:
    select p.LotNumber, T.Probe01,T.Probe02 from ProductLotStartEnd as p,OvenTemps as T
    where p.Start >= T.DateTime and p.end<= T.Datetime
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2013
    Posts
    3
    Quote Originally Posted by healdem View Post
    presumably you'd expect multiple oven temps for a product lot
    its not clear if there are multiple product lots running concurrently during the same time period.

    on what you have provided so far this should get you close to wear you want
    Code:
    select p.LotNumber, T.Probe01,T.Probe02 from ProductLotStartEnd as p,OvenTemps as T
    where p.Start >= T.DateTime and p.end<= T.Datetime
    Thanks, healdem. The SQL code you gave worked on my test data perfectly (after I fixed your sign mistake ). Unfortunately, it is not working on my real data.

    Here is my SQL code for the test data:
    SELECT p.Lot, T.Probe01, T.Probe02, T.Probe03, T.Probe04, T.ReadingTime
    FROM tblLotStartEnd_Test AS p, tblProbes_Test AS T
    WHERE (((p.Start)<T.ReadingTime) And ((p.End)>T.ReadingTime));

    The test code gives me one unique T.ReadingTime for each line of data, as it should.

    Here is my SQL code for the real data:
    SELECT p.Lot, T.Probe01, T.Probe02, T.Probe03, T.Probe04, T.ReadingTime
    FROM qry01ChesmontLotStartEnd AS p, qry04ChesmontRawData AS T
    WHERE (((p.Start)<[T].[ReadingTime]) AND ((p.End)>[T].[ReadingTime]));

    The real data code gives me the same T.ReadingTime on each line of data, which is incorrect. On the positive side, the line of data that it is giving me ad nauseum is the first line of data that I should be getting...

    I cannot find the difference that is messing me up...


    As a side note, there is only one lot in the oven at a time and there are small time gaps between lots. Part of the goal is to tabulate the temperature variations during a lot at various points in the oven without worrying about the temperatures when no lots are running (ie - empty, stoppped, doors open, shut down, etc).

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by DELurker View Post
    Thanks, healdem. The SQL code you gave worked on my test data perfectly (after I fixed your sign mistake ). Unfortunately, it is not working on my real data.
    thats the problem with air code, its not tested it may or may not work

    [QUOTE=DELurker;6587660
    Unfortunately, it is not working on my real data.
    ....I cannot find the difference that is messing me up...
    [/QUOTE]

    short of seeing the query definitions then I'd agree and the data then I'd agree I can't see why it isn't working. although my initial suspicion would be that because you are using a query on both sides of the join there is a strong possibility of a the join being the root cause of the problem.

    check that there is only one row per product in the p.query
    then check there is only one row per sample if the q.query
    if you have more than one in either then thats almost certainly your problem
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2013
    Posts
    3
    Yep, there was an extra table included in a previous query which was giving me excess data lines.

    Thanks for all of your help, healdem.

Posting Permissions

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