Posts Tagged ‘Queries’

Special Characters are a Pain in the Asterisk

September 7th, 2009

There’s an old saying that goes “You learn something new every day”. That’s fine, but it’s embarrassing when what you learned that day is something that you should have learned years ago.

I recently was working with Access when I ran into a situation I’ve never run into before. As I did some research, it became clear that this “problem” I was having seemed to be common knowledge.

It revolved around special characters. Since we all have things to do today, I’ll simplify the scenario to a basic example.

. » More: Special Characters are a Pain in the Asterisk

Adding Data Bars to Access Queries

September 2nd, 2009

Today, the old schedule is a bit tight, so no time for a full on post.  I’ll be recycling old some material today.  The crunchy environmentalists keep telling me that recycling is good.  So think of today’s post as Earth Day, without the…..um….earth.

.A few months back, I came up with an Access visualization trick while thinking about building dashboards in Access.

I’ve put together a video that shows you how to build data bars in an Access query.  This technique will work with Access forms and reports too.  » More: Adding Data Bars to Access Queries

Dynamically Adding Primary Keys

August 18th, 2009

For many of us, Access serves as an easy to use ETL (extract, transform, load) tool. That is, Access allows us to extract data from many sources, then reformat and cleanse that data into consolidated tables.

Many of us also automate our ETL processes with the use of macros that fire a series of queries. This typically works quite well in most cases.

There are, however, instances where our ETL process requires us to add primary keys to temporary tables in order to keep data normalized during processing.

  » More: Dynamically Adding Primary Keys

Replacing Large Amounts of Data in Access

June 25th, 2009

For small tables, the Find-and-Replace functionality in Access does provide a quick and easy way to alter data and save time.

But for large tables, the Find-and-Replace functionality essentially crashes and burns, able to process only a few thousand records at a time.

If you do have large tables that choke the Find-and-Replace functionality in Access, one alternative is the REPLACE function.

» More: Replacing Large Amounts of Data in Access