Select range of monthly data

[Guest]

I am planning a database that tracks certain data that comes in as a monthly
total. So I have a figure for Jan-07, Feb-07, etc. I plan a field for the
total and a field for the month/year.

In queries I will want to be able to select data that might include a range,
such as "between Jan-07 and May-07".

My question is, for the field which stores the month and year that the data
covers, do I need to make it a date field and enter a complete date, i.e. Jan
30, 2007 in order to select such a range (e.g. select between 1/30/07 and
5/31/07) or does Access have some function that allows me to say select a
range of month/year combinations from a text field. Or is there some other
solution to this problem of monthly data?

Thanks,

Steve

 

[Steve]

Rather than using month/year use a MonthEndingDate. In your query you can
then use:
Between #1/1/07# And #5/31/07#

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Michael Gramelspacher]

I am planning a database that tracks certain data that comes in as a monthly
total. So I have a figure for Jan-07, Feb-07, etc. I plan a field for the
total and a field for the month/year.

In queries I will want to be able to select data that might include a range,
such as "between Jan-07 and May-07".

My question is, for the field which stores the month and year that the data
covers, do I need to make it a date field and enter a complete date, i.e. Jan
30, 2007 in order to select such a range (e.g. select between 1/30/07 and
5/31/07) or does Access have some function that allows me to say select a
range of month/year combinations from a text field. Or is there some other
solution to this problem of monthly data?

Thanks,

Steve

perhaps something similar to this,
but this is not tested.

CREATE TABLE MonthlySalesTotals
(store_nbr VARCHAR (10) NOT NULL,
sales_month DATETIME NOT NULL,
sales_total DECIMAL (12,4) NOT NULL,
PRIMARY KEY (store_nbr,sales_month));

Query: Sales Totals Last x Months
Assuming sales_month as 1st of month

PARAMETERS [Prior months to see:] Integer;
SELECT t.store_nbr,
FORMAT(t.sales_month,"mmm-yy") AS [Sales Month],
t.sales_total
FROM MonthlySalesTotals AS t
WHERE (((t.sales_month) BETWEEN
DATEADD("m",DATEDIFF("m",1,NOW()) - ([Prior months to see:] - 1), 1)
AND DATEADD("m",DATEDIFF("m",1,NOW()) - 1,1)))
GROUP BY t.store_nbr,t.sales_total,t.sales_month;

 

[Michael Gramelspacher]

perhaps something similar to this,

but this is not tested.

CREATE TABLE MonthlySalesTotals
(store_nbr VARCHAR (10) NOT NULL,
sales_month DATETIME NOT NULL,
sales_total DECIMAL (12,4) NOT NULL,
PRIMARY KEY (store_nbr,sales_month));

Query: Sales Totals Last x Months
Assuming sales_month as 1st of month

PARAMETERS [Prior months to see:] Integer;
SELECT t.store_nbr,
FORMAT(t.sales_month,"mmm-yy") AS [Sales Month],
t.sales_total
FROM MonthlySalesTotals AS t
WHERE (((t.sales_month) BETWEEN
DATEADD("m",DATEDIFF("m",1,NOW()) - ([Prior months to see:] - 1), 1)
AND DATEADD("m",DATEDIFF("m",1,NOW()) - 1,1)))
GROUP BY t.store_nbr,t.sales_total,t.sales_month;

Oops, should read: Assuming sales_month is last day of month.

and make line 7 of query
DATEADD("m",DATEDIFF("m",1,NOW()) - [Prior months to see:], 1)