CODEWITHSUNDEEP
sqlms-accessrange
rfg
rfg

Reputation: 1636

How to create a sql query with ranges from unrelated table in ms access?

I have an ms access 2010 database with two unrelated tables: Days and Periods. Like these:

Days
-----
Day (Date)
Value (Integer)

and

Periods
-----
PeriodNum (Integer)
StartDate (Date)
EndDate (Date)

I want to get a new table, like this:

Periods_Query
-----
PeriodNum (Integer)
Value (Integer) - sum of all Values from table Days, where Day is in between 
                  StartDate and EndDate

I tried to build an SQL query, but i don't know how to get ranges. Tried somethig like this but it didn't work:

SELECT Period, Sum(Value) FROM Days, Periods;

So, is there a way to build such a query?

Thanks.

Upvotes: 1

Views: 1531

Answers (3)

mrak
mrak

Reputation: 2906

Another approach would be to use SUM over subselect (Tested in Access 07):

SELECT Periods.PeriodNum, Periods.StartDate, Periods.EndDate, (SELECT SUM(DayValue) FROM Days WHERE Day BETWEEN Periods.StartDate AND Periods.EndDate) AS DaySums FROM Periods;

Upvotes: 0

HansUp
HansUp

Reputation: 97101

Start with a plain SELECT query to consolidate the base data as you wish.

SELECT d.Date, d.Value, p.[Period #], p.StartDate, p.EndDate
FROM Days AS d, Periods AS p
WHERE d.Date BETWEEN p.StartDate AND p.EndDate;

Then convert to an aggregate (GROUP BY) query to compute the sums.

SELECT p.[Period #], Sum(d.Value) AS SumOfValue
FROM Days AS d, Periods AS p
WHERE d.Date BETWEEN p.StartDate AND p.EndDate
GROUP BY p.[Period #];

I got the impression you may want to store that result set in another table. However, you may decide that is unnecessary because you can use the query everywhere you would have used another table. However if you do need to store the result set, you can convert to an INSERT query.

INSERT INTO Periods_Query(PeriodNum, [Value])
SELECT p.[Period #], Sum(d.Value) AS SumOfValue
FROM Days AS d, Periods AS p
WHERE d.Date BETWEEN p.StartDate AND p.EndDate
GROUP BY p.[Period #];

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

After you create your table, empty,

run the following insert statement:

 Insert Periods_Query(PeriodNum, SumValues)
 Select p.PeriodNum, Sum(d.Value)
 From Periods p
    Join Days d On 
       d.Day Between p.StartDate And p. EndDate
 Group By p.PeriodNum

Upvotes: 0

Related Questions