Official Softs

Rumours circulating that Minesweeper was not to be included in Windows Vista have been dispelled with a screenshot (see image).Office Professional 2010 is great!

Minesweeper has been a favourite game among Windows users for as long as anyone can remember. Many will breathe a sigh of relief that a new, improved version will sit on their Windows Vista machines next year. Office 2010 –save your time and save your money.

Do you have a column full of numbers in Excel 2007 that you would like to sort from highest to lowest, or biggest to smallest? Microsoft Office 2007 is powerfull.

To sort from high to low in Excel 2007:

1) Select the cells you want to sort

2) Click on the “Data” tab of the Ribbon.IS Windows 7 Professional better than Windows 7 Home Premium ?

3) Look for the “Sort & Filter” group

4) Click on the “Sort Z to A” button – it is the one that has a Z on top of an A with an arrow pointing down.Photoshop CS4 is so magic!

Creating a CheckBox

Until now, all the posts have focussed on creating buttons in a variety of styles, shapes and sizes. Today, we’ll take a look at a simple example of a checkbox. What we’ll do is create a single checkbox on a custom tab.Ms office 2007 is so Charismatic.

The XML to create a checkbox is once again similar to what we’ve seen. One big difference is that we cannot use a picture, of course, since the actual check box will take it’s place. The XML for our test is as follows:

 

Office 2007 professionaland Office 2007 ultimate are so powerfull. This will create a custom tab called “XLG”, and on it will be the lonely control as shown below:

Now, the checkbox is there, but we need to be able to figure out if it is clicked. If you recall Part 1 of this series, we set up a routine called “CallControl” (specified in our XML), that started like this:

 

To determine the value of the checkbox, however, we need a slightly expanded VBA routine. We still use the basic framework as above, but we expand it to also pick up the “Pressed” portion as shown below:

 

Again, the code above goes in a standard module. Once you have it there, try clicking the checkbox on the Ribbon, and notice that it will feed back if it is checked or not.

Microsoft Office 2007 is powerfull.

This example is educational only, of course. In a real setting you would replace the message boxes with code that you would wish to execute based on the user’s selection.

In the next part, we will look at how to toggle the values of the checkbox via code, and have it update the Ribbon automatically… stay tuned! IS Windows 7 Professional better than Windows 7 Home Premium ?

Today’s author, Dan Battagin, a Program Manager on the Excel team, talks about joining two tables in Excel (a.k.a. returning multiple rows for VLOOKUP). Ms office 2007 is so Charismatic.

Today, we’ll take a look at the VLOOKUP function, and work out a way to get around its major drawback – it returns only a single value that matches the lookup.

If you’re like me, there are times when you have a big table of data which is pretty well normalized, and you want to pull all of the rows out of that table that match a certain criteria. Well, it’s easy to get the first match in that table by using VLOOKUP (in fact, it’s easier than ever with the new structured references in Excel 2007):

Figure 1: VLOOKUP is an easy way to return a value out of the first row that contains a customer name.

As you can see above, with structured references in Excel 2007, not only is VLOOKUP easy to use, but it’s actually pretty readable – we’re matching the value of A9 in the first column of Table1 and returning the value from the 3^rd column ($25.00). That’s really nice, and very useful (especially if you use VLOOKUP as part of a calculated column in a table) but it does have the drawback that it doesn’t ever allow us to return any of the values for the second “Dan” in the list. And, if you’re like me, this can be pretty frustrating. I’m constantly working with lists of Excel bugs (yep, we’ve got some bugs), materials lists for woodworking projects that I’m currently involved in, price lists for whatever current home remodel I’m working on, etc. where I really need to see more than just that first row.Office 2007 professionaland Office 2007 ultimate are so powerfull.

So today, let’s look at how we can actually make this work, and retrieve all of the values from a table that match a certain criteria.

Before we get started on the solution, let’s lay out the data that we’re going to use. Since it’s always a pain to come up with data sets for work examples like this, I’m going to use an old standby – the Northwind Sample Database that ships with pretty much every version of Access. Of course, this being Excel, I’m going to copy the portion of the data that I need into worksheets. (Yeah, yeah, I’m a database guy too, and I know we could do the joins in SQL, but that’s not always the case, so bear with me.) OK, so here’s the data I’m going to copy onto sheets in Excel:

