Solved

create a timeline dynamically in access 2007

Posted on 2010-09-23
8
1,033 Views
Last Modified: 2012-06-27
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
Comment
Question by:Forensicon
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

by:CRJ2000
Comment Utility
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
*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
 

Author Comment

by:Forensicon
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Forensicon
Comment Utility
Oh, forgot to mention, vba is fine, actually that is encouraged because my db is all run through vba.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
just change the criteria statement to:

BETWEEN 0 and 23
0
 

Author Closing Comment

by:Forensicon
Comment Utility
Great Solution! Thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now