Repeat record based on the start and end date

[Kpatel]

I have a table with the following data:

Job# Weight StartDate EndDate
12345 1000 9/1/06 9/3/06
67890 500 9/1/06 9/5/06

Following is the result that I am looking for

Date SumOfWeight
9/1/06 1500 (this would be total of job 12345 & 67890)
9/2/06 1500 (this would be total of job 12345 & 67890)
9/3/06 1500 (this would be total of job 12345 & 67890)
9/4/06 1000 (this would be total of job 67890 and
12345 has ended)
9/5/06 1000 (this would be total of job 67890 and
12345 has ended)

Could anyone tell me how can I make my query do this? Any help will be
greatly appreciated. Do let me know if you need any further details.

 

[John Spencer]

UNTESTED SOLUTION

Easiest way would be to build a calendar table with all the relevant dates
in it. For your example you would only need dates from 9/1/06 to 9/5/06,
but in real world you probably would need a couple years worth of dates.

Easiest way to do that might be to use Excel to enter the first two dates
and then do a fill down to get the sequential dates. Copy and paste into an
Access table.

Once you have that built, set up a query
Assumptions:
-- Table named CalendarTable with one field -TheDate- which contains unique
dates and all the dates in the needed range ( good primary key candidate
field)

Query should look something like
SELECT CalendarTable.TheDate
, Sum(Weight) as TotalWeight
FROM YourTable INNER JOIN CalendarTable
ON (CalendarTable.TheDate Between YourTable.StartDate and YourTable.EndDate)
GROUP BY CalendarTable.TheDate

 

[Kpatel]

Thanks for your response, however, is there any other possible soulution to
this because the data that I gave as an example was just a sample. In my
database I have entries since 1/1/2003 and is still being updated with new
records everyday. Creating a calendar table does not seem practical. I was
thinking if it was possible to create a table from the current table that
replicates the record for every single day in the date ranges. For example a
table that has the following data,

Job# Weight Date
12345 1000 9/1/06
12345 1000 9/2/06
12345 1000 9/3/06
67890 500 9/1/06
67890 500 9/2/06
67890 500 9/3/06
67890 500 9/4/06
67890 500 9/5/06

This could really solve my problem. However, I do not know how I can do
this. I know this is possible with recordset properties. But I have no idea
how I could use this to make my thing work. I have never used it before.

Any help will be greatly appreciated.

Thanks

 

[John Spencer]

Using Excel you should be able to build the Calendar data in less than 10
minutes for 10 years. That is only around 3,652 rows. That is not much
work.

Open Excel
Enter 1/1/2003 in the first row, first cell
Enter 1/2.2003 in the second row, first cell
Select both cells
At the bottom of the second cell, grab the little black square in the bottom
right-hand corner
Drag down

Select All
Open your database
Create a table named CalendarTable with one field named TheDay
Click on the column header, Paste

Table is built. ( I just did exactly that and it has taken me longer to
type this up then it did to create the table).

If this method doesn't work, then post back. My feeling is that this is the
best way to do this and the most flexible.

You could add a century's worth of records in almost the same amount of
time.

If you want to see something more complex
http://www.aspfaq.com/show.asp?id=2519

Explains the concept of having a calendar table to use in calculating
workdays, specific days in a month, etc. That requires a more extensive
table and more extensive knowledge of SQL.

If you want another solution, open a new post so someone else will answer
it.

 

[KARL DEWEY]

Create a table with numbers from 0 (zero) to the highest date spread you
expect to have. Use this query.
SELECT [StartDate]+[CountNUM] AS Daily, Sum(Kpatel.Weight) AS SumOfWeight
FROM Kpatel, CountNumber
WHERE ((([StartDate]+[CountNUM])<=[EndDate]))
GROUP BY [StartDate]+[CountNUM];