MS Access Date Increment?

I need to write a query that will give me every date in a range, regardless of data associated with that date. Specifically, I need to find out the total $ in orders placed on each day in the range, even if the value is $0 for that day. Unfortunately, if there's $0 for that day, there's no entry in our database for that day.

2 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    Use a correlated subquery. A correlated subquery can't be evaluated independently of its outer query; it's an inner query that depends on data from the outer query. A correlated subquery is used if a statement needs to process a table in the inner query for each row in the outer query. A correlated subquery is executed repeatedly—once for each candidate row selected by the outer query.

    The basic syntax of a query that contains a correlated subquery is:

    SELECT outer-columns

    FROM outer_table

    WHERE outer_column_value IN

    (SELECT inner_column

    FROM inner_table

    WHERE inner-column = outer_column)

    __________

    SUGGESTION:

    First, use a Loop that calls the DateAdd() function to generate the consecutive date values for each day in a given month. Next, create a pseudo-table (virtual table) that is populated with the results of return values of the DateAdd() function. Loop through the new date table in the outer SELECT clause, and use the inner SELECT clause to get the sales for each day of the month.

    _______

    OTHER RELATED FUNTIONS

    _______

    Note: Access also has Day, Week, and Month functions.

    -----------

    DateSerial() function: Returns a Variant (Date) for a specified year, month, and day.

    Syntax:

    DateSerial(year, month, day)

    This example uses the DateSerial function to return the date for the specified year, month, and day:

    Dim MyDate

    ' MyDate contains the date for February 12, 1969.

    MyDate = DateSerial(1969, 2, 12) ' Return a date.

    -----------

    Because dates are stored as numbers, you can subtract two dates to find the number of days between them, or add a number and a date together to find another date either in the future or the past. Try the following examples in the Immediate window of a module.

    Find the number of days since the beginning of the year:

    Print #7/7/93# - #1/1/93#

    187

    Find the date 45 days in the future:

    Print #7/7/93# + 45

    8/21/93

    Find the date two weeks ago:

    Print #7/7/93# - 14

    6/23/93

    _________

  • Anonymous
    5 years ago

    Suppose u r making a database of students in ur college u may encounter cases where 2 students may have the same name or same Date of Birth so in processing this datbase if u want to select students with names as "Andrew" u may get multiple records. So it identify to records from each other we use Primary Keys. Primary keys are fields whose values cannot be null or duplicates Example : Social Security No or Student Roll Nos

Still have questions? Get your answers by asking now.