Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Hertfordshire, UK
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals Default (Access 2000)

    Hi, Does anyone know if it's possible to change the default for the Totals Row in a select query to show 'Sum' instead of 'Group By'???? I need to do it by default as the query is a reusable one which sums data from a different table each time it's used.
    Thanks,
    Gill.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals Default (Access 2000)

    The same wish also occurred to me, but I have never been able to find how to do this.

    Do the tables whose data you want to sum have the same structure?
    If so, adapting the query shouldn't be difficult. Assign an alias for the table in the Design view of the query; then you only have to change the table name in one place in SQL view.
    If not, what is the advantage of reusing the query?

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Hertfordshire, UK
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Default (Access 2000)

    Hi Hans,
    Thanks for your reply.
    Yes the tables are all the same structure. The summing query is just one step in a long procedure for appending sales figures from 110 tables, one after the other. Hence I have a macro which simply opens the query ready for me to add in whichever table I've just imported to work on, then sum and continue my procedure.
    I don't understand your alias option - I'm not into SQL in any big way, but I will investigate myself.
    Thanks,
    Gill.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals Default (Access 2000)

    Concerning an alias:
    <UL><LI>Open a query in design view.
    <LI>Open the Properties window (View/Properties).
    <LI>Select the field list of the table in the upper pane of the query design window.
    <LI>The first item in the Properties window is the alias. By default, this is the table name.
    <LI>Change this to something simple , like T.
    <LI>Now switch to the SQL view of the query. You see something like

    SELECT T.Field1, Sum(T.Field2) AS SumOfField2, Sum(T.Field3) AS SumOfField3
    FROM Table1 AS T
    GROUP BY T.Field1;

    Note that the actual table name (Table1 in this example) occurs only once; in the rest of the text it is referred to by its alias T.
    <LI>If you want to base the query on a different table, you only need to replace the single occurrence of the table name. There is no need to do anything with the fields in the query.[/list]

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Hertfordshire, UK
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Default (Access 2000)

    Understood!
    Many thanks, I'll give it a try.
    Gill.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Totals Default (Access 2000)

    Seems like you go to a lot of trouble to run this.
    Do you intend to run this more than once?
    Could you automate this procedure rather than typing in the table name for each select?
    Just curious.
    Cheers,
    Pat

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Location
    Hertfordshire, UK
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Default (Access 2000)

    I found the suggestion didn't work because I'm working on a new table each time around, the query loses all the filed names anyway. So I'm back at the drawing board.
    The task is simply to bring in a table which contains records of figures for months of the year. I then want to sum the months so I have a check figure for the rest of my procedure. Each new table has a different name (which is necessary), so I can't save the query, other than as an 'empty' one.
    Any other suggestions which don't involve programming would be useful.
    Gill.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals Default (Access 2000)

    It's a pity you don't want to use programming - it could save you a lot of time in the end.

    Do you want to add the new table to (possibly many) tables used in the query? In that case, I don't understand what you're doing.

    Or do you want to replace the name of one table used in the query? In that case, the method described in my previous post should work if the table used the last time still exists. If you've deleted that table, Access messes up the SQL string because it can't find the fields any more.

    To get around this, you can save the SQL string when you have a working query. Open the query in SQL view and copy the SQL string. You can paste it into a table created for that purpose, or into a text file, or into a Word document. Whatever works best for you. Use an alias in the SQL string, as described in my previous post.

    Each time you import a new table, copy and paste the saved SQL string into the SQL view of a query, and substitute the new table name. It should work, if the table has the same field names each time. But I'd say it would soon get tedious...

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Totals Default (Access 2000)

    Are the table names fixed, if so why dont you setup 3 union queries (you can specify a maximum of 49 tables in a union query in Access97), then setup a query to union query those 3 union queries, then a further query to sum that query. Sounds complex, but it's really quite straight forward.

    Doing it this way does not involve any programming.

    Cheers,
    Pat

  10. #10
    New Lounger
    Join Date
    Apr 2002
    Location
    Hertfordshire, UK
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Default (Access 2000)

    Thanks for the posts. The SQL string option sounds like it will work, but I think would take longer set-up than I do now, as I'd have to do it for each table.
    The union query idea might work as the table names are fixed (or should be), I haven't tried any union queries - so that could be interesting. Thanks again.
    Gill.

Posting Permissions

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