Jet SQL Examples

NOTE:
Are you developing queries to use in your VBA code? Or troubleshooting SQL from your VBA code? This Microsoft Access Team blog entry may provide valuable insights for you. Worth checking out.

Categorize Records In A Query

A common request is to categorize records into different categories based on a value in a field or fields. A common solution used is to hard-code the values in a query or use VBA code to accomplish this where the best answer almost always involves using a table to define the categories.

Defining the categories in your SQL makes future category range changes difficult. Same with a coded solution. It is usually easier to define the categories in a table and use a join to dynamically categorize at query time. Here is an Employee table and AgeRange table:

tblEmployee:
  EmpID - Long (PK)
  EmpName - Text(25)
  EmpAge - Long

EmpID     EmpName       EmpAge
1         Paul          21
2         Steven        31
3         Tom           41
4         Joe           51
12        Joe           61
14        Tim           71
15        Phil          55
16        Joe           35
17        Karen         26
19        Jill          23
22        Raymond       43

tblAgeRange:
  AgeMin - Long (PK)
  AgeMax - Long
  AgeCategory - Text(50)

AgeMin    AgeMax    AgeCategory
18        25        Young Adult
26        30        Late Twenties
31        40        No Longer Young
41        50        Looking Forward To AARP
51        60        Looking Back At Twenties
61        150       Done For
            

By joining these two tables together using the EmpAge field from tblEmployee we can determine the appropriate age category:

SELECT tblAgeRange.AgeCategory, tblEmployee.*
FROM tblEmployee LEFT JOIN tblAgeRange ON (tblEmployee.EmpAge <= tblAgeRange.AgeMax) AND (tblEmployee.EmpAge >= tblAgeRange.AgeMin)
ORDER BY tblEmployee.EmpAge;
            

and get results like this:

EmpID     EmpName   AgeCategory
1         Paul      Young Adult
2         Steven    No LongerYoung
3         Tom       Looking Forward To AARP
4         Joe       Looking Back At Twenties
12        Joe       Done For
14        Tim       Done For
15        Phil      Looking Back At Twenties
16        Joe       No Longer Young
17        Karen     Late Twenties
19        Jill      Young Adult
22        Raymond   Looking Forward To AARP
            

Re-defining the minimum and maximum ages for each range are done in the tblAgeRange table and no other modification is required. An application would typically provide a configuration screen for the user to re-define category limits. Re-running the query will reflect those changes.

Updated 2009-04-24.

Ranking Records

Another common request is to rank records, or a subset of records, against each other. Think of sports box scores, top ten lists or consumer product rankings. Take this example table:

tblScore:
  CompID   - Long (PK)
  PlayerID - Long
  Score    - Long

CompID    PlayerID  Score
1         1         39
1         2         38
1         3         32
2         1         41
2         2         39
2         3         41
3         1         42
3         2         41
3         3         43
4         1         39
4         2         33
4         3         42
5         1         44
5         2         42
5         3         28
6         1         39
6         2         42
6         3         47
            

Typically some subset of records needs to be defined (e.g. CompID = 6) and then a sequential number needs to be generated to indicate ranking. A sub-query is used to count the number of records that meet or exceed the current record's Score value and this becomes the ranking. This sub-query becomes a correlated sub-query because of the need to only count scores for our subset of records and and to count records that meet criteria found in the outer query:

SELECT 
  tblScores.PlayerID
, tblScores.Score
, (SELECT Count(*) FROM tblScores As S WHERE S.CompID = 6 AND S.Score >= tblScores.Score) AS Rank
FROM tblScores
WHERE (((tblScores.CompID)=6))
ORDER BY tblScores.Score DESC;            
            

And the results look like this:

PlayerID  Score     Rank
3         47        1
2         42        2
1         39        3
            

Additionally it may be desirable to calculate a "behind" value. This query generates that value:

SELECT 
  tblScores.PlayerID
, tblScores.Score
, IIf( ((SELECT Max(Score) FROM tblScores WHERE CompID = 6) - tblScores.Score) = 0, "----"
  , ((SELECT Max(Score) FROM tblScores WHERE CompID = 6) - tblScores.Score)) AS Behind
, (SELECT Count(*) FROM tblScores As S WHERE S.CompID = 6 AND S.Score >= tblScores.Score) AS Rank
FROM tblScores
WHERE (((tblScores.CompID)=6))
ORDER BY tblScores.Score DESC;            
            

and the results look like this:

PlayerID  Score     Behind    Rank
3         47        ----      1
2         42        5         2
1         39        8         3          
            

See a more advanced version of ranking - only within a group further down the page.

Updated 2009-04-24.

Repeating Records

