Unofficial Access Stuff

Book

Suggestions

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Saturday, May 03, 2008 – Permalink –
Sample Queries, Forms, Reports
Examples to part out

This sample queries database contains examples of useful database queries, including the crosstab query, the union query , and the join query

Sample: query topics database

Here are some other sample databases. They are all for Access 2000, but the installed base is predominantly in that format. Access 2000 is also the default format for Access 2002 and 2003.
Sample Access databases that you can download and adapt

Database of Access 2000 sample forms
The sample forms in this database demonstrate a variety of form types and techniques, including how to manipulate data, use controls, and create undo and redo operations.

Some forms include:

Bring a subtotal from a subform to a main form
Create a running sum
Create a stopwatch form
Display line numbers on subform records
Fill current record with data from previous record automatically
Hide the combo box drop-down arrow
Simulate drag-and-drop capabilities

Database of Access 2000 sample reports
The sample reports in this database demonstrate a number of techniques, including how to shade every other row or every nth row in a report, how to create a table of contents or an index for a report, and how to create a top 10 report.

See all Topics

access
Labels: Forms, Queries, Reports

<Doug Klippert@ 6:15 AM

Comments: Post a Comment

Sunday, April 06, 2008 – Permalink –
Add a Table
Drag drop trick

While working with a query in Design view, you may find that you need to add a table or query. The "book" way to do it is to click the Show Table toolbar button, drag the appropriate objects from the list, and then close the dialog box.
There is another way to do this.

Drag the table or query object's icon from the Database window/Navigation pane directly to the top half of the query design grid.

You can also use this technique in Access's Relationships window

See all Topics

access
Labels: Queries, Tables, Tips

<Doug Klippert@ 6:50 AM

Comments: Post a Comment

Wednesday, December 19, 2007 – Permalink –
Quickly Query Table Names
Change by code

If you've ever developed a dozen or more complex queries, then had to change one of the table names, you know how frustrating it can be to all but rebuild the queries in the Query Design view grid by changing the table name in each cell.

One quick alternative is to choose View >SQL View while the query is open and then cut and paste all the SQL code into Word.

Next, do a Find and Replace, changing all the instances of the old table name to the new table name.

Finally, copy and paste the SQL statements back into the SQL view of your Access queries.

When you go back to the QBE, the new table will replace the old one.

See all Topics

access
Labels: Queries, SQL

<Doug Klippert@ 4:00 AM

Comments: Post a Comment

Tuesday, December 11, 2007 – Permalink –
Add Objects to the Query Grid
Easy additions

If you need to add a table or query to a query you're building in Design view, you most likely click the Show Table button, drag the appropriate objects from the resulting dialog box, and then close the dialog box.

However, there's a much easier way to do this.

Simply drag the table or query object's icon directly to the gray background of the query design grid. This same technique also works with Access's Relationships window.

See all Topics

access
Labels: Queries, Shortcuts

<Doug Klippert@ 8:04 AM

Comments: Post a Comment

Friday, November 09, 2007 – Permalink –
Null Parameter
Show something

If a user doesn't specify a parameter value, you can use a wildcard with the parameter in the format
Like [Enter Name] & "*"

The problem with this is that the query will return records that partially match the criteria.

For instance, if users searching for records based on last name enter a parameter value of "Smith" they'll also get the records for Smithers, Smithfield and Smithson.

Another problem is that the parameter query will ignore any records where the field being searched contains a Null value when you try to return the entire recordset with a blank parameter.

To fix this, set up a query to limit responses to explicit parameter entries, but still allow users to return all records by leaving the parameter blank.

If you're searching for LastName, open the query design grid and add LastName to it.

In the Criteria row for the field, enter the parameter prompt
[Enter Name]

Then, in the next blank column of the design grid, enter the same parameter (everything between and including the square brackets) in the Field text box.

Finally, in the Or row, enter the criteria Is Null .

If you're using any additional criteria for other fields, make sure to copy that criteria to the Or line as well.

See all Topics

access
Labels: Queries

<Doug Klippert@ 6:21 AM

Comments: Post a Comment

Thursday, July 26, 2007 – Permalink –
Use a Table Alias
to change names in a query

When you need to change the table name referenced in an existing Access query, it can be a pain. This is especially true if the query contains a large number of fields.

You typically might use an alias when you need to relate a table to itself or tables have long or unwieldy names.

If you make a practice of always using aliases in your queries, you can easily change which table is used by changing the one occurrence of the original table name in the query's FROM clause.
To set a table alias in the query's Design view,
Right-click on the table field list and choose Properties.
Then, enter the alias name you want to use in the Alias text box.

See all Topics

access
Labels: Queries, Tables

<Doug Klippert@ 7:36 AM

Comments: Post a Comment

Thursday, March 15, 2007 – Permalink –
SQL statements Automatically
Hidden code

If you're having trouble figuring out a complex SQL statement for use in code, you may be able to simplify the process by first setting it up the query you want in the query design grid.

Once you've got it configured correctly, choose View >SQL view to reveal the underlying SQL statement, which you can copy and paste into your code.

You may have to make minor modifications, but this technique often eliminates much of the hassle of manually constructing SQL statements.

You can also tweak the underlying SQL code to adjust your Query. This code can also be copied and reused in other Queries after a little customizing.

See all Topics

access
Labels: Queries, SQL

<Doug Klippert@ 6:23 AM

Comments: Post a Comment

Wednesday, January 10, 2007 – Permalink –
View Related Fields
The whole thing

When designing a query, you'll often find the need to temporarily view fields beyond the ones you want shown in the final result.

To do so, you probably drag the relevant fields to the design grid and then delete them when you're done. However, there's an easier way to view the data that you typically want excluded in the query.

To do so:
Open the query in Design view.
Display the query's Properties sheet
Set the Output All Fields property setting to Yes
Run the query

Data from all of the underlying tables' fields will be displayed in addition to the fields you specified in the QBE grid.

To change the query so that it only displays the fields explicitly selected in the QBE grid, simply reset the Output All Fields property to No.

See all Topics
Labels: Queries

<Doug Klippert@ 4:51 AM

Comments: Post a Comment

Sunday, December 17, 2006 – Permalink –
Combo Box Queries
How to

Parameter queries add flexibility to filtering records in a database. To make it easy, take a look at this approach from Martin Green's Office Tips site:

Drop down box in a Parameter Query

Build a dialog box with as many combo boxes as you need.
Design a query to read its criteria from the information on the dialog box.
Create a macro or visual basic procedure to tell them both what to do.

Also
Base Combo Box on Parameter Query to Filter Values

See all Topics
Labels: Combo, Queries

<Doug Klippert@ 5:23 AM

Comments: Post a Comment