Figure 2: The “Customer” table, inserted on a sheet called Customers, and named “tblCustomers” for easy referencing in the future.	Figure 3: The “Orders” table, inserted on a sheet called Orders, and named “tblOrders” for easy referencing in the future.	Figure 4: The “OrderDetails” table, inserted on a sheet called Details, and named “tblDetails” for easy referencing in the future.

So you don’t have to do this yourself, I’ve also made a copy of the spreadsheet available so you can just follow along (see the attachments at the bottom of this post). Of course, if you want to walk through this yourself, go right ahead, that’s a pretty cool compliment.QuickBooks 2010 is so Helpful!

So now that we’ve got all of our source data, I want to put together the actual Master-Detail form. Nothing too fancy, but basically I want to select the Order Number that I want to look at, and see some details about that order (who ordered it, order status, etc.) as well as all of the items that are part of that order (item name, price, quantity, etc.). Pretty standard stuff. Here’s our target end result:

Figure 5: The master-details form where I can pick an order number and see all of the details.

In the available workbook, you’ll find this form put together already, but if you’re doing it yourself, here are the steps you’ll want to take (note that these steps skip the formatting of this form, since not everyone likes green the way I do):

1. Enter the following static strings in the sheet:
   1. B1: Order Number
   2. C3: Order Information
   3. C4: Customer
   4. F4: Order Date
   5. F5: Status
   6. F6: Salesperson
   7. F7: Ship Date
   8. C10: Order Details
   9. C11: Product
   10. D11: Quantity
   11. E11: Unit Price
   12. F11: Discount
   13. G11: Total Price
   14. H11: Status ID
2. Create a Named Range that includes all of your Order IDs, which we’ll use to create the Order ID drop down using Data Validation. To do this, click on the Formulas tab | Define Name and enter:
   1. Name: OrderIds
   2. Refers to: =tblOrders[ID]

      Tip: Data Validation cannot refer to ranges on a different sheet than the one that contains the validation, but by using a named range, you can get values from another sheet (and in this case, get a dynamic list of values using the structured reference to get an entire table column, even if new values are added to it in the future)

3. Select D1 and name it rngOrderId (we’ll use this later).
4. With D1 still selected, click on the Data tab | Data Validation and create a new List type validation with Source: “=OrderIds”. Boom, now you have your drop down.Photoshop CS4 is so magic!

5. OK, now we’re going to create some “normal” VLOOKUPS as we discussed above already, with a bit of a twist to make them a bit more robust: we’re going to use the MATCH function instead of a hard-coded column number for the value we want to retrieve from the source table.

