Create a List of Dates in Access Query

Question

I have a MakeTable query in Access that I am working on. I want to make it so that one of the fields in the table being created will be a list of dates in chronological order from 1/2/2015 to 11/30/2015. How is this done in Access query design view?

Answer 1

You can create a query:

SELECT DISTINCT 
    Abs([id] Mod 10) AS N
FROM 
    MsysObjects;

Save this as qdxNumber10

Now create a query with a Cartesian Join (multiplying) to generate your dates:

SELECT 
    DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,#2/1/2015#) AS [Date]
FROM 
    qdxNumber10 AS qdxNumber10_0, 
    qdxNumber10 AS qdxNumber10_1, 
    qdxNumber10 AS qdxNumber10_2
WHERE 
    DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,#2/1/2015#) <= #11/30/2015#;

Use this as source when you create your table.

To generate all dates for the current year, use:

SELECT 
    DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,DateSerial(Year(Date()),1,1)) AS [Date]
FROM 
    qdxNumber10 AS qdxNumber10_0, 
    qdxNumber10 AS qdxNumber10_1, 
    qdxNumber10 AS qdxNumber10_2
WHERE 
    DateAdd("d",[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100,DateSerial(Year(Date()),1,1)) <= DateSerial(Year(Date()),12,31);

or use DateSerial directly:

SELECT 
    DateSerial(Year(Date()),1,1+[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100) AS [Date]
FROM 
    qdxNumber10 AS qdxNumber10_0, 
    qdxNumber10 AS qdxNumber10_1, 
    qdxNumber10 AS qdxNumber10_2
WHERE 
    DateSerial(Year(Date()),1,1+[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100) <= DateSerial(Year(Date()),12,31);

Answer 2

I'd create a table with three fields:

[Snapshot Date]   date
[CountA]          number
[CountB]          number

Fill the Snapshot Date column in with a wide range of dates (yours inclusive) -- you can do this through code or just cut/paste from a spreadsheet.

Then save a crosstab query similar to this:

TRANSFORM Avg([All Dates].[CountB]) AS DeleteMe
SELECT [All Dates].[CountA]
FROM [All Dates]
WHERE [All Dates].[Snapshot Date] Between #1/2/2015# and #11/30/2015#
GROUP BY [All Dates].[CountA]
PIVOT [All Dates].[Snapshot Date];

(once you save this query, you should be able to switch to design view and see what the designer looks like)

Assuming the crosstab query above was named All Dates Crosstab, you could:

select *
into [My New Table]
from [All Dates Crosstab]