Page 1 of 6 123 ... LastLast
Results 1 to 15 of 76
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: Where am i meant to add query?

    I have to hand code a query, i have called it Rain_Data.

    This is the main part of the query.

    SELECT DATEVALUE(Time), Seconds, (Select TOP 1 Seconds FROM tblImportTableTest) / 3600 as hourFrac,
    CDEC(testa, 7) as A, CDEC(testb, 7) as B, CDEC(testc, 7) as C, CDEC(testd, 7) as D,
    CDEC(teste, 7) as E, CDEC(testf, 7) as F, CDEC(testg, 7) as G, CDEC(testh, 7) as H
    FROM tblImportTableTest


    I inserted the above code into the query code builder.


    --------------------------------------------------------
    -- My queries
    --------------------------------------------------------

    1. Number of spill timesteps >0.001
    Select count(Seconds) AS CT FROM Rain_Data WHERE
    A > 0.001
    OR B > 0.001
    OR C > 0.001
    OR D > 0.001
    OR E > 0.001
    OR F > 0.001
    OR G > 0.001
    OR H > 0.001



    2. Total number of timesteps
    SELECT COUNT(Seconds) from Rain_Data


    3. Spill timesteps as a % of total timesteps
    -- I'm assuming that a spill is greater than 0.001
    SELECT TOP 1
    ((
    Select count(Seconds) AS CT FROM Rain_Data WHERE
    A > 0.001
    OR B > 0.001
    OR C > 0.001
    OR D > 0.001
    OR E > 0.001
    OR F > 0.001
    OR G > 0.001
    OR H > 0.001
    ) /

    (
    SELECT COUNT(Seconds) from Rain_Data
    )) * 100 AS PCentSpills FROM Rain_Data


    Where about do i put the above queries??

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Marleyuk

    I'm not sure what your question is - You appear to have done all the work.

    BTW - if you are interested their are probably some design and SQL improvements you might benfit from.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    213
    Yeah i know thats the code but because im such an access amatuer i dont know where im meant to put it to make the queries work. And yeah and improvements would be great.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by marleyuk
    Yeah i know thats the code but because im such an access amatuer i dont know where im meant to put it to make the queries work. And yeah and improvements would be great.
    Stored query ... SQL Statement run from VBA ... Query as the record source of a form or report ... It could be put on/in any of the listed above (and perhaps others I can't think of right now) ... The important question is WHERE DO YOU WANT TO USE IT? Also, what do you want to use it on? THAT will determine where you need to put it ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2006
    Posts
    213
    Ok so ive got my database setup, ive imported a file into the database, ive called a query to be run on the table and now i want it to be outputted.

    When i tried to run the query it failed to work. I was prompted by several input boxes instead of being shown my query.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by marleyuk
    And yeah and improvements would be great.
    Super.

    Your tblImportTableTest table sounds demormalised. Structure something like
    Seconds, testa, testb, testc, testd.....

    It is the sort of structure you would commonly see in Excel for example. Because of this strucure you need queries like you have got - i.e. long where clauses. A normalised structure would be more like:

    Time, Seconds, TestCode, TestValue

    You change the A, B, C... into rows rather than columns. Your queries would now be along the lines of:
    Code:
    SELECT DATEVALUE(Time), Seconds, (Select TOP 1 Seconds FROM tblImportTableTest) / 3600 as hourFrac,
    TestCode, CDEC(TestValue, 7)
    FROM tblImportTableTest
    WHERE CDEC(TestValue, 7) > 0.001
    It would mean also that if ever you create a Test I you won't have to redesign every query.

    Normalisation reference

    Another principal of relational database is that their is no natural order to the rows. In effect Access returns rows in order of the PK value however not all rdbms's do so. I would put in an Order By clause in your statements that contain TOP 1 to be explicit exactly what you are after. It would also insulate you from any future bugs when TOP 1 is not returning the value you think it will.

    Otherwise your SQL is ok. I originally noticed that your last one could be changed but actually it isn't that big a deal. In case you are interested:
    Code:
    SELECT SUM(Iif(A > 0.001 OR B > 0.001 OR C > 0.001 OR D > 0.001 OR E > 0.001 OR F > 0.001 OR G > 0.001 OR H > 0.001, 1, 0))/COUNT(Seconds) * 100 AS PCentSpills 
    FROM Rain_Data
    Finally - why are you having to convert your test results to decimal?

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2006
    Posts
    213
    Right.. the database imports a file with dynamic fields, every imported file could have a different amount. This means that i couldnt just make normal queries. For some reason my database wont upload here. Is it possible for me to email you it so you can see what i mean??

    Thanks,
    Marley.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi marleyuk

    You need to zip it up and it needs to be below 10MB. You can't attach an unzipped mdb. Best to keep it on the forum as then everyone has access. Remove any sensitive info of course.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2006
    Posts
    213

    Wont

    I have compressed it and zipped it into 1.27mb but when i try and upload it the upload screen just goes white and sticks. Ive used firefox and IE and they both do the same.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmm - that could be the Dodge McDodge software. We've been having lots of problems recently with the site hanging.

    You could try PMing a mod or contact SoftWareRevue (admin - very good) in the Feedback forum. They might be able to attach it for you if you email it to them. Otherwise, keep trying is all I can add - apols.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi marleyuk

    tks for the db - I can't attach it either. I'll let admin know.

    What specifically do you want me to check out?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2006
    Posts
    213
    well i have a couple of querys on it. I want them to run in the same process that i import the file. So.. i import file --> it goes into table --> queries run --> output as a formatted .txt

    but there queries wont run the way they should. Any idea why?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Is the problem then the fact that the table field names are not fixed? That when you import the table it could be in any format?

    In that case there is not much you can do - you will have to adjust the rain_data query each time you import your table. At least you had the idea of creating a single query to hang all the rest off.
    Code:
    SELECT DATEVALUE([Time]), [Seconds], (Select TOP 1 Seconds FROM tblImportTableTest) / 3600 AS hourFrac, CDEC(sj258117951,7) AS A, CDEC(SJ258180012,7) AS B, CDEC(SJ268017501,7) AS C, CDEC(SJ268035021,7) AS D, CDEC(SJ268060513,7) AS E, CDEC(SJ278073012,7) AS F, CDEC(SJ288034051,7) AS G
    FROM tblImportTableTest
    Do you have any control of the source table\ data? Are you able to standardise the structure before you import?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2006
    Posts
    213
    erm no, I just wanna make whats there work. DO u know how to make those queries run on that data from the code ive written?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm - marleyuk - did you write this stuff or did someone else do this for you? Just curious because you are asking something that is way easier than all the stuff in the db.

    Anyway - if you were to make the SQL change I posted and empty your table before the import (personally I would import the data into a staging table and then delete the pre-existing data and then finally INSERT from the newly imported data as this gives you the opportunity to pull out if there are importing errors) then everything should work fine.

    BTW - according to what I have seen in your code the csv file is of a fixed format - correct?
    BTW2 - I would get rid of your DoCmd.SetWarnings calls too. They are unnecessary here and are prone to do far more harm than good. I can't wait for the FAQ to post about the evils (or at best pitfalls) of DoCmd.SetWarnings....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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