Microsoft Access Tips, Issue #003 (March 27 2013)
March 27, 2013
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
I have this table in my database. Within it looks some thing this:
I need to remove the "CELL:" on the rows that contain the word " CELL: "
So for I have created this:
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: "?
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
The AutoNumber field is great, but sometimes just plain numbers are just not what you are looking for i.e. 1,2,3, you may instead wish to display the autonumber as:
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:
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.
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)
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
UNION SELECT Process.Product, Process.Whole2
UNION SELECT Process.Product, Process.Whole3
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
UNION SELECT tblPrices.Product, tblPrices.Whole2, "Whole2" as Wholesaler
UNION SELECT tblPrices.Product, tblPrices.Whole3, "Whole3" as Wholesaler
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
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.
That is all for this newsletter. Talk to you all in a fortnight.