Roger's Access Library : Roger's Download Samples www.rogersaccesslibrary.com/forum/ This is an XML content feed of; Roger's Access Library : Roger's Download Samples : Last 10 Posts Fri, 10 Sep 2010 01:06:13 +0000 Thu, 15 Jul 2010 15:15:01 +0000 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.67 30 www.rogersaccesslibrary.com/forum/RSS_topic_feed.asp?FID=1 Roger's Access Library www.rogersaccesslibrary.com/forum/forum_images/ral2.gif www.rogersaccesslibrary.com/forum/ Roger's Download Samples : YearsMonthsDays.mdb (intermediate) www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=215&PID=556#556 Author: Roger Carlson
Subject: YearsMonthsDays.mdb (intermediate)
Posted: 15 Jul 2010 at 3:15pm

Update: Added accdb sample]]> Thu, 15 Jul 2010 15:15:01 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=215&PID=556#556 Roger's Download Samples : RollingAverages.MDB www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=544&PID=555#555 Author: Roger Carlson
Subject: RollingAverages.MDB
Posted: 25 Jun 2010 at 7:13pm

Please consider donating $1 per sample downloaded, find out why here.
 
This sample illustrates two different ways to create a "Rolling Average" query. It averages the latest 13 months in the sequence.
 
SequencetWeektValueRollingAverage
20Week 261039.8461538461538
19Week 251143.8461538461538
18Week 242248.4615384615385
17Week 234447.6923076923077
16Week 225545.4615384615385
15Week 214446.1538461538462
14Week 202245.4615384615385
13Week 197748.5384615384615
12Week 188846.1666666666667
11Week 171142.3636363636364
10Week 167445.5
9Week 153542.3333333333333
8Week 142543.25
7Week 136245.8571428571429
6Week 127143.1666666666667
5Week 111237.6
4Week 101544
3Week 96453.6666666666667
2Week 83548.5
1Week 76262
 
The first two use DCount and the second two use a Sub Query.  You must identify a unique column in the query to create the sequence on.  I used an autonumber ID field, which while in order, has gaps in it. 

The DCount method creates an updateable recordset.  The SubQuery method produced non-updateable recordsets.

Download Access 2000-2007 (mdb): RollingAverage.zip
Download Access 2007-2010 (accdb): RollingAverage07.zip
 
]]> Fri, 25 Jun 2010 19:13:55 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=544&PID=555#555 Roger's Download Samples : ActionQueries.mdb (beginner)(2000-2007) www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=529&PID=538#538 Author: Roger Carlson
Subject: ActionQueries.mdb (beginner)(2000-2007)
Posted: 30 Dec 2009 at 10:23am

Please consider donating $1 per sample downloaded, find out why here.
 
ActionQueries.mdb
 
This sample illustrates Action Queries (also called Data Manipulation Language - DML)
in both SQL view and Query Builder View.

There are 4 types of Action Queries: Delete Query, Append Query, Update Query, and Make-Table Query.

Access 2000-2007 Download: ActionQueries.zip ]]> Wed, 30 Dec 2009 10:23:05 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=529&PID=538#538 Roger's Download Samples : ChooseQueryFromList.zip (beginner) (2003) www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=528&PID=537#537 Author: Roger Carlson
Subject: ChooseQueryFromList.zip (beginner) (2003)
Posted: 15 Dec 2009 at 2:42pm

Please consider donating $1 per sample downloaded, find out why here.
 
ChooseQueryFromList.zip
 
This sample shows how to display all the queries in your database in a listbox and run them with a button.
 
Download Access 2003 version: ChooseQueryFromList2k.zip
]]> Tue, 15 Dec 2009 14:42:08 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=528&PID=537#537 Roger's Download Samples : AppendValuesToExcel.mdb (intermediate) (2000-2003) www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=522&PID=530#530 Author: Roger Carlson
Subject: AppendValuesToExcel.mdb (intermediate) (2000-2003)
Posted: 10 Nov 2009 at 3:13pm

Please consider donating $1 per sample downloaded, find out why here.
 
AppendValuesToExcel.mdb
 
This sample demonstrates how to append data from a database to an Excel Spreadsheet using Office Automation to find the next empty row in the spreadsheet.
The overall process:

1) Define Access and Excel object variables

2) Open Excel sheet as an Excel Object

3) Find the last row of data

4) Write data from Access query to Spreadsheet

5) Save and close spreadsheet

Look in the "basExport" module for the relevant code. 

 
Note:  This requires a spreadsheet called "ExportSpreadsheet.xls" (included with the sample) exist in the same directory as the  application.  Also requires references set to DAO 3.6 and Excel
 