Sometimes it is desired to repeat a record multiple times in a query. Perhaps the query is a recordsource for a report that is printing mailing labels where some duplication of labels is needed. One technique to do this is to have a table that specifies the number of repeats needed. This table is then joined to a simple table holding sequential numbers. The image below outlines the table and working query.

This works fine where the number of repeats doesn't require a large table of sequential numbers. Also, for demonstration purposes here I used a table that contains the output data with the number of repeats needed. Typically you would not store a "Repitions" column in any permanent table using a temporary table instead where you would have the source table's Primary Key value and the Repitions value and update the query appropriately. For example:

Just a quick note on temporary table use in Access before I leave this snippet. Creating and deleting tables in a production environment Access application can cause the .accdb/.accde/.mdb/.mde file size to swell. Compacting the database returns the file size to something closer to deployment size. A nice alternative is to create your temporary tables outside your application in a temporary Access file. See the details on Tony Toews' valuable Access site.

Updated 2009-04-25.

Combining (One Row) Parameter Tables

Applications often have single row tables to store things like app or user parameters. A common method to ensure the table only has one record is to set up a numeric field (perhaps "ParamID" as type Long) and set as the Primary Key. Set the Default Value for the table to 0, the Validation Rule for that field to =0 and set some Validation Text such as "Only one record allowed in this table."

If the app has both an AppParam table for general settings and a UserParam table for user-specific setting you could combine them as one row in a query by creating a temporary field in the query that is only used to join the two tables together.

SELECT sp.*, up.* 
FROM 
(
  ( SELECT *, "A" as addedField FROM SysParam)  AS sp
  INNER JOIN 
  ( SELECT *, "A" as addedField FROM UserParam) AS up
  ON sp.addedField = up.addedField
)
            

Updated 2009-04-25.

Identifying Missing Values In Numeric Field

Reporting on missing values in a numeric sequenced value is handy. This query will identify gaps in a numeric field.

Updated 2009-04-25.

Query Data In Two External Access Files and Join Results

Not all Access developers are aware of the ability for Jet queries to join on tables in external database files so here is a quick example:

SELECT c.*, d.*
FROM (;database=c:\src\football\k_data.mdb) Team AS c 
INNER JOIN (;database=c:\src\football\k_data_may_2004.mdb) Team AS d 
ON c.TeamID = d.TeamID;
            

Updated 2009-04-25.

Sum By Category With Running Sum

Running sum queries are used for reporting. This query example sums by category and displays a running sum.

Updated 2009-04-25.

Compare Value to Previous Row's Value

If you records have a value that allows you to order them then you can compare a field to the same field in the previous record. In this sample we compare the purchase price of an item to the last purchase price for that item.

Updated 2009-05-05.

Ranking Scores In Multiple Competitions (Group)

A reader asked if their query could list individuals and their scores in various competitions and rank them for the individual - a way to see that individual's on-going performance.

But this query could easily be car sales by model by store or individual blood pressure readings for various units of a medical facility.

The more challenging part of the request was a way to break ties for an individual so that the ranking was sequential - that is, there were no duplicate rank values. Typically when there is a tie both entrants are given the same rank and the next rank values are skipped. In this example we break the tie by adding a tie breaker value using a SELECT subquery that returns a value added to the rank to break the tie.

I look for matching PlayerIDs and Scores and if found count the number of CompIDs that are less than the current CompID. Usually this results in 0 until a tie is found and then a value is returned that is added to the rank.

Updated 2009-07-19.

Display Top X Records For Each Group

An Utter Access reader posted a question about taking data like this:

METER_ID, READ_DATE, METER_READ
Meter_1, 2/3/2009, 4323
Meter_2, 2/4/2009, 4194
Meter_3, 2/3/2009, 4274
Meter_4, 2/4/2009, 4596
Meter_1, 3/9/2009, 4843
Meter_2, 4/2/2009, 4913
Meter_3, 3/11/2009, 5012
Meter_4, 3/12/2009, 4673
Meter_1, 5/6/2009, 5456
Meter_2, 5/7/2009, 5681
Meter_3, 5/7/2009, 5231
Meter_4, 5/7/2009, 4984
            

and outputting to this format where the two most recent meter readings for each meter looks like this:

METER_ID, DATE_1, READ_1, DATE_2, READ_2
Meter_1, 3/9/2009, 4843, 5/6/2009, 5456
Meter_2, 4/2/2009, 4913, 5/7/2009, 5681
Meter_3, 3/11/2009, 5012, 5/7/2009, 5231
Meter_4, 3/12/2009, 4673, 5/7/2009, 4984
            

Here is one way to use Jet SQL to output the desired info. First make a query that pulls all the records from the Meter table and assigns a Relevance value based on the READ_DATE for each unique meter. The most recent reading for a particular meter gets a Relevance value of 1, the next most recent reading for the meter gets a relevance of 2, etc.

