Lesson 8 - More Complex Queries

Previous
Contents
Next
Lesson 8 - More complex queries

Monday, October 12, 2015
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-ofbirth 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

Lesson 8 - More Complex Queries

Uploaded by

Copyright:

Available Formats

Lesson 8 - More Complex Queries

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lesson 8 - More Complex Queries

Uploaded by

Copyright:

Available Formats

Previous

Lesson 8 - More complex queries

Working with dates

In Access, a date or time constant should be identified with # ... #, as in:

Queries that calculate

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

Update and Delete queries

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

You might also like