Unofficial Access Stuff

Use your pdf converter to make your pdf files easy! You can now buy software that makes converting pdf to doc possible! Did you know you can even convert pdf to word?

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Host your Web site with PureHost!

Thursday, January 25, 2018 – 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: General, Queries, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

Saturday, January 20, 2018 – 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 the Access Relationships window.

See all Topics access
Labels: General, Queries, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:16 AM

Comments: Post a Comment

Sunday, January 29, 2017 – Permalink –

Drag Query to Word

Drop in
Rather than duplicate the effort, an Access query can be dragged into a Word document.

TechRepublic.com

See all Topics access
Labels: General, Queries, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:15 AM

Comments: Post a Comment

Saturday, August 20, 2016 – 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 access
Labels: Combo, Queries, Reference, Tips, Tutorials

<Doug Klippert@ 3:12 AM

Comments: Post a Comment

Thursday, July 07, 2016 – Permalink –

Hiding Duplicates in Query Results

Once is enough

It's easy to hide duplicate entries when you run a query, even though Access doesn't go out of its way to call attention to this ability.

Set up a query as usual using the design grid.

Choose View>Properties from the menu bar to display the Query Properties dialog box.

Change the Unique Values property to Yes.

Access displays unique records based on each field returned by the query.

See all Topics access
Labels: Customize, General, Queries, Reference, Tips, Tutorials

<Doug Klippert@ 3:53 AM

Comments: Post a Comment

Sunday, February 07, 2016 – Permalink –

Crosstab Query Column Headings

Using Month Numbers

If you display a crosstab query as a datasheet, consider using a month's or day's number as a column heading instead of a text abbreviation (e.g., 1 instead of Jan or January, or 2 instead of Mon).

Text abbreviations are sorted alphabetically. Apr appears before Feb, Mon appears before Sun, etc. Number representations will sort in their proper order.

See all Topics access
Labels: General, Queries, Reference, Tips, Tutorials

<Doug Klippert@ 3:57 AM

Comments: Post a Comment

Wednesday, January 06, 2016 – Permalink –

Toggle Object Views

Use the keyboard

When you are putting a database together you often want to switch between views of Access objects to see the changes.

For instance, you'll switch the view to examine a Table in Design view and then back to data view. It is can be faster to switch between views using keyboard shortcuts, rather than the View menu.

You can cycle through the views of an open object using the period

Ctrl + .

or comma

Ctrl + ,

shortcut keys.
These shortcuts can be used with tables, queries, forms, reports, and data access pages.

See all Topics access
Labels: Forms, General, Queries, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:42 AM

Comments: Post a Comment

Monday, December 14, 2015 – Permalink –

Parameter Queries Deux

Another look at parameters
The ability to use dynamic criteria in a Query makes Access even more valuable.

Access Parameter Query Tutorial video that walks the viewer gently through the process.

(Parameter queries are also referenced here:
Parameter v. Form)

How to create a parameter query

Using Parameter Queries

See all Topics access
Labels: General, Queries, Tips, Tutorials

<Doug Klippert@ 3:38 AM

Comments: Post a Comment

Saturday, October 03, 2015 – Permalink –

Alphabetize by One Field or the Other

If one is missing, use the other

Let's say you have a database that has the company name and a contacts name.

In some cases the CompanyName field is empty. If that happens, you want to continue the alpha sort using the contact's LastName.

To do this, you need to create an extra query field to provide the sort, using the NZ() function to replace the contents of one field for null values in another.

(Nz(variant, [valueifnull])

Select the Queries, and then click "Create query in Design view".

Choose the table you want to sort, click Add.

Click Close.

Drag down all the fields you want to display in your form, including the two separate fields you want to alphabetize.

Insert a new column on the left side of the QBE grid.

In the Field cell, enter the expression
NZ([CompanyName],[LastName])

Select Ascending for the Sort option.

When you run the query, if CompanyName is null (empty — no entry), the NZ() function uses the contents in LastName instead.

Here's another way to do it:

See all Topics access
Labels: Functions, General, Properties, Queries, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:46 AM

Comments: Post a Comment

Friday, May 01, 2015 – Permalink –

Import Queries

As Tables

If you want to use the results of a query, and you don't need to update the underlying tables, you don't have to import unnecessary data.

You can import the query as a new table.

Select File>Get External Data Import from the menu bar.
(External Data tab, Import in 2007+)

Select the appropriate database and click Import.

Select the queries you want to import on the Import Objects dialog box's Queries sheet.

Next, click the Options >> button and select the As Tables option button on the Import Queries panel.

Finally, click OK

Access processes the queries and saves the results as a table with the same name as the original query.

See all Topics access
Labels: General, Queries, Reference, Shortcuts, Tips, Tutorials

<Doug Klippert@ 3:32 AM

Comments: Post a Comment

Thursday, March 19, 2015 – Permalink –

Samples Queries, Reports, Forms

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, General, Queries, Reference, Reports, Samples, Templates, Tutorials

<Doug Klippert@ 3:50 AM

Comments: Post a Comment