Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85

    Question Unanswered: A very basic SQL question

    Dear All

    A very basic question. When you create a report or form it can be based on a query. Is it ok to create a report or form and then in record source property add only SQL statement rather than selecting any query already created.

    Or in other words I have several reports and forms each based on different queries. As I was not very familiar with SQL before, now I am thinking is it possible to copy SQL from each query (in SQL view) and paste it on to data source property of relevant report or form. In this way I can decrease the no of objects (queries) in my database that may have an effect on its performance.

    Any suggestions.

  2. #2
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    I don't see a reason why you cannot but basing the report on a query should make make it open faster (in theory!).

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, basing your report or form on a pre-compiled query will save the time required to come up with an execution plan, but this is does not usually take much time anyway.

    I almost always use an sql statement as my recordsource, unless the statement is something that will be used by more than one object and I am pretty sure the output format of the statement will not change. Otherwise, embedding your sql in the form keeps your database from becoming cluttered with queries (hmmm...what is this query for?) and prevents you from screwing up one form or report if you make a change in the query to support another form or report.

    It's cleaner to keep things encapsulated, so that each form or report contains everything it needs in order to execute.

    blindman

  4. #4
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85

    Smile

    Thank you very much for your reply.

    This is one of my problems. I have about 20 queries in my database. Some of the queries are record soruce of more than one forms or reports and sometimes it becomes confusing when I want to made changes in a query.

    That is why I am thinking now to decrease the no of queries and use SQL for most of them. It would make my database a bit more tidy.

    Would it also be OK to use SQL for crosstab query for a report?

    Thanks

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Using a crosstab query as a source may have its complications. Only on execution time the number of created fields will become known (dynamically) This may cause problems with assigning report/ form fields to fields from query output........................
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Remember that your stored queries are SQL. You can view the code in the SQL pane and cut and paste it as a recordsource for your forms. Also, I assume you know that when you click on the elipses (...) for Data Source on you forms you go to the standard query designer window, but the query you create will not be stored separately from your form or report.

    That said, you can use query-designer within a form's recordsource to develop a cross-tab query and base your report on it. As blom0344 pointed out, the headers on your resulting table could potentially change, invalidating your report.
    If you know ahead of time one the headers will be (months of year, days of week, specific business divisions) you can specify the column headers when you create the crosstab query. You do this in the Column Headings property of whichever field you denote as the column header.
    This will ensure that your reports don't choke, and will also make them load faster. The drawback is that if new data comes in that does not fall into one of the predefined headers it will not appear on the report and you will not know about it.

    blindman

  7. #7
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    Thank you very much for your reply and very useful information.

    So the bottom line is that it is not advisable to use crosstab query as SQL in record source of a form or report. Ok that is fine.

    Your above comment led me to another of my question, hopefully you would not mind answering it here because I think you would certainly have some solution. I have asked this question before on an access forum but could not get any proper answer.

    How can you make the crosstab query to update latest data? In one of my crosstab queries, data in month format appears as column heading, but as new month starts, it does not add the new month automatically, I have to do it manually by going into design view and adding new month from record list and rearranging the widths and heights of columns to accommodate the new month column heading- It is time consuming and there must a way around.

    Looking forward to your reply.

  8. #8
    Join Date
    May 2002
    Location
    London
    Posts
    87
    For the crosstab problem I'd be tempted to show the data in a datasheet view sub-form if that is of for what you need.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Assuming you crosstab query shows months only, and not the month/year combination, you can pre-define all twelve headings in the column properties of your query. All twelve columns will then be displayed regardless of whether they contain data, and you will be able to design your report around it.

    If you are using months/years as a column header, you either predefine everything for the next several years or you redesign your report to group by year.

    Another alternative I have used is to show a rolling twelve-month history by having the column headers be 0, -1, -2, etc indicating the number of months previous to the current month. You will need a formula to calculate this, but then on your report you can have formulas instead of static fields for the column headers that translate these into text values indicating the actual month.

    blindman

  10. #10
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Very nice solution there, blindman, I must try and remember it for the future!

  11. #11
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    blindman

    Thank you very much for your reply. You have certainly given very useful information.

    In my database I have got date column in year-month format. So for time being what I have done is that I have put each year and month (e.g. 200101, 200102, 200103 ...) in column heading of crosstab query till I find some other solution. But still I would have to update new data each time new month starts.

    I am trying to figure out the solution you described in last paragraph. There might be something in it for me.

    Thanks a lot again.

  12. #12
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Well if you had a table with fields
    YearMonth (Text*6 - "200212"
    Item ("Series1"
    Value (123.4)

    The following query takes a paramater of the current month in your format and returns a crosstabe of the last 12 months values with column headings of -11, -10......0

    Code:
    PARAMETERS parCurDte Text (6);
    TRANSFORM Sum(tblTestIT.AValue) AS SumOfAValue
    SELECT [parCurDte] AS CurrentMonth, tblTestIT.Item
    FROM tblTestIT
    WHERE ((((CInt(Left([yearmonth],4))-CInt(Left([parCurDte],4)))*12
    +CInt(Right([yearmonth],2))-CInt(Right([parCurDte],2)))>=-11 
    And ((CInt(Left([yearmonth],4))-CInt(Left([parCurDte],4)))*12
    +CInt(Right([yearmonth],2))-CInt(Right([parCurDte],2)))<=0))
    GROUP BY [parCurDte], tblTestIT.Item
    PIVOT (CInt(Left([yearmonth],4))-CInt(Left([parCurDte],4)))*12
    +CInt(Right([yearmonth],2))-CInt(Right([parCurDte],2));
    Now build your report knowing that the fields will be names "[-11]" to "[0]" and set the column headings to calculated values off the CurrentMonth.
    Last edited by Risky; 07-08-03 at 05:54.

  13. #13
    Join Date
    Mar 2003
    Location
    UK
    Posts
    85
    Thank you very much for this help.

    I will try it later. It seems to be something I am looking for.

    Thanks

Posting Permissions

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