Download Access 2000-2003 Version: AppendValuesToExcel.zip
 
 
]]> Tue, 10 Nov 2009 15:13:28 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=522&PID=530#530 Roger's Download Samples : MultipleLabelsOffsetBatch2.mdb (intermediate) 2000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=519&PID=526#526 Author: Roger Carlson
Subject: MultipleLabelsOffsetBatch2.mdb (intermediate) 2000
Posted: 08 Oct 2009 at 10:18am

Please consider donating $1 per sample downloaded, find out why here.
 
MultipleLabelsOffsetBatch2.mdb
 
This sample is a similar to MultipleLabelsOffsetBatch.mdb, but adds the ability to have different numbers of labels for each address.

Download
Access 2000 DAO (52 KB): MultipleLabelsOffsetBatch2.zip

]]> Thu, 08 Oct 2009 10:18:04 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=519&PID=526#526 Roger's Download Samples : PreviousDateQuery.mdb (beginner)(2000-2007) www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=516&PID=523#523 Author: Roger Carlson
Subject: PreviousDateQuery.mdb (beginner)(2000-2007)
Posted: 10 Sep 2009 at 9:21am

Please consider donating $1 per sample downloaded, find out why here.
 
PreviousDateQuery.mdb (beginner)
 
This sample illustrates how to create a Previous Date query.  It shows how to:
 1) Display the previous date in subsquent records of a table
or
 2) Display the previous date in subsquent records of a table  over a group.  In this case, the group is the OrderID field.

There are two methods shown here: Using a 1) correlated subquery and 2) DMax domain aggregate function.  The correlated subquery is the faster method, but it returns a non-updateable recordset.  The DMax method is slower, but the recordset is updateable.

HOWEVER, it should be noted that against a large dataset, neither method will be very fast!

Access 2000-2007 version: PreviousDateQuery.zip
 
NOTE: If you want to know the number of days between the two dates, see my sample:  DaysBetweenQuery.mdb.
]]> Thu, 10 Sep 2009 09:21:27 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=516&PID=523#523 Roger's Download Samples : ParseMultivalueFieldToNormalizedStructure A2K-A2K3 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=449&PID=445#445 Author: Roger Carlson
Subject: ParseMultivalueFieldToNormalizedStructure A2K-A2K3
Posted: 10 Apr 2009 at 11:43am

Please consider donating $1 per sample downloaded, find out why here.
 
ParseMultivalueFieldToNormalizedStructure.mdb
 
This sample illustrates how to write values from a multiple valued field (not the Access 2007 multi-value field) into a normalized Many-to-Many structure.  The original table is called "ContactsDownloadMultivalue". The contact information is written into "tblContacts". The category information from the multiple valued field is written to "tblCategories"

Then a record is created in jtblContactCategory junction table, establishing the many-to-many relationship.

To run the code, open Module1, place the cursor in the subroutine, and click Run.

Access 2000-2003 version: ParseMultivalueFieldToNormalizedStructure.zip
Level: Intermediate. Requires knowledge of VBA programming.
]]> Fri, 10 Apr 2009 11:43:24 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=449&PID=445#445 Roger's Download Samples : DaysBetweenQuery.mdb (beginner)(2000-2007) www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445&PID=441#441 Author: Roger Carlson
Subject: DaysBetweenQuery.mdb (beginner)(2000-2007)
Posted: 29 Jan 2009 at 1:33pm

Please consider donating $1 per sample downloaded, find out why here.
 
DaysBetweenQuery.mdb
 
This sample illustrates how to create a "Days Between" query.  It shows how to:
1) Display the difference between dates in subsquent records of a table
or
2) Display the difference between dates in subsquent records of a table  over a group.  In this case, the group is the OrderID field.

There are two methods shown here: Using a 1) correlated subquery and 2) DMax domain aggregate function.  The correlated subquery is the faster method, but it returns a non-updateable recordset.  The DMax method is slower, but the recordset is updateable.

HOWEVER, it should be noted that against a large dataset, neither method will be very fast!

Download
Access 2000+ (17 KB):  DaysBetweenQuery.zip
]]> Thu, 29 Jan 2009 13:33:13 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=445&PID=441#441 Roger's Download Samples : ERMaster.ppt www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=442&PID=438#438 Author: Roger Carlson
Subject: ERMaster.ppt
Posted: 08 Jan 2009 at 10:38am

Please consider donating $1 per sample downloaded, find out why here.
 
ERMaster.ppt
 
Not an Access sample, but a useful Powerpoint template for creating ER Diagrams.
 
Download: ERMaster.zip
]]> Thu, 08 Jan 2009 10:38:54 +0000 www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=442&PID=438#438