Access tutorial

Previous page Previous     TOC Contents     Next pageNext


Lesson 8 for

More complex Queries






For more information and special deals related to any of the issues on this page, place your cursor over the double-underlined links.
All information supplied by Kontera.com.





Working with dates


Whenever you develop a commercial application, there is absolutely no way that you can get by without using date fields. The Video application has Movie Rental date, Return date, Date-of-birth, Report dates. The Dating Service has dates for dates. The Credit card has an Expiry date. There are Birth dates, Hire dates, Delivery dates, Order dates, and so on, and so on ....

In ancient times, like 20 years ago, dates were stored as strings and we all remember what that brought about in 1999. Now all DBMSs handle dates in a Date/Time format, which makes our lives a lot simpler, but we have to be aware of the particular properties of Date formats.

Before we begin writing queries there is an aside we must make about formatting.

Access is a Microsoft product, obviously. As with all Microsoft products it works closely with Windows, obviously. When it comes to formatting for different countries Access gets its information from the Windows Control panel.

The Windows default values are basically American. But, if you live in France for example, the standard date format is dd-mm-yy and not mm-dd-yy as you are used to. And some countries use the comma as a decimal separator and put the currency sign to the right of the amount and so on.

Therefore, before starting with Access you should go to the Control panel ---> Regional settings and adjust all the settings for your particular situation.

To avoid confusion with dates I preach and preach to all who will hear that you should automatically adopt the ANSI standard for dates and that is: yyyy-mm-dd. For example, January 1st 2003 should always be input into Access as: 2003-01-01.

In Access, a date or time constant should be identified with # ... #, as in:
... [m_videorelease] = #2001-01-01#;


To begin with, know that you can do some calculations with dates as you do with numbers.

#2003-01-31# - #2003-01-01# will return 30, the number of days between the 2 dates.

#2003-01-01# + 3 will return #2001-01-04# because a numeric constant is always taken to mean days.

When using the comparison operators, > #date1# is taken to mean later than or after and < #date1# is taken to mean earlier than or before.

The smallest date value refers to the earliest or oldest date. The person with the smallest date-of-birth is the oldest. This sometimes comes-in handy because you can sort people by age without having to actually calculate age: to get a list in ascending age order, sort on date-of-birth descending.

In the criteria BETWEEN #date1# AND #date2# sets a date between date1 and date2, inclusive.

To work with date fields we'll use the Date and Time functions that Access supplies. There are dozens of built-in functions that will allow us to manipulate dates and times in just about any way that you can imagine.

The function that will probably be used more than any other: Now( ) returns the current date from the system clock.

In Access and SQL, one of the most useful functions is called: DateDiff( )

DateDiff('interval', #date1#, #date2#) returns the time difference between date1 and date2, expressed in interval units which could be: days, months, years, weeks or hours.

The interval is specified as: 'd' for days, 'w' for weeks, 'm' for months and 'yyyy' for years.

For example:

Datediff('d', #2003-01-01#, now()) returns the number of days between January 1st, 2003 and today.

Datediff('m', p_StartDate, p_EndDate) returns the number of months between start date and end date, in this case, for a project.

If the result displays too many numbers after the decimal, use the ROUND(number, digits) function to display the number rounded to 'digits' positions after the decimal:
ROUND(Datediff('m', p_StartDate, p_EndDate), 2).


In theory, Datediff('yyyy', c_BirthDate, now()) returns the customer's age, expressed in years. In practice however, you will find that it works or doesn't work depending on whether the person has had his birthday yet this year or not. Usually it doesn'y work very well.

To calculate the exact age, the following formula is quite accurate. There may be a small variation of a day or so once in a while.

INT(Datediff('d', c_BirthDate, now())/365.25)

Calculate the number of days between birth and now and divide by the exact number of days in a year, which, as you know, is 365.25 and not 365. That takes leap years into account.

The Integer function, INT( ) , truncates the result so that 25.9 becomes 25, for example; the person is 25 years old until the day she turns 26; after the age of 5, you rarely hear people say that they are 25 and a half years old.
Queries that calculate

In the previous lesson we created several queries to get information from a table. But we always selected columns that had already been defined in the table.

We can also define a query that will do other calculations using data from the table.

As we saw in the previous section, we can calculate age if we know date-of-birth, as in the following example:







As you can see, to put calculations into a query:

Pick an empty column

Define a heading - a name followed by a colon - for example, Age:

When you use a column name in the calculation, it must be enclosed in square brackets as, [c_DOB]

All functions have parentheses - make sure all parentheses are balanced, especially if you use several functions


Obviously you can do more than calculate age with queries. Eventually you will use the technique to calculate sales amounts, stock quantities, etc.

For example, if you have a Sales table with a s_Quantity column and a s_Amount column, you would do:
LineTotal: [s_Quantity] * [s_Amount]

One of the frequent uses of this is to do concatenation, that is, stick two strings together.

For example, instead of having Last name and First name in separate boxes on a form, I want to see First name and Last name together.

To get that I have to put it into a query:









Update and Delete queries

90% of the time when you use a query it will be a Select query - one that displays information.

Once in a while you will have to do maintenance operations on the tables: change a group of prices, delete all obsolete items, etc. For those operations you use a different type of query called: an action query.

We'll look at two: the Update and the Delete.

First, create a new query and specify the table that will be affected, same as the Select query.

Then, choose the Query type from the toolbar.




For the Update query, specify the operation on the update line: what you want to change and the criteria: which records will be changed.




For the Delete query, all you have to specify is the Criteria.

You cannot ask to delete certain columns in certain records; you can only delete entire records.

After choosing Delete query, specify the Criteria to delete records:





Next week: Creating a Master/Detail Form for transactions. See you then!














Top



Home | Tutorials | Contact | Sitemap | Add URL | Privacy policy