MS Access Date Increment?

Question

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.

Answer 1

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

_________

Source(s): http://office.microsoft.com/en-us/access/HA0122881... http://msdn.microsoft.com/archive/default.asp?url=...

Answer 2

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