Solved

Query Formatting in Access 2010

Posted on 2011-02-18
13
2,021 Views
Last Modified: 2012-05-11
I'm starting to use Access 2010 and I have a question about the new formatting options in queries.  I setup a query with the layout I want.  I want to show totals, and I setup the color to highlight every other line in datasheet view and some other little things.

My question is, how can I save this as a default for all other queries.  I need to apply it for all new queries, as well as any existing ones.

Thanks for the help.
0
Comment
Question by:broomee9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
13 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34929342
Does this Have to be done in Datasheet view?

You did not post any details on how you:
"setup the color to highlight every other line in datasheet view and some other little things."
...so I can't be sure what you are asking for is possible.

For example, Access can have default template Forms or Report, ...not queries.

Depending on what exactly you did, AFAICT, this can all be done in a report.
Create a new blank Report and set all your options/settings.
Then name the report:  rptCustomTemplate1
Then in the Database Options-->Object Designers-->Form/Report Design View-->Report Template
Type in: rptCustomTemplate1

Compact/repair the DB and create a new report in design view.
It will have your custom settings.

JeffCoachman
0
 
LVL 24

Author Comment

by:broomee9
ID: 34929647
Thanks for your reply Jeff.

Yes, it's the Datasheet view for the query, not on a report.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34953922
Again, I don't know of any way to create a "Design template" of a query.

Will you be posting the specifics on:
    "setup the color to highlight every other line in datasheet view and some other little things."
... I am still at a loss as to what this means specifcally

I don't know how re-wording the question will help....
But I leave that to you.

Jeff
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 24

Author Comment

by:broomee9
ID: 34954162
If you have Access 2010 go into Datasheet view on a select query.  Select the Home tab and you will see the formatting options I mentioned.  Once I make all the changes I want, my actual question is how to make it so these changes will be seen in all select queries in datasheet view ("My question is, how can I save this as a default for all other queries.").

>> Will you be posting the specifics on:
    "setup the color to highlight every other line in datasheet view and some other little things."
... I am still at a loss as to what this means specifcally
See attached screenshot.  I already know how to do this, so this is not what I'm asking for help with.  How do I save these changes for future use is what I'm asking for.

>>I don't know how re-wording the question will help....
It didn't seem like you understood what I was talking about, so perhaps re-wording it would be more clear.

If you have any suggestions, please let me know.  I thought this was a dead question though, so that's why I was going to delete it.  But if you know what I'm talking about now and know how to do it, then I'd be happy to hear it.
Snapshot.jpg
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 150 total points
ID: 34954771
Thanks for the clarifications.

1. <How do I save these changes for future use.>
I just double-checked again in Access 2010 to be sure.
I don't see a way for this to be done.

2. I guess my question was why this had to be in a query specifically.
Again, while there is no direct way to saye a query template, although there is a way to save a form or report template.
You can set up a form to display in Datasheet view and do the Alternate row shading.

However the "Total" would be tricky.
A datasheet view form wil not allow for totals.
To do this you will need to create a "Tabular/Continuous" form, then put a control in the form footer with a controlsource of: =Sum([YourField])
...but again, this requires a differnt form design, and there is no way to make a "template" that might sumarize fields.

So again, if your ultimate question is:
<How do I save these changes for future use>
Unfortunately, the answer appeares to be: You can't

JeffCoachman
0
 
LVL 24

Author Comment

by:broomee9
ID: 34955209
>>1. I don't see a way for this to be done.
I didn't see a way either, hence the question :-)

>>2. I guess my question was why this had to be in a query specifically.
I search through the data in my tables a lot for ad hoc questions by users.  It's very handy to have the totals added (not necessarily for a specific field, but just added to the bottom, so I can select as necessary) and for every other row to be highlighted.  My tables have millions of records, so it's nice to differentiate between the lines, especially when there are many many columns in each table.  (Note: I did not design the tables, they're linked tables to an ODBC using SQL Server).

I didn't want to use a form, because I'm looking for something quick and dirty, but I didn't want to have to do it every single time I look at any query in datasheet view.

If it's alright with you, I'll leave this open for a day or two and see if any other experts pipe in, if not I guess the answer is it can't be done, and I'll give you the points for your comment http:34954771

Thanks again for taking a look.
0
 
LVL 24

Author Comment

by:broomee9
ID: 34955223
One change:
I search through the data in my tables

Should be:
I search through the data in my tables by using queries so I can aggregate the data
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34957715
<If it's alright with you, I'll leave this open for a day or two and see if any other experts pipe in>
Sure, no problem.

;-)

Jeff
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34959832
broomee9,

If I'm reading your question right, you are simply trying to alternate colors (highlighting) in your datasheet views of queries, tables, etc.

This works in Access 2007, and I imagine that Access 2010 is a slight variation:

- Click the office 'blob' in the upper left-hand corner of the Access window
- Click 'Access options'
- Goto the Datasheet options
- Change the "Alternate Background Color" setting to whatever you want your highlight color to be.

This persists (ie it is 'saved'), and it applies to all datasheet views in your database.
0
 
LVL 24

Author Comment

by:broomee9
ID: 34960887
mbizup - I took a snapshot of the Datasheet section and I don't see the Alternate Background Color.
Screenshot.JPG
0
 
LVL 61

Accepted Solution

by:
mbizup earned 350 total points
ID: 35033783
broomee9,

Sorry about the delayed response...

You're absolutely right - it appears that the Access 2010 upgrade has reduced the flexibility in datasheet formatting that Access 2007 has.

You can however still highlight alternating rows by selecting 'Automatic' as shown in the attached screenshot.

This will highlight every other row, based on whatever the color scheme for the database is overall.  And unlike actually choosing a color, if you choose 'automatic' highlighting it persists ('saves') from query to query, and datasheet views of tables, etc throughout the current database (but I think you are stuck with the default theme highlighting color).

I think this is as close as it gets to what you are trying to do.

Datasheet-Highlighting.PNG
0
 
LVL 24

Author Closing Comment

by:broomee9
ID: 35070193
OK, thanks for the explanation.  I split the points mostly because both answers were it can't be done but mbizup, your answer showed at least one piece that can be done.

Thank you both.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access query 16 61
Combo box question 6 55
Error in Visual Project 10 49
Moving database to a shared server 7 32
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question