Jump to content

Te4t0n

Members
  • Content Count

    8
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Te4t0n

  • Rank
    Newbie
  1. I have the Mode_ID using Where INSELECT Mode_IDFROM TBLEventRecordWHERE (Input_Time IN (SELECT MIN(Input_Time) AS Input_Time FROM (SELECT TBLMode.Mode_Description, TBLEventRecord_1.Input_Time FROM TBLEventRecord AS TBLEventRecord_1 INNER JOIN TBLMode ON TBLEventRecord_1.Mode_ID = TBLMode.Mode_ID WHERE (TBLEventRecord_1.Input_Time >= @StartTime) AND (TBLEventRecord_1.Input_Time <= @FinishTime)) AS TempModesTable))I'll do the same for Mode_Description:WHERE (Mode_ID IN (SELECT Mode_ID FROM TBLEventRecord WHERE (Input_Time IN (SELECT MIN(Input_Time) AS Input_Time FROM (SELECT TBLMode_1.Mode_Description, TBLEventRecord_1.Input_Time FROM TBLEventRecord AS TBLEventRecord_1 INNER JOIN TBLMode AS TBLMode_1 ON TBLEventRecord_1.Mode_ID = TBLMode_1.Mode_ID WHERE (TBLEventRecord_1.Input_Time >= @StartTime) AND (TBLEventRecord_1.Input_Time <= @FinishTime)) AS TempModesTable))))this works, and I;m pretty happy with it now i look back. i would just like confirmation this is the best way. thks.
  2. Hi,I have 2 tables:TBLEventRecord Input_Time as DateTime Mode_ID as IntegerTBLMode Mode_ID Mode_DescriptionI need to get all the rows from TBLEventRecord between two dates (StartTime, FinishTime) and then find the Min of the Input_Time in that range and use it's Mode_ID to get it's Mode_Decription.so far I have:SELECT MIN(Input_Time) AS Input_Time FROM (SELECT TBLMode.Mode_Description, TBLEventRecord.Input_Time FROM TBLEventRecord INNER JOIN TBLMode ON TBLEventRecord.Mode_ID = TBLMode.Mode_ID WHERE (TBLEventRecord.Input_Time >= @StartTime) AND (TBLEventRecord.Input_Time <= @FinishTime)) AS TempModesTable Which gets me the Min Input_Time from a given range, which is great so far. But i now need to get it's Mode_ID out of TBLEventRecord and then get the Mode_Description out of TBLMode.I'm thinking along the lines of now using WHERE IN to get the ID and again for the Mode Description. Is this the best way or is there a short SQL Statement?
  3. mignulikz, Has also PM'd me.Could the Admin please remove this user.How did this happen? Has a security hole been found with the registration? Can this be looked into and find how this happened and prevent it from happening in the future?Regards,Tom
  4. Te4t0n

    Simple Query

    Hi, the union all didn't display the required records.But using Inner Join worked great, except it returned one record twice?
  5. Te4t0n

    Simple Query

    Thank you for replying,I will try your solutions, i think an inner join on a subquery with where statements will work best, it's really close to all my solutions i tried.I'll pot back tomorrow between 9am and 10am.
  6. Can an Admin Remove this please. Thks
  7. Te4t0n

    Simple Query

    Hi, I have a table:Table1 Co1 is an ID (auto increment)Co2 is a DateCo3 is TextExample Data:Col1 col2 col31 01/12/2008 812/112 30/12/2008 812/223 18/12/2008 812/334 10/12/2008 812/335 21/12/2008 812/226 06/12/2008 812/227 04/12/2008 812/118 01/12/2008 812/119 02/12/2008 812/11I would like for a query to select all three columns and return only rows that have the highest date the col3.7 04/12/2008 812/112 30/12/2008 812/223 18/12/2008 812/33I hope this is enough information. P.S i'm using Access, so just SQL, WHERE, IN, Subqeries etc would be great thks.
  8. TBL_camp_total (Table)Mill Roll Order: Textline: TextMinOfDate: Date/TimeMaxOfDate: Date/TimeType/Micron: TextHandover_corr: NumberPlannedEventsMR Number: TextQuerry:SELECT Max(TBL_camp_total.MinOfDate) AS MaxOfMinOfDate, TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51"));When i run this at current i get 69 records. It works fine.I would like to also extract the Handover_corrBut if i just add it and group by i get 1000's of records...I tried you usingSELECT Handover_corr FROM TBL_camp_totalWHERE (TBL_camp_total.MinOfDate, TBL_camp_total.[Type/Micron]) IN(SELECT Max(TBL_camp_total.MinOfDate) AS MaxOfMinOfDate, TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51")))andSELECT Handover_corr FROM TBL_camp_totalWHERE (TBL_camp_total.MinOfDate+TBL_camp_total.[Type/Micron]) IN(SELECT Max(TBL_camp_total.MinOfDate) AS MaxOfMinOfDate+TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51")))andSELECT * FROM TBL_camp_totalWHERE (SELECT TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51"))) as SQL1 INNER JOIN TBL_camp_total ON (SQL1.MaxOfMinOfDate = TBL_camp_total.MinOfDate) AND (SQL1.[Type/Micron] = TBL_camp_total.[Type/Micron]);i want the handover_corr for every Type/Micron which my sub query returns. I Also don't know how to convert a datetime into a varchar. I'm using sql within Access btw. thks
×
×
  • Create New...