How to assign "odd" or "even" value to every record in a table.
Q: I would like to write some simple code to run through an Access table and update each record, alternating with "Odd", "Even", "Odd", "Even," et cetera, throughout the table.
A: If you have a primary key on the table or a unique index on a column, then you could use two queries to do the update for you, then a third one to get rid of a temporary table. It doesn't matter if this column is numeric or not, or even non-sequential.
First query:
SELECT ID,
IIF((((SELECT COUNT(*)
FROM tblMyTable AS Cnt
WHERE Cnt.ID < tblMyTable.ID) + 1) MOD 2) = 1, "Odd", "Even") AS OddOrEven
INTO tblNumbering
FROM tblMyTable;
Second query:
UPDATE tblMyTable AS T INNER JOIN tblNumbering AS N ON T.ID = N.ID
SET SomeValue = OddOrEven;
. . . where the table is named tblMyTable, ID is the primary key or unique
index, and SomeValue is the name of the field that needs "Odd" or "Even." When finished, run the third query to delete the temporary table.
Third query:
DROP TABLE tblNumbering;
Top
How to assign sequential row numbers to each row in a query.
Q: How can I assign sequential row numbers for each row in a query?
A: If you have a primary key on the table or a unique index on a column, then it doesn't matter whether this column is numeric or not, or even non-sequential. Use the following query:
SELECT ((SELECT COUNT(*)
FROM tblPersonnel AS Tmp
WHERE Tmp.PersID < tblPersonnel.PersID) + 1) AS Num,
PersID, FName, LName
FROM tblPersonnel
ORDER BY PersID;
. . . where the table is named tblPersonnel, the primary key is PersID, and the numbered column is named Num.
Top
How to calculate a running total.
Q: How do I calculate a running total in my query results?
A: To calculate a running total, the query needs to use an unequal self-join, the GROUP BY clause, and the SUM( ) aggregate function.
Use SQL syntax such as the following:
SELECT P1.PymtID, P1.Pymt, SUM(P2.Pymt) AS RunningTotal
FROM tblPayments AS P1 INNER JOIN tblPayments AS P2
ON P2.PymtID <= P1.PymtID
GROUP BY P1.PymtID, P1.Pymt;
... where tblPayments is the name of the table holding the values, PymtID is the primary key for each record, and Pymt is the amount paid.
Top
How to calculate or update "week of the month."
Q: We send out invoices every Friday and some invoices need special messages included depending upon which week of the month it is. I'm trying to assign the numerical week of the month (1 through 5) according to which week of the month the Friday InvDate lands on, but I'm having trouble writing a query using GROUP BY that also includes fields that aren't part of an aggregate function. Can this be done in an UPDATE query?
A: The "Friday of the month" is a calculated value, and it's usually best to calculate values at run-time instead of storing the value in a table. To calculate the value in a query that can be run whenever needed, create a new query and paste the following SQL statement into it:
SELECT ID, InvDate, IIf ((DatePart("d", InvDate) Between 1 And 7), 1, IIf ((DatePart("d", InvDate) Between 8 And 14), 2, IIf ((DatePart("d", InvDate) Between 15 And 21), 3, IIf ((DatePart("d", InvDate) Between 22 And 28), 4, 5)))) AS FridayOfMonth
FROM tblInvoices
ORDER BY DatePart("yyyy", InvDate), DatePart("m", InvDate), DatePart("d", InvDate);
Replace the name of the table in this example, tblInvoices, with the name of your table and add/replace any other fields needed from the table.
If you find that you really must store this calculated value in your table, then you may use the above query to update the existing table, as long as there is a primary key. If you had saved the above query as qryFridays, then the following query could be executed to update the tblInvoices table with the numerical week of the month:
UPDATE tblInvoices INNER JOIN qryFridays ON tblInvoices.ID = qryFridays.ID
SET tblInvoices.FridayOfMonth = qryFridays.FridayOfMonth;
... where ID is the primary key.
Top
How to calculate student grades from test results.
Q: I have a table with test ID's, student ID's and each student's test score for each test. How do I calculate a letter grade for each student's test?
A: There are a number of ways to assign a letter grade to a score with a query. An easy way (but probably not the most efficient method) is to create a second table with the following attributes:
Table Name: tblGrades
|
Field Name
|
Data Type
|
|
Grade
|
Text, 1 char (Primary key)
|
|
MinPct
|
Single precision
|
|
GrdPts
|
Long
|
Fill the tblGrades table with the following data:
|
Grade
|
MinPct
|
GrdPts
|
|
A
|
90
|
4
|
|
B
|
80
|
3
|
|
C
|
70
|
2
|
|
D
|
60
|
1
|
|
F
|
0
|
0
|
The minimum percent required for each letter grade can be altered to suit your needs, but the other values for grade and grade points for each record must remain as listed above.
Paste the following SQL statement in a new query:
SELECT TestID, StudentID, Score,
IIF((MAX (GrdPts) > 0),
CHOOSE (MAX (GrdPts), "D", "C", "B", "A"), "F") AS TestGrade
FROM tblScores INNER JOIN tblGrades ON tblScores.Score >= tblGrades.MinPct
GROUP BY TestID, StudentID, Score
ORDER BY TestID, Score DESC;
... where tblScores is the name of the table holding the test ID's, student ID's and test scores. TestID identifies each test, StudentID identifies each student, and Score is the student's test score.
Top
How to determine the lowest five values of a record set, instead of the highest five values.
Q: I know how to get the top five scores for players in a tournament by using the TOP keyword. How can I get the bottom five scores?
A: By default, using the TOP keyword in a query will sort the results in descending order. To get the bottom results, the sort order needs to be reversed to ascending order by using the ASC keyword. For example:
SELECT TOP 5 FirstName, LastName, Score
FROM tblPlayers
ORDER BY Score ASC;
Top
How to determine which customers purchased both products.
Q: I have a table of orders with customer ID's and the product ID of the item purchased, along with other information about the order. I'm trying to write a query that will determine which customers purchased both of two products over any period of time, but I can't seem to write the query so that it produces a list of both the first and the second products. Do I need to redesign my Orders table so that I can retrieve this information from the data?
A: Not necessarily. A subquery can determine which customers purchased either of the products, then that subquery can be used as the FROM clause of the main query to determine which customers purchased both of these products by counting the number of records in the subquery that each customer has. If the record count equals two, then that customer purchased both products. The Jet SQL syntax for such a query would be:
SELECT CustomerID
FROM [SELECT CustomerID, ProductID
FROM Orders
WHERE ((ProductID = 1) OR (ProductID = 2))
GROUP BY CustomerID, ProductID]. AS qry2Products
GROUP BY Customer
HAVING COUNT(CustomerID) = 2;
Top
How to determine which tables are no longer needed as sources for queries or forms.
Q: I have many tables, queries and forms in my database application, but some tables are no longer needed as data sources. How can I find out which tables to get rid of?
A: As database applications evolve, many database objects change or become obsolete. "Deadwood" should be removed from the database application whenever feasible. To determine which tables or other objects are still needed, Microsoft Office Access 2003 has a feature that allows one to check object dependencies, as long as the Track Name Autocorrect database option is turned on.
If this version of Access is not available, then there are still several options:
1. The Access Documenter Wizard could be used to produce a report for all of the queries in the database. Each query will list the SQL statement, which names the tables used in the query. The SELECT queries will also name the SourceTables for each of the fields in the query.
2. There are also some third-party applications available which can produce an object dependencies report listing the tables and objects currently in use for you, but the applications that do an adequate job are not free.
3. One can create the following queries and a VBA procedure to determine which tables are no longer needed:
To produce a list of the object names used in the SELECT queries, create a new query and paste the following text into the SQL View pane and then save the new query with the name "qryQueryObjectNames":
SELECT DISTINCT Name1 AS ObjName
FROM MSysQueries
WHERE ((Attribute <> 6)
AND (IsNULL(Name1) = FALSE))
ORDER BY 1
UNION
SELECT DISTINCT Name2 AS ObjName
FROM MSysQueries
WHERE ((Attribute <> 6)
AND (IsNULL(Name2) = FALSE));
To produce a list of table names never used in SELECT queries, create a new query and paste the following text into the SQL View pane:
SELECT MSysObjects.Name AS TableName
FROM MSysObjects
WHERE ((MSysObjects.Type IN (1, 4, 6))
AND (MSysObjects.Flags = 0)
AND MSysObjects.Name NOT IN
(SELECT ObjName
FROM qryQueryObjectNames));
Hint: This query produces a list of tables never used in SELECT queries, but does not list the tables never used in other types of queries, such as action queries, UNION queries and queries used as the sources or filters for bound forms and bound controls. To produce a list of those queries that need to be checked for table names, create a new query and paste the following text into the SQL View pane:
SELECT [Name]
FROM MSysObjects
WHERE (([Type] = 5)
AND (Flags IN (32, 48, 64, 80, 128))
AND (Left([Name], 1) <> "~"))
ORDER BY 1
UNION
SELECT "Form " & Right([Name], Len([Name]) - 5) AS FormName
FROM MSysObjects
WHERE (([Type] = 5) AND (Flags = 3));
The names preceded by "Form" signify the bound forms or bound controls on forms that need to be checked for table names in the sources or filters for these bound objects. Using this recordset of query names and form names, an experienced developer can easily write a VBA procedure to determine which tables are not used in any of these objects. An inexperienced developer has yet another opportunity to "get experience" on why spaces should never be used in variable names and other identifiers by writing a much more complex VBA procedure to determine which tables are never used in any of these objects, or the inexperienced developer can take the time to visually check every query, form, and control on each form listed in the above query to determine which tables are never used.
And remember, once a table is identified as "deadwood," the table should not be deleted immediately. Instead, the table name should be changed temporarily (many developers precede the name of an object with a "~" to denote that it's marked for deletion) -- just in case the table really is needed elsewhere. For example, another database application may link to a table in the current database, which would be an indicator that the table should be moved to the other database application.
Top
How to "Find and Replace" a value in as many records as needed.
Q: Why doesn't Access "Find and Replace" more than 9,487 cells at a time? I have more than 300,000 cells in a column that I need to find and replace part of the values. Access will only find and replace 9,487 at a time. Is this a limit that can be changed?
A: If one uses "Find and Replace" to update values in columns, the number of records affected will likely be limited by the amount of memory allocated to the Access application. So the question is, "How much RAM does the computer have available after memory is allotted for the operating system and the other applications that are currently running?" And the answer is, "Only enough RAM to handle 9,487 records at a time, not 300K records."
Why not just let Jet update all of these records in an update query? It will take seconds (or a fraction thereof) to run the query, and all of the records will be changed with a single run of the query. That's a lot faster than running "Find and Replace" at least 32 times.
Top
How to read Jet's query execution plan to write more efficient queries.
Q: SQL Server and Oracle have tools to analyze query execution plans, which can help me to write more efficient queries. Does Access have such a tool, or do I need to purchase a utility from a third-party vendor?
A: Jet can provide this for you if you don't mind making a Windows Registry change. You can find out how by reading the article, "Use Microsoft Jet's ShowPlan to write more efficient queries" for instructions on how to use JETSHOWPLAN to write the query execution plans to a text file.
Top
How to use Jet SQL to append records to a table from a text file.
Q: Is there a way to write a query that appends records from an imported text file?
A: Yes. The simplest way would be to use SQL syntax such as the following:
INSERT INTO tblMyTable
SELECT ImportedFieldName AS MyFieldName, ImportedStuff AS MyStuff
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].TextImport.txt;
... where tblMyTable is the name of the table to append the records to, ImportedFieldName is the name of the column in the text file that maps to the MyFieldName field in tblMyTable table, and ImportedStuff is the name of the column in the text file that maps to the MyStuff field in tblMyTable table. The imported text file is C:\Data\TextImport.txt, which contains the names of the columns as "headers" (first row), along with the records of data to be imported.
Top
How to use Jet SQL to insert records into a remote database that has a database password.
Q: I have an Access database that I would like to insert records into from another Access database. The table structures are identical, so this should be easy. The trouble is, the database that needs the new records has a database password, and if I link the table using the link table wizard, I have to supply the database password. Users will be able to read this database password in the system table. (Some of my users know this trick!) But SQL statements are Greek to them, so I've tried to write a SQL statement in the SQL View pane, but when I try to run this append query, I receive the "Not a valid password" error. Here's my SQL statement:
INSERT INTO tblOrders IN 'C:\Data.mdb' SELECT * FROM tblDailyOrders;
These are the Property settings for the query:
Is there any way to provide the database password within the SQL statement?
A: Yes. You can specify the database password in the IN clause of an INSERT SQL statement in Microsoft Access. The syntax is a little different from a regular "IN" clause when there's a database password involved. To run your SQL statement directly from the query window, create a new query and copy/paste the following SQL statement into the SQL View pane:
INSERT INTO tblOrders IN '' [;DATABASE=C:\Data.mdb;PWD=myPassword]
SELECT *
FROM tblDailyOrders;
... then execute your query.
Note that Access will translate this into Jet SQL when you save it, so the saved query will use the following syntax, which doesn't include the IN clause:
INSERT INTO [;DATABASE=C:\Data.mdb;PWD=myPassword].tblOrders
SELECT *
FROM tblDailyOrders;
There are several variations available for the syntax, including using double quotes, but all (correct) variations will be translated into the above Jet SQL statement when the query is saved.
Of course, your database password will be visible in both the SQL statement and the destination connection string property of your query for everyone to read, so this isn't a very secure way to do it.
Instead of using this method, I recommend that you write VBA code for the query in a module using the DAO (see http://support.microsoft.com/default.aspx?scid=209953) or ADO (see http://support.microsoft.com/default.aspx?scid=191754) libraries and then compile the database into an MDE file so that the source code, including the database password for the remote database, cannot be viewed. And the original MDB file should be kept available so that changes can be made in the future and recompiled into a new MDE as the need arises, such as when the database password is changed periodically.
Top
How to use Jet SQL to update records in (or from) a remote database that has a database password.
Q: Using similar circumstances as the prior question, what syntax would I use to update records in a different table in the database that has the database password? For that matter, what syntax would I use to update records in the current database from records in the password-protected database?
A: You can use the same method to update records in a table in a database that has a database password, using values from a different table. For example:
UPDATE my_table IN '' [;DATABASE=C:\Data.mdb;PWD=myPassword]
SET myField = 'myValue'
WHERE anotherField =
(SELECT someField
FROM diffTable
WHERE criteriaField = 100);
. . . where "my_table" is a table in the remote database which contains "myField" and "anotherField" fields, and "diffTable" is a table in the current database, which contains "someField" and "criteriaField." Of course, the subquery in this example can only return one record.
Or if "my_table" is in the current database and "diffTable" is in the remote (password-protected) database, then this next example would work:
UPDATE my_table
SET myField = 'myValue'
WHERE anotherField =
(SELECT someField
FROM diffTable IN '' [;DATABASE=C:\Data.mdb;PWD=myPassword]
WHERE criteriaField = 100);
Again, the subquery in this example can only return one record.
Top
How to use a user-defined VBA variable as criteria in a query.
Q: Is it possible to use a global variable as criteria in a query?
A: You can use a variable as criteria in a query, but you'll need to do some prep work first. And this variable doesn't even need to be global. A module-level variable is just fine, and it's preferable too, because it's better for code maintainability and modularity.
In a standard module, define your module-level variable, then define a public function that sets the value of this variable, then define a public function that retrieves the value of this variable. Next, create your query that uses this "get" function as the criteria for selecting records.
When you need to run this query, first call the "set" function in the standard module to assign a value to the variable, then run the query. You won't be prompted to enter a value when the query runs.
Example code in a standard module (need to add error handling to functions):
Option Compare Database
Option Explicit
Private m_nAcctYear As Integer
Public Function setAcctYear(nYear As Integer)
m_nAcctYear = nYear
End Function
Public Function getAcctYear( ) As Integer
getAcctYear = m_nAcctYear
End Function
------------------------------
Example SQL statement:
SELECT *
FROM DeptSales
WHERE (DeptSales.AcctYear = getAcctYear( ));
------------------------------
Top
Where to find SQL developer tools to practice with.
Q: I'm new to SQL and I'm trying to learn, so after "playing" with SQL strings and ADO Recordsets in Access's VB Editor for a while, I wonder whether there are any SQL developer tools that run on Windows XP that I can practice with.
A: If you're new to SQL, then keep things simple and start with the Access SQL View pane for typing the SQL statements. It does't have any bells and whistles, but you'll see the SQL statement without any extra VBA verbage or requirements, such as embedding the statement within a string, and using concatenation and line continuations. There are limitations to the SQL View pane, such as only one SQL statement per query, maximum number of characters, maximum nested queries, limited complexity , et cetera. (Don't worry. It will be quite a while before you can dream up a SQL statement that's too complex for Jet.)
For appropriate SQL syntax, please see the following Web pages:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acf undsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/aci ntsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/ac advsql.asp
When you feel confident to move on to more complex statements or executing batch statements, you'll have the basis for the SQL dialects that each client/server database uses. You have quite a few choices, so I'll cover a few free or low-cost ones.
If you have Access 2000 or newer, then you can create an ADP project that uses the MSDE (Microsoft Database Engine) and create views (equivalent of queries) on the tables you create. You won't have SQL Server's Enterprise Manager and Query analyzer available, but you'll have the Access front end interface and command line OSQL.
You may download and install the free SQL Server 2005 Express Edition (beta 3):
http://lab.msdn.microsoft.com/express/sql
Oracle Personal Edition can be installed on Windows 2000/XP/Server 2003:
http://www.oracle.com/technology/tech/windows/faq.html
Download the free Oracle 9i Personal Edition:
http://www.oracle.com/technology/software/products/8i_personal/index.html
Download and install the free MySQL database engine:
http://www.mysql.com
You may purchase the SQL Server 2000 Developer Edition for about $50 (shop around and you'll find it cheaper on the Internet):
http://www.microsoft.com/sql/howtobuy/development.mspx
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
|
