Welcome

Hi,

Welcome to the third edition for 2013. I have not written a Newsletter for a month, but will get back into the fortnightly routine from now on.

Lots of things happening on the home front:

My daughter gave birth to a little baby girl called Alice. I am now a grandmother. Pretty exciting. Thus I have been a bit distracted.

Hope you have all been working hard trying to figure out the amazing yet somehow frustrating program of Microsoft Access. Sometimes it is not Access but rather how do you solve a problem in Access, hopefully these Newsletters help.

Microsoft Access Tips

Tip One: Using an update query to remove unwanted text

Question:

I have this table in my database. Within it looks some thing this:

281-1_2

CELL:326-CDM:1-AP

264-2_2

CELL:382-CDM:2-AP

CELL:218-CDM:10-AP

545-2_1

CELL:246-CDM:5-AP

206-2_1

CELL:522-CDM:1-AP

194-5_1

90-1_1

521-1_1

164-1_1

I need to remove the "CELL:" on the rows that contain the word " CELL: "

So for I have created this:

Mid([Unit Id],6,20)

Now it works, but it's also removed the Numbers on the fist five places in the other fields.

So how do it make it so that it only removes the word " CELL: "?

Answer:

One way to do this is to use an update query.

First make a copy of the table, in case there is a muck up :0)

In the update query, choose the field you wish to update, then in the update to row type the following:

Replace (Nameoffield,"CELL:", "")

Change NameofField to the field name that contains the above information.

Run the query and it will update all the cells accordingly. Thus it is important to make a copy of the table, as there is no going back.

Tip Two: Formatting AutoNumber field

0001 0002 0003 etc

Maybe for an invoice number.

To set this up

With the table in design view and the Autonumber field selected. At the bottom of the window there is a Format property, just type 0000 here.

You may also like to add letter i.e. Inv:

The the format Property would look like this:

"Inv: "0000

Tip Three: Union Query

I have an access database table I call 'Process' with about 10,000 rows describing products and 30 columns identifying wholesale suppliers. Each row includes the wholesale prices from whichever suppliers have that product in stock.

I need to create a query that will compare the wholesale prices of each product, choose the lowest wholesale price in each row, multiply the lowest wholesale price in each row by a factor of say 1.3 as an example to create the retail price and then place that retail price in a separate, linked table I call 'Pricelist'.

When I try to manually copy the prices into the 'Pricelist' I have to cut the list in half because otherwise the program says the list is too long.

Answer:

This is not as straightforward as you would expect it to be. Access is a bit different to Excel, whereas Excel copes well with information in different columns, Access copes better with this information in rows/records.

i.e. for your example there would only be 4 columns (possibly)

ID

Product

WhoesalerName

Price

Then the query to find the lowest price would be quite simple.

I am guessing though, how you have the information is how you want it. It can still be done, but it is not as easy as doing one query. You will need to create a union query - that will display the information as I have outlined, then build a second query to query for the lowest price of a particular product. Do you need the name of the wholesaler? (if yes, I have covered this a bit further down.)

To build the union query:

Queries > New > Design View > Add the table that you wish to query

Close the Add Table window

Then in the query design grid, on the toolbar (I am presuming you are not using 2007) > Queries > SQL Specific > Union

You will be presented with a blank screen. To this screen add the following SQL

SELECT Process.Product, Process.Whole1

FROM Process

UNION SELECT Process.Product, Process.Whole2

FROM Process

UNION SELECT Process.Product, Process.Whole3

FROM Process

etc

Change Whole1, Whole2 etc to the name of your wholesaler field names, you will have to do this for all the wholesalers unfortunately.

If you want the name of the wholesaler to appear, then the SQL would look like this:

Note: change the table name from tblPrices to Process

SELECT tblPrices.Product, tblPrices.Whole1, "Whole1" as Wholesaler

FROM tblPrices

UNION SELECT tblPrices.Product, tblPrices.Whole2, "Whole2" as Wholesaler

FROM tblPrices

UNION SELECT tblPrices.Product, tblPrices.Whole3, "Whole3" as Wholesaler

FROM tblPrices

etc for each of the wholesalers

I just had another thought, by default the prices fieldname will be the name or your first wholesaler, this does not make a lot of sense, so change the name to price i.e.

SELECT tblPrices.Product, tblPrices.Whole1 As Price, "Whole1" as Wholesaler

FROM tblPrices

You will only need to do this for the first one, as all the other will be stored under that field name.

Once you have done this, run the query to make sure it is working oK

Then you would create the query to determine the lowest price for each product.

To do this, create a new query using the SQL query above as the base for the query.

Add the 3 fields to the query.

Choose to group the query, using the 'Totals' button Then in the 'Total Row' choose the following

Products = Group By

Price = Min

Wholesaler = First

This should display the lowest price for each product, with the associated Wholesaler's name.

Cheers