6. Select C5 and enter the following function:
   =VLOOKUP(rngOrderId, tblOrders, MATCH(C4, tblOrders[#Headers], 0), FALSE)

   Here we’ve said find the Order ID that I’ve selected in the Orders table, and return the value from the column that MATCHes Customer.Acrobat 9 is so useful!

7. Just like C5, we’re going to setup the rest of the normal VLOOKUP functions to return metadata about the order – this is all the “Master” data in this Master-Detail form:
   1. C6: =VLOOKUP($C$5,tblCustomers,MATCH(”Address”,tblCustomers[#Headers],0), FALSE)
   2. C7: =VLOOKUP($C$5, tblCustomers, MATCH(”City”,tblCustomers[#Headers],0), FALSE) & “, ” & VLOOKUP($C$5, tblCustomers, MATCH(”State”,tblCustomers[#Headers],0), FALSE) & ” ” & VLOOKUP($C$5, tblCustomers, MATCH(”Zip”,tblCustomers[#Headers],0), FALSE)
   3. H4: =VLOOKUP(rngOrderId, tblOrders, MATCH(F4, tblOrders[#Headers], 0), FALSE)
   4. H5: =VLOOKUP(rngOrderId, tblOrders, MATCH(F5, tblOrders[#Headers], 0), FALSE)
   5. H6: =VLOOKUP(rngOrderId, tblOrders, MATCH(F6, tblOrders[#Headers], 0), FALSE)
   6. H7: =VLOOKUP(rngOrderId, tblOrders, MATCH(F7, tblOrders[#Headers], 0), FALSE)

Figure 6: The completed Master section of the Master-Details form.

Right, so that’s not too bad, and get’s us something that’s pretty robust, even if we add additional data to our source data tables – just as long as we keep the headings in our form matching the column headings in the tables. OK, so now let’s move on to the “Details” part of the form – and this is where we’ll get to find a solution to the VLOOKUP limitation of only returning a single value.

Before we build up the actual solution, let’s talk about some of the elements of the formula we’re going to create:

1. We’re not actually going to use VLOOKUP! Since we need to return multiple items, what we really need is a way to return an array (list) of values, and VLOOKUP just doesn’t allow for that. Instead, we’ll use the INDEX function to return the value at a specific row and column intersection. Dreamweaver CS4 is very easy-to-use!
2. We need a way to designate which item in the array we want to show in the cell, and we’ll use a combination of the SMALL and ROW functions to accomplish that.
3. We want this to be robust in the same way that we made our VLOOKUP function robust – that is, we want to be sure that if we add additional columns to our source data, that these functions don’t break. Just like with the VLOOKUP solution, we’ll use MATCH to ensure this.

   Tip: A nice side effect of what we’re going to do here is that this formula is totally fillable within the “Details” portion of the form, which makes it pretty easy to work with/edit.
   

4. Lastly, we want to have a bit of error handling – specifically, we’ll use IFERROR (a new function in Excel 2007) to ensure that we just show a “blank” cell if there is an error in the calculation.

OK, so without further ado, let’s see that function, as it exists in cell C12 (note that when you enter this function, you enter it without the curly braces, and you press Ctrl+Shift+Enter to commit the cell, which adds the curly braces, making it an array function):

Figure 7: Array function used to return the Nth item that matches a specific value.

That looks pretty complex, so let’s take a look at what’s actually going on here.  I’ve pretty printed sections of the formula for easier reading, from the inside out, since that’s how Excel will ultimately calculate it:

{
=IFERROR(
          INDEX(tblDetails,
                    SMALL(
                              IF(tblDetails[Order ID]=rngOrderId,
                                   ROW(tblDetails[Order ID])-ROW(tblDetails[#Headers])
                              ),
                              ROW(1:1)
                    ),
                    MATCH(C$11, tblDetails[#Headers], 0)
          ),
          ”")
}

Let’s look at the SMALL function first.  Small works by taking an array (list) of values, and returns the Nth smallest value from that list. 

                    SMALL(
                              IF(tblDetails[Order ID]=rngOrderId,
                                   ROW(tblDetails[Order ID])-ROW(tblDetails[#Headers])
                              ),
                              ROW(1:1)
                    ),

In this case, the array of values is determined by the IF function.  Specifically, IF the Order ID for a row in the Details table equals the Order ID I’ve selected in the drop down, then add the row number of that row (minus the row number of the heading row, in case the table doesn’t start in row 1) to the array of values.  And, once the full Details table is analyzed in this way, return the 1st item in the array – ROW(1:1) returns 1.

Now, you’ll see a couple tricks that we’ve used here:

1. Instead of ROW(1:1) to return the first item in the array, we could have just used the value 1 (or the value 4 if we wanted the 4th item in the array).  However, by using the ROW function, Excel will adjust the formula for us as we fill it down a range of cells, so the next row will contain ROW(2:2) for example.
2. We used structured referencing to make the formula more robust.  We could have had cell references for the tblDetails[Order ID] and for the [#Headers], etc. but those would not have adjusted as the source table was modified.  By using structured referencing, we have a pretty solid solution here. (and more readable)

OK, let’s move on to the INDEX function next.  INDEX works by taking a 2d array (table) and returns the value that is at the specified row and column position in that array.

          INDEX(tblDetails,
                    SMALL(
                              IF(tblDetails[Order ID]=rngOrderId,
                                   ROW(tblDetails[Order ID])-ROW(tblDetails[#Headers])
                              ),
                              ROW(1:1)
                    ),
                    MATCH(C$11, tblDetails[#Headers], 0)
          ),

In this case, we know that the entire SMALL function returns the ROW(Nth) value in the list of Order Details that match the Order ID that I entered – so in this case the 1st value, which is the row number that we want to retrieve a column out of in the Details table.  The only thing left then is to specify the column number that we want to retrieve, which as we learned earlier is what MATCH is used for – in this case, taking the value in C11 and finding the column in the Details table that has the same name (Product in this case).

Right, so we see a couple more tricks here:

1. To specify the 2d array (table of data), we are again using a structured reference – tblDetails – as opposed to defining the array using a range reference (A1:G50 or whatever). This gives us robustness.
2. Ditto with the MATCH function. We could have used the column number explicitly, but by using MATCH, we can much more easily make changes to the source table without having to worry about whether it will break the rest of our model.
3. Lastly, you’ll see that with the MATCH function, we’ve made it so that C$11 will adjust across, but will not adjust down – that will come in handy as we want to fill this formula to create our details list.

And with that, all we’re left with is the IFERROR function and the array function designators. IFERROR is pretty simple – it basically says: if an error occurs while calculating the stuff I contain, replace the error value with an empty string (”"). The array function designators (curly braces) are what allow INDEX and SMALL to work over the entire Details table range.

OK, so now that we’ve discussed the formula in C12, let’s quickly finish up the form (and watch the magic of our robust formulas at work). With C12 selected, grab the little fill handle in the bottom right corner of the cell and drag it to H12.

Ooooohhhhhh. Aaaahhhhhh. Notice how that C$11 auto-adjusted to C$12, C$13, etc. as you moved across.

Next, with C12 – H12 selected, grab the fill handle and fill it down 10 rows or so. When you let go, you’ll see, as if by magic, that all of the Order Details appear (as appropriate) and now as you change the Order ID at the top of the sheet, the entire Order Details updates.

Figure 8: The completed details table, with the array formula filled across then down in order to retrieve all details.

Pretty cool huh? We had to create a formula that was a bit more complex than just using VLOOKUP, but we also got a Master-Detail view working in a very robust way, using only built-in Excel formulas (no code, etc.).

Of course, I’m sure there are other, equally slick ways of doing this – if you’ve got one, let’s see it!

Thanks to Eric Patterson for putting together this post.

In Excel 2010, we have made a number of improvements to Solver that make it easy for beginners to get started and more advanced ones to find solutions to all types of problems.  The purpose of this post is to review the improvements to Solver, and so assumes some familiarity with the tool.  If you are not familiar with Solver, you can learn more about Solver from Office Online.  The help topic applies to the Solver that ships in Excel 2007 and earlier, but the concepts are very much the same.Ms office 2007 is so Charismatic.

In short, Solver is a what-if analysis tool for optimization.  It is an add-in licensed from Frontline Systems that has shipped with Excel for many years.  Solver helps to find an optimal value in one cell, called the target cell, on your worksheet. It works by changing a group of cells related to the target cell to find an optimal value subject to the constraints that you set.  You can use Solver, for example, to determine the most efficient shipping routes, maximize income/sales/etc., or discover the best product mix.  To see an example of (the Excel 2007) Solver in use, check out this earlier blog post where Solver is used to maximize the number of hikes on a vacation road trip. Office 2007 professionaland Office 2007 ultimate are so powerfull.I hope to write more posts in the future about examples of using Solver to solve optimization problems.

In this post I am going to cover the new user interface for the Solver app, the new solving methods and constraints that can be used, and the new reports that can be generated.

New Interface

One of the changes that we have made is to the interface used for setting the Solver Parameters. The Solver Parameters dialog Box has been streamlined to make it easier to define a model. The most common features are now surfaced at the top level making it easier for both beginning and advanced Excel users to be successful. For example, the solving method (Simplex, GRG Nonlinear, Evolutionary) can now easily be chosen based on the type of model being solved. Also, a checkbox for making unconstrained variables non-negative (on by default) is bubbled up to the Solver Parameters dialog.IS Windows 7 Professional better than Windows 7 Ultimate ?

Also updated is the Solving Options dialog. The Options dialog has now been divided into 3 tabs with settings based on Solving Method. Now it will be easier for people to associate settings with the solving method that they are using.

Solving Methods

The Excel 2010 Solver has 3 Solving methods used for solving spreadsheet optimization problems.

Simplex Method

The Simplex method is used for solving linear problems. The Simplex solving method has several performance enhancements in Excel 2010 resulting in greatly improved performance for some problem types.

GRG Nonlinear

The GRG solver is used for solving smooth nonlinear problems. There is a new Multi-start search setting which when used in conjunction with the GRG solver results in better solutions, escaping locally optimal solutions in favor of globally optimal ones.QuickBooks 2010 is so Helpful!

Evolutionary Solver

The new Evolution solver accepts Solver models defined in exactly the same way as the Simplex and GRG Solvers, but uses genetic algorithms to find its solutions. While the Simplex and GRG solvers are used for linear and smooth nonlinear problems, the Evolutionary Solver can be used for any Excel formulas or functions, even when they are not linear or smooth nonlinear. Spreadsheet functions such as IF and VLOOKUP fall into this category.

All Different Constraint

There is a new type of integer constraint known as AllDifferent that form a permutation of integers, making it easy to define models with ordering or sequencing. The well-known travelling salesman problem is hard to define in the current solver, but it can be defined with just an objective and one AllDifferent constraint.Acrobat 9 is so useful!

New Reports

In Excel 2010, we’ve added a number of new report types that provide additional detail about the problem being solved.

Linearity Report

If you try to solve a model that is not linear, Solver will indicate that linearity conditions were not satisfied:

A new Linearity Report is available that uses numerical tests to help you find the constraints that are not linear and/or the variables that do not occur linearly in the model:

Feasibility Report

If you try to solve a model that has no feasible solution, Solver will display the message “Solver could not find a feasible solution” in the Solver Results dialog. There now two new Report choices:

The Feasibility Report uses a procedure that involves automatically adding and dropping constraints and re-solving the model, to identify an “Irreducibly Infeasible Subset” of the constraints. (The Feasibility-Bounds report uses the same procedure but doesn’t add or drop variable bounds, to save time.) These reports can help you find the source of infeasibility in your model.

Population Report

Where the Simplex and GRG Solvers find a single solution, the Evolutionary Solver creates a population of candidate solutions. When this Solver Engine stops, the best solution from the population is displayed on the worksheet, but a new report is available in the Solver Results dialog:

The Population Report provides statistics about the entire final population of candidate solutions, which can yield insights into how the Evolutionary Solver performed on this model, and whether a true optimal solution has been found.

I love to hear what problems you solve or opportunities you exploit with Access—it is amazing what self-taught users are able to accomplish.  Ms office 2007 is so Charismatic.There is a interesting thread What do you use Microsoft Access for? on UtterAccess. Here is an interesting Wordle tag cloud of the thread (Database, Microsoft and Access are filtered out because they were so dominate in the cloud):

Access touches people’s lives in so many ways… Here are a few of my favorite quotes from self-taught users, highlighting their scenario and background:

JimBud writes:

I have volunteered for the non-profit organization Make-A-Wish of Southwestern Ontario (kid’s with a life threatening illness) – now in my 16th year.

I had no experience in databases or VBA so this was a learn as you go project – beg, borrow and steal what ever I could to make it do what ever was required. I basically was making changes to accommodate their needs as they arose.

car313 writes:

I primarily use Access for maintaining simple static data related to my work with STD/HIV/AIDS. (That would mean Sexually Transmitted Diseases/Human Immuno Virus/Acquire Immunodeficiency Syndrome for people from other areas of work). No need for relationships, junction tables, VB code and macros, and select queries. So I can say I am perhaps using about 10% of the capabilities of Access.

doris7264 writes:

Essentially, I build Access databases when data volume or complexity outgrows Excel spreadsheets. I like the fact that Access will store the query definition instead of the results, particularly parameter queries. The query definitions can be much more complex than in Excel, as well. If a piece of information is the result of a calculation, Excel would require the formula on each line; but in Access, I can just have the value calculated when I need it. An update query is a lot faster and more trustworthy than a Ctrl-H search and replace, too. The analytical possibilities are so much better.

At the same time, an Access database remains within the reach of the end-users, not the IT department that might get around to making requested modifications someday, maybe.

Oldbirdman writes

I am a birder (aka bird watching), and I want to keep track of what I have seen, when, and where. This is a very similar problem to a standard business application, with products (bird species) and customers (locations), where a purchase order (sightings at location on a specific date) may contain a list of products ordered (birds seen) on a specific date.

I am self-taught. Most of the books I’ve tried were worthless, so I learned using Access ‘Help’ and the internet.

Scarpo80 writes:

I joined a small but fast growing business 7 years ago to run their HR dept. We had about 500 employees when I started and all their info was kept on an Excel spreadsheet. Seeing the limitations of this as we grew I put in place an Access database.
We now have over 3,500 employees and, due to the high turnover of staff in the retail industry in the UK, records on over 15,000 people on this same database all with their employment, training, salary and personal data stored. Office 2007 professionaland Office 2007 ultimate are so powerfull. It works really well and has cost us nothing. Plus I can continue to develop it and add to it in a way that suits us. It’s only limitations appear to be what I’m able to learn how to do and I nearly am always able to find a way around a problem. QuickBooks 2010 is so Helpful!

I’m very proud of it and it shows how powerful Access can be. I’m also entirely self-taught (books and messing around). I think Access is a great bit a software and certainly nothing anyone with a bit of IT savvy should be afraid to try out.

CVFoote writes:

I recently designed a database to prepare rate sheets for the insurance company I work for. Prior to my idea agents would have to find the appropriate Excel Spread sheet for the payroll mode, then find the worksheet for the industry code, then pick the items they wanted by cut and paste or deleting those they don’t want.

I created a great interface, screenshot attached, and agents were very excited about the application. I made the mistake of showing my idea to corporate IT and in January they are releasing their own version. Of course I am just a little disappointed because I thought I might make a little side income from the hours I spent on it. Still, I also learned a lot building the database.

mbhknight writes:

One of my hobbies is genealogy and I have an access database of all towns villages and cities in England, Wales and Scotland and I’m just building one for all of Ireland.

I also use access to keep control of all the junk mail I get. I mail the sender asking them to remove me from all their mailing lists and databases and if they still persist I threaten them with action under the (UK) data protection act giving them exact details as to when I asked them to remove my name. As a result I’ve gone from getting at least 5 or 6 of these irritating letters a day down to about 1 a month. Try it yourself it means you only get mail that you want and not lots of annoying sales tripe.

I love access and at the moment I’m building a database of all my jazz records with details of bands performers etc.

Bobed writes:

Simple to moderately complex applications for work (before I retired) myself, family and small business acquaintances. Most recently developed a player/registration application for the Michigan Thumb Area kids athletic league, which my daughter administers for fun. This is the characteristic application for which Access was created: players, parents, teams, coaches, schedules, sponsors, advertisers, uniforms, equipment, multiple sports, 100 specialized reports and forms, several tough queries, lots of automation including real-time Saturday morning registration for 50 kids, fee collection and management. A really fun project for me, including user documentation.

cbteng101 writes:

Strictly business. Have used access for approx 10 yrs. Developed programs for Quality Assurance. Reject/Corrective action , Vendor Rating, Customer Drawing/Specifications, Metallurgical/Welding Inspection, Test Data report databases. Recent developments have been Job Costing , Sales/ Quoting databases.

Being a self trained programmer, Mech Eng degree, I have found a lot of info in this forum to be a great asset.

Dinghus writes:

I use it to beat cockroaches to death.

I’ve found a small niche of people in LARGE companies that want quick Access db’s for things like attendance monitoring. The company IT depts don’t want to be bothered with such small projects so I get to scarf them up.

AQM_UK writes:

The biggest project was an 8 month write for a contracting process. This involved gathering global data of purchases, and then based on the category that the user was working one, it would prepare an RFI (request for information) from plants globally and based on their answer may request further information. This would then be analyzed by Category managers, who would send this to vendors to bid on. The vendors bids would be analyzed by the system and contracts awarded from here. This was then prepared into a contract for the vendor to sign, entered to the ERP system and savings calculated from here.

As a result, I got promoted.

Magicmusic writes:

What I’ve learned has been by reading books.

I developed a db over 6 years with two people different IT people (who had major differences in how to accomplish tasks) to track students in our university choral program.

The_Catlady writes

I use it to match up Excel spreadsheets for me! I just point it at a few Excel worksheets and then design a query to match them up. This serves 2 purposes: 1) it tells me, with a left outer join, whether data is missing from the second worksheet; 2) it enables me to quickly combine data from various tables at once and export the results into yet another worksheet for my end-user to see.

People I work with are amazed at the quick results I can obtain this way.

mrado writes:

I am a teacher and use access for my records. I have developed a database for school reports which a school in England is using. I am developing another database for a different school to track pupil progress etc. but this is proving more tricky and testing my self taught knowledge of access to the limit. I am looking to develop my knowledge of SQL and VB so I can use code better.

irobbie writes:

I use Access 2003 to maintain membership info for a country wide group of car clubs which all have the same interest – Cadillacs. The database is set up to hold data about the member, their membership status and the cars they own. The data is maintained by a 70 years old person with no training and very little knowledge of computers.

doctor9 writes:

Frankly, if it weren’t for my Access skills, I’d probably still be renting, rather than paying off a mortgage.

On the side, I’m helping my wife with her photo studio. For instance, I’ve created a master checklist of specific wedding shots she can get. The customer checks off which ones they want, and she prints out a list of “must have” shots that she can take with her on location.

This request came in from Gisli one of our Technical Advisors at MSFT.

I’ve been asked by a collection of UN organizations that handle disaster response around the world if we would be able to provide training material (including recorded webcasts, on-line training, etc.) that could be used to train information management  people in a number of disaster areas around the world. Ms office 2007 is so Charismatic.

One of the key areas they have identified as a need is to increase the knowledge of these people in the area of administration, querying, reporting, analyzing and programming within Access. Windows 7 is also my love!

As you can imagine during disasters (or complex emergencies such as conflicts) there is a lot of data being collected about the situation on the ground. This data often ends up not getting analyzed properly because of lack of DBA know-how in the field. Acrobat 9 is so useful!

So if you can point me to some freely available material that we could share with them then that would be much appreciated.

We are putting together some stuff but wanted to ask all of you to contribute any resources/links you think might be helpful. Please add the info in the comments so we can all share.

Today’s guest blogger is Edwin Blancovitch. Edwin is president of Advanced Developers.net, creators of Easy Payroll, a software package to manage your human resources, payroll, scheduling, time and attendance needs. IS Windows 7 Professional better than Windows 7 Ultimate ?

Regularly when we create databases for our customers we use continuous forms for data entry. The problem is, it’s often difficult for users to identify the record they’re working on a big continuous form with tons of records.

I’ve found a way to overcome this in just a few just quick steps that I’d like to share with you guys.

This is fully compatible with previous versions of Access, because I have been doing this my whole life. Actually, I just found it by mistake, while trying to create a conditional formatting event. We can do this with conditional formatting but this method is easier. Windows 7 is also my love!

1. First select the continuous form.
2. Set the Back Color of all the controls in the detail section of the form to the color that you want to be displayed when the user clicks the control. In this example, I’m using #ADC0D9.
3. Set the Back Style property of all the controls in the detail section of the form to Transparent.

Now, when you display the object in form view you will see the selected control highlighted with the color you have selected in the Back Color property of the control.

YEAH, now all your users can easily identify the current selected record and even the selected control where the cursor is. That’s all, no code, no difficult conditional format to configure. Office 2007 professionaland Office 2007 ultimate are so powerfull.

You can download a sample database that illustrates this method here.