SELECT 
  m.METER_ID
, m.READ_DATE
, m.METER_READ
, (SELECT COUNT(*) FROM Meter WHERE METER_ID = m.METER_ID AND READ_DATE >= m.READ_DATE) As Relevance
FROM Meter m;
            

Next filter the query to include only those with a value of 1 or 2. The result is the last two readings for each meter.

SELECT 
  m.METER_ID
, m.READ_DATE
, m.METER_READ
FROM Meter m 
WHERE (SELECT COUNT(*) FROM Meter WHERE METER_ID = m.METER_ID AND READ_DATE >= m.READ_DATE) < 3 
ORDER BY 1, 2 DESC;
            

But to join the two rows into one for each meter I used two queries, one for each relevance value and I'll join them together based on the meter ID:

SELECT * FROM 
  ( SELECT 
      m.METER_ID
    , m.READ_DATE As READ_DATE_1
    , m.METER_READ As METER_READ_1
    FROM Meter m 
    WHERE (SELECT COUNT(*) FROM Meter WHERE METER_ID = m.METER_ID AND READ_DATE >= m.READ_DATE) = 1 
  ) As t1
  LEFT JOIN
  ( SELECT 
      m2.METER_ID
    , m2.READ_DATE As READ_DATE_2
    , m2.METER_READ As METER_READ_2
    FROM Meter m2 
    WHERE (SELECT COUNT(*) FROM Meter WHERE METER_ID = m2.METER_ID AND READ_DATE >= m2.READ_DATE) = 2 
  ) As t2
  ON t1.METER_ID = t2.METER_ID ;
            

which provides output that looks like this:

t1.METER_ID READ_DATE_1 METER_READ_1      t2.METER_ID READ_DATE_2 METER_READ_2
Meter_1     5/6/2009    5456              Meter_1     3/9/2009    4843
Meter_2     5/7/2009    5681              Meter_2     4/2/2009    4913
Meter_3     5/7/2009    5231              Meter_3     3/11/2009   5012
Meter_4     5/7/2009    4984              Meter_4     3/12/2009   4673
            

Updated 2009-10-13.

Show Data For Each Date Between Two Dates

Another Utter Access reader posted a question about displaying data for each date between a start and end date entered when running a query.

The goal was to take a Reservations table (see image below) where each record has a start and end date and generate a record for each reservation for each date. This could then be used to sum up the number of room reservation days as, perhaps, a performance measure for the business or just to get a daily count of reserved rooms.

I then generated a "YearDates" table that has a single field "DOY" (day of year) which is each day of the year. Some VBA code can quickly generate any number of years of dates for this table. I am going to use this table to join to the Reservations table in a query so that I can end up with a record for each date and reservation.

The SQL WHERE clause needs to select appropriate records based on the user entering a Start Date and End Date when running the query. At least one day of the reservation must be between, or equal to one of, the Start and End dates of the reservation.

The way I joined the RESERVATIONS and YEARDATES tables together is unusual in that it does not join on a field in each table with equal values but instead joins a range of rows from the YEARDATES table for each RESERVATION ROW.

I used a comparison in the SQL WHERE clause to limit the resulting output records to those between the Start and End date that the user enters when running the query. In the above image you can see the SQL and the results of running the query where I requested reservations for the week of 1/14/2009 to 1/20/2009.

There are pros and cons to this approach.

PROS:

- no VBA coding needed

CONS:

- have to generate a table of dates. This is a one-time activity but some developers don't like using generated tables.

- query performance may be an issue with a really large number of records in your tables (3 years of reserverations in a 50 room hotel could generate 50,000 records).

Here is the SQL in a code block that you can copy:

SELECT 
  YearDates.DOY, Reservations.BookingID, Reservations.NumRooms
FROM 
  Reservations 
INNER JOIN 
  YearDates 
ON 
  (YearDates.DOY >= Reservations.StartDate) AND (YearDates.DOY <= Reservations.EndDate)
WHERE 
(
  (YearDates.DOY)>=[Enter Start Date] 
  AND (YearDates.DOY)<=[Enter End Date]
)
AND
(
 ((Reservations.StartDate)<[Enter Start Date]) 
    AND ((Reservations.EndDate)>=[Enter Start Date]) 
 OR ((Reservations.StartDate)=[Enter Start Date]) 
 OR ((Reservations.StartDate)=[Enter End Date])
 OR ((Reservations.StartDate)>[Enter Start Date] 
     AND (Reservations.StartDate)<=[Enter End Date]) 
)
ORDER BY 
  YearDates.DOY, Reservations.BookingID;
            

It would be easy to create a cross-tab query with the results of my query to get the data in the format you displayed in your post. I'd love to hear about alternate solutions and opinions from other members on this post.

Updated 2009-12-04.