Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1074
  • Last Modified:

create a timeline dynamically in access 2007

I would like to create a timeline table in access using a beginning and end date provided by the user. For example, a query prompts the user for two inputs, he types in 10/18/2008 and 10/20/2008. The query then creates a table which contains every hour between those two date:


DATE      HOUR
10/18/2008      0
10/18/2008      1
10/18/2008      2
10/18/2008      3
10/18/2008      4
10/18/2008      5
10/18/2008      6
10/18/2008      7
10/18/2008      8
10/18/2008      9
10/18/2008      10
10/18/2008      11
10/18/2008      12
10/18/2008      13
10/18/2008      14
10/18/2008      15
10/18/2008      16
10/18/2008      17
10/18/2008      18
10/18/2008      19
10/18/2008      20
10/18/2008      21
10/18/2008      22
10/18/2008      23
10/19/2008      0
10/19/2008      1
10/19/2008      2
10/19/2008      3
10/19/2008      4
10/19/2008      5
10/19/2008      6
10/19/2008      7
10/19/2008      8
10/19/2008      9
10/19/2008      10
10/19/2008      11
10/19/2008      12
10/19/2008      13
10/19/2008      14
10/19/2008      15
10/19/2008      16
10/19/2008      17
10/19/2008      18
10/19/2008      19
10/19/2008      20
10/19/2008      21
10/19/2008      22
10/19/2008      23
10/20/2008      0
10/20/2008      1
10/20/2008      2
10/20/2008      3
10/20/2008      4
10/20/2008      5
10/20/2008      6
10/20/2008      7
10/20/2008      8
10/20/2008      9
10/20/2008      10
10/20/2008      11
10/20/2008      12
10/20/2008      13
10/20/2008      14
10/20/2008      15
10/20/2008      16
10/20/2008      17
10/20/2008      18
10/20/2008      19
10/20/2008      20
10/20/2008      21
10/20/2008      22
10/20/2008      23

any thoughts on how to create this make table query?
0
Yaniv Schiff
Asked:
Yaniv Schiff
  • 3
  • 2
  • 2
  • +1
1 Solution
 
CRJ2000Commented:
The easiest method would be to use VBA. However, if you want to do it with a query, I would approach it in this manner:

1. Create a table (CALENDAR_DATE, for example) that has a list of all possible dates that the user could choose. Populate this table with one row per date.

2. Create a table (HOUR_OF_DAY, for example) that has the numbers 0 through 23 -- one row per hour.

3. Create a prompted Make Table query that includes both of those tables (without a join). The prompt should limit the dates on the CALENDAR_DATE table based on the user's input. By not joining the two tables together, you'll actually have a cartesian product, which will give you one row for each hour in each day returned in the query.

Chris
0
 
Dale FyeCommented:
I use a table and a query to generate a series of numbers, then use that query and the dateadd function to generate the Dates and Times.  See the attached.

I use tbl_Numbers and qry_Numbers or qry_Numbers_to_99 whenever I need a sequene of dates.
DateGenerator.mdb
0
 
Mark WillsTopic AdvisorCommented:
*laughing* so do I...

A dynamic query based on numbers to generate all dates / hours from [my_start_date] through to [my_end_date] :

SELECT dateadd("d",days.n,dateadd("h",hours.n,my_Start_date)) AS The_date
FROM numbers AS days, (SELECT n FROM numbers WHERE n between 0 and 23) AS hours
WHERE days.n<=datediff("d",My_Start_Date,My_End_Date)
ORDER BY days.n, hours.n;


and "numbers" is a table with one column "n" of integers starting from 0 and going to how ever many days, or the down arrow gets too boring to hold down. You only really need as many days out as they can specify a date range for...

I use the numbers table for a wide range of activities... So, generally have a few thousand numbers in there...

Dynamic-Calendar-Query.mdb
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Yaniv SchiffDirector of Digital Forensics Author Commented:
Great responses guys! Fyed's db is a little closer to what i'm looking for. I would like to use military times, however, fyed's number goes 1-24, when there is no 24th hour, so it should actually go 0-23. Not quite sure how to make this change. Mark's solution seems a little more elegant and simple, but it's not in military time which is how all my data is presented. Also, in Mark's db, when it gets to midnight on a day, it doesn't display a time, just the date.

Both samples are really very good. And i hope one of you wouldn't mind making these necessary changes.

Thanks!
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
Oh, forgot to mention, vba is fine, actually that is encouraged because my db is all run through vba.
0
 
Dale FyeCommented:
just change the criteria statement to:

BETWEEN 0 and 23
0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
Great Solution! Thanks.
0
 
Mark WillsTopic AdvisorCommented:
It was generating a datetime column, so really was displaying just the default datetime style.

I did notice your requirement had TIME as a seperate column, but thought if you were ever going to do any date type arithmetic, then it really is better as a datetime.

Anyway, just for "completeness" have a look at some of the options available :

SELECT  dateadd("d",days.n,dateadd("h",hours.n,my_Start_date)) AS The_date_time
    ,  format(dateadd("d",days.n,dateadd("h",hours.n,my_Start_date)),"yyyy-MM-dd hh:mm") as The_Formatted_Date_Time
    ,  dateadd("d",days.n,my_Start_date) As The_Date
    ,  hours.n As The_Hour
FROM numbers AS days, (SELECT n FROM numbers WHERE n between 0 and 23) AS hours
WHERE days.n<=datediff("d",My_Start_Date,My_End_Date)
ORDER BY days.n, hours.n;


or if you created just that original bit as a qry, then you could also do things like present the data as you want it, when you need to (because formatting of a datetime is normally done at the "presentation" layer not necessarily the "raw" data) and notice the ease with which we can do things like datediff (please type in 01 Sep 2010 to 31 Dec 2011 as your date range):

SELECT  format(The_date,"MM/dd/yyyy") as [DATE], datepart("h",The_Date) as [HOUR], datediff("h",The_Date,now()) as age_in_hours
FROM    qCalendar
WHERE   The_Date between now() and dateadd("h",-48,now())


Anyway, hope that does complete the picture for you, and yes I know that this thread has already been closed - just wanting to be "complete"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now