Hi [FirstName],
Simply Access Tips
Issue 01 – 2009
January 6
th2009
Welcome to the first edition of Simply Access Tips for 2009.
Housekeeping, as usual, is at the end of the Newsletter so; if you need to know how to unsubscribe, white-list, etc., then scroll to the bottom.
Julie’s Ponderings
Hi [FirstName],Welcome to all new subscribers and all my long term subscribers.
I am off to a good start for 2009 with the first Newsletter out on time. As per usual life has been, not hectic, but I did something that I had not planned for so my month of December was a bit mixed up.
The first thing I did was hiking with my daughter to Wilson’s Prom – this was planned. We did a 4 day hike which was amazing (about 54km). The scenery was spectacular. The weather was pretty good, with mild weather, except for one afternoon/night when it hailed with hail stones the size of golf balls. It also rained quite heavily that night and my daughter learnt a good lesson in tent placement; i.e. do not place it at a bottom of a walking track, as it turns intro a river when it rains. I then went on a 3 week cruise. This was unexpected. My mum and dad had booked to go, but a few weeks before they left, mum had a mini stroke; she also has a heart condition. So my sister and I decided to go on the cruise for a bit of support for my dad. Lucky we did as mum was not well, and was confined to the ship. Having the four of us was great as on the days we were in port one of us could stay with mum and the other two could go ashore and do something together, so we rotated which worked out extremely well.
The cruise was from Melbourne Victoria Australia, down to Tasmania then across to New Zealand, then back to Australia arriving in Brisbane Queensland, we then flew back to Victoria.
Highlight were swimming with the Hector Dolphins near Christchurch New Zealand and going out on the New Zealand America’s Yacht.
That is all for now, back into the swing of things for 2009. Talk to you all in a fortnight.
Julie
Back Issues
Just a reminder, back issues of Simply-Access Tips can be found at:
http://www.simply-access.com/SimplyAccessTipsNewsletter1.html
Questions
Unfortunately, I am unable to answer individual questions, personally, via email. Purely due to the fact that all I would do is answer questions. You do, though, have three other options for me to answer your questions:
1) I can include them in an edition of Simply Access Tips (if you do not mind your question being shared). There may be a wait before they are included, depending on how many I have.
2) You can go to All Experts and post your question there. I have put myself on permanent holidays from All Experts, as I find I cannot keep up with even one question a day, but I will visit often and check the question pool.
http://www.allexperts.com/browse.cgi?catLvl=3&catID=1440
3) Another good site is www.utteraccess.com
Learn VBA
You may have noticed I have used VBA code in some of my answers. If you would like to learn this valuable skill to enable you to expand the flexibility of your Microsoft Access database, then click below to get your first lesson free, or cut and paste into your URL Address bar. You will need to scroll to the bottom of the page and enter your information into the form.
Tips
NOTE: If you are copying code from the tips and find it is not copying correctly –
i.e. the html is also being copied, then copy them into NotePad first (not Microsoft Word, as Word keeps the formatting) then copy from NotePad into Microsoft Access.
Tip 1:
Counting Records in a Group
Question
I have a query with different people sort by name and by date. So it looks like this :
John 12-jan-08 John 15-jan-08 John 22-jan-08 Amy 08-jan-08 Amy 21-jan-08
I want to add a new column to count each line and to be reset if i have a different name. So, in row 1 to have 1 for John ... and in row 3 to have 3 for John but then on the row 4 to have again 1 because the counting should start again. It`s easily done in Excel but what about Access?
Answer
This can be done in a query in Access - but it does take coding to achieve. It is easier to achieve in a report.
To do this:
Create a report in design view and choose to group by name when prompted. Once the report is created, in design view add an unbound text box and in type =1
Right click on this text box > properties > Data > Running Sum > Yes Over Group This will then number each record in each group.
You can always remove the name from the header and add it to the detail section if you wish it to appear in each row.
Tip 2:
Summing Positive and Negative numbers in a
query
Question
I have 15 columns that have either positive or negative values. I need to have 2 extra columns that give me the result of adding only those that are negative or positive. I tried with various forms of DSum, but I can't get it to work.
Answer
To do what you ask try the following:
For the negative (change to >=0 for the positive)
IIf(Column1 < 0,Column1,0) + IIf (Column2 < 0, Column2,0), IIf(Column3 < 0, Column3,0) etc...
The IIF statement evaluates the expression, and if it is true i.e. less than 0, then it uses the first value (i.e. column1), if not then, the second value will be used (i.e. 0)
Tip 3:
Changing the recordsource of a subform
Question
I need to change the record source in a sub form
Answer
To do this, with the form in design view right click in the upper left hand corner of the subform (where the two rulers meet), select the properties option, then click on the data tab, then change the record source property, it should be the top row.
Housekeeping
Once again, just a quick reminder: Please ensure the following email address is white-listed with your ISP:
