Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Another SQL problem

  • 26-06-2000 8:42am
    #1
    Registered Users, Registered Users 2 Posts: 1,643 ✭✭✭


    Still having trouble getting to grips with all the commands (and the exactness) of SQL.

    Anyways,

    Problem 1:

    If i have a column of numbers 1 to 100 say, and i want to query sets of various non sequential numbers;

    e.g: 1,2,8,14,50,69

    or: 12,99

    How do I do it? Like when asking for date parameter I want it to ask me to enter all the numbers to be queried as well.


    The Second Problem:

    Unfortunately I don't reckon this one is even possible, but if you even think it might let me know (at least then it's worth still looking for an answer).


    Basically I have a number value column which i want to convert to a time value which can then be added to a time.


    In other words I have a column of say 90, 120, 180. Which is a number value of the minutes.

    I want to convert them to time (01:30 = 90 ...) and have them added to the time field. Any ideas?


    Cheers for any help,

    JAK.


Comments

  • Closed Accounts Posts: 91 ✭✭koloth


    for the second problem, firstly divide the mins number by 60 and round down. So 90 divided by 60 is 1.5, round to 1 that gives you the hour. If you can't round to 1 you could probably parse it to take what's left of the period.

    Then use MOD 60 (ironically I've never looked at SQL but I'm sure there's an equivalant) on the minutes. So, same example, 90 MOD 60 gives you 30, this is the minutes remainder.

    After that you get 1.30

    Same goes for 120, 155, onwards. Is this what you're looking for? (I'm no mathematician). I did the same thing in an Excel spreadsheet, so if you want that I'll send it on.


  • Registered Users, Registered Users 2 Posts: 1,643 ✭✭✭Jak


    Unfortunately the Databases Im working on are too big for Excel. But Thanks.

    What I am after is the SQL Language (as it has to be done in Access)which would convert number values to time values.

    Once i can sort this then Im gonna put together the macro to do all the various bits of analysis on command.


    JAK.


  • Registered Users, Registered Users 2 Posts: 2,494 ✭✭✭kayos


    Ok for your Second problem if the field your adding is always in minutes you could do the following
    DATEADD(minute,<ValueToAdd>,<ValuetoAddto> )

    I'll post the solution to the first prob in a while but in the mean time try messing around with CharIndex and passing in all the numbers in a comma seperated format.

    Kayos


  • Closed Accounts Posts: 91 ✭✭koloth


    no no, I didn't mean do it in Excel. I meant I have examples in an Excel spreadsheet. Had thought you wanted to do this in SQL Server, but you can still use the MOD function and simple parse functions such as LEFT as CSTR in actual Access SQL.


  • Registered Users, Registered Users 2 Posts: 2,494 ✭✭✭kayos


    Solution to Problem one:
    DECLARE @vDocCodes VARCHAR(255),
    @iToken INT,
    @iCode INT
    SET @vDocCodes = '1,2,3,'
    WHILE CHARINDEX (',', @vDocCodes) >= 1
    BEGIN
    SELECT @iToken = CHARINDEX(',',@vDocCodes)
    SELECT @iCode = CONVERT(INT,SUBSTRING(@vDocCodes,1,@iToken - 1))
    /*
    ** Do what you want with the number
    */
    SELECT @vDocCodes = SUBSTRING(@vDocCodes, @iToken + 1, 255)
    END

    [This message has been edited by kayos (edited 26-06-2000).]


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 1,643 ✭✭✭Jak


    Right.

    First of all - Cheers for all this help lads.

    Koloth, I'd be interested in taking a look at that example so - Mail it to ja2k@ireland.com whenever you can.

    Kayos-

    For the second problem. The column is a number data type. What I want is to put a command in that will first convert the number 90 into minutes (or whatever format) that can then be added (using date add) to the original time - creating a new field which lists the converted times.

    e.g 12.30 + 01.30 = 14:00


    As for the second part you posted. Je5us - I thought that would be the less complicated part of the problem.

    I'll be perfectly honest and say it all looks rather complex. So rather than try to understand it, I just copied the expression into the SQL view and it said

    Characters found after SQL statement

    I'm doing something wrong I'm sure.

    Problem is I only recently had to start using MSaccess for my research - so SQL is about 2 weeks old to me. Turns out it's a lot more difficult and precise than I thought.


    Anyhows, I'll keep trying a few things in the meantime.

    Cheers again,

    JAK.


    ps- This is what my SQL view looks like atm. This is basically what I'm using to break up the date and time. I want to add is the Machine number query (prob 1) and have an extra field in the data (prob 2) giving the modified time.


    SELECT TBLTRANSACTION.HORO, TBLTRANSACTION.DUREE_PAYEE, TBLTRANSACTION.TRDATE, CDate(DatePart("d",[trdate]) & "/" & DatePart("m",[trdate]) & "/" & DatePart("yyyy",[trdate])) AS [The date], DatePart("h",[trdate]) AS [The time]
    FROM TBLTRANSACTION
    WHERE (((CDate(DatePart("d",[trdate]) & "/" & DatePart("m",[trdate]) & "/" & DatePart("yyyy",[trdate]))) Between CDate([enter start date]) And CDate([enter end date])) AND ((DatePart("h",[trdate]))>=[enter start time] And (DatePart("h",[trdate]))<[enter end time]));


    [This message has been edited by Jak (edited 26-06-2000).]


  • Registered Users, Registered Users 2 Posts: 2,494 ✭✭✭kayos


    Well all that CharIndex stuff works fine in SQL7 and SQL6.5 if it didn't I'd be *(&^%$!.
    But what works in SQL Server doen't always work in Access frown.gif so sorry about that.

    Kayos


  • Registered Users, Registered Users 2 Posts: 1,643 ✭✭✭Jak


    Ok...

    My last post to do with this i swear ... but to use the dateadd command ... how?

    The column i want to add is called D_P .. the column it's being added to is TRD ... and I want it the result to create a new colum called CO.

    So would this be DateAdd(Minute,<D_P><TRD> ) or what exactly?

    Or is this an SQL 6.5/7 command which won't work in MSAccess?

    Cheers,

    JAK.


  • Registered Users, Registered Users 2 Posts: 2,494 ✭✭✭kayos


    DATEADD(minute,VALUETOADD,VALUETOADDTO)
    e.g. SELECT DATEADD(MINUTE,10,'01/01/2000 13:00')
    would give
    '01/01/2000 13:10'
    DATEADD is a T-SQL command I just took a look at Access Help and its there as well but its looks like its a VBA thing only frown.gif .

    Kayos


Advertisement