473,320 Members | 2,052 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query to Show all Date Records as well as Gaps

I have a set of dates. These dates are not continuous. Any ideas for designing a query for the required output? I have a list of “From Date” to set of “To Dates” as listed below.

The earliest From Date is 18-Feb-08

The last To Date is 11-Sep-08

Date From ----- Date To
18-Feb-08 ----- 30-Apr-08
01-May-08 ----- 03-Sep-08
04-Sep-08 ----- 11-Sep-08
13-Mar-08 ----- 30-Apr-08
01-May-08 ----- 11-Sep-08
02-Mar-08 ----- 04-Mar-08
15-Mar-08 ----- 19-Mar-08
02-Jun-08 ----- 11-Jun-08
09-Jul-08 ----- 11-Jul-08
18-Jul-08 ----- 20-Jul-08
27-Jul-08 ----- 29-Jul-08
31-Mar-08 ----- 30-Apr-08
01-May-08 ----- 05-Sep-08
18-Feb-08 ----- 29-Apr-08
31-Mar-08 ----- 30-Apr-08
01-May-08 ----- 01-Jun-08
12-Jun-08 ----- 08-Jul-08
12-Jul-08 ----- 17-Jul-08
21-Jul-08 ----- 26-Jul-08
30-Jul-08 ----- 04-Aug-08

I want a query that will output the dates as shown in the order below without a break from 18-Feb-08 to 11-Sep-08

Date From Date To
18-Feb-08 ----- 01-Mar-08
02-Mar-08 ----- 04-Mar-08
05-Mar-08 ----- 12-Mar-08
13-Mar-08 ----- 14-Mar-08
15-Mar-08 ----- 19-Mar-08
20-Mar-08 ----- 30-Mar-08
31-Mar-08 ----- 29-Apr-08
30-Apr-08 ----- 30-Apr-08
01-May-08 ----- 01-Jun-08
02-Jun-08 ----- 11-Jun-08
12-Jun-08 ----- 08-Jul-08
09-Jul-08 ----- 11-Jul-08
12-Jul-08 ----- 17-Jul-08
18-Jul-08 ----- 20-Jul-08
21-Jul-08 ----- 26-Jul-08
27-Jul-08 ----- 29-Jul-08
30-Jul-08 ----- 04-Aug-08
05-Aug-07 ----- 03-Sep-08
04-Sep-08 ----- 05-Sep-08
06-Sep-08 ----- 11-Sep-08

Any help will be appreciated. Thanks in advance.
May 21 '11 #1
6 2030
Stewart Ross
2,545 Expert Mod 2GB
There is no simple answer to what you have asked, as the two sets of dates appear totally unrelated. You have posted the exemplar dates without explaining how they are related, if indeed there is any relationship between them.

If they are as unrelated as they seem, it is not possible to design any form of query which can systematically process the first table to produce the second.

Unless you can explain how the two tables are algorithmically related I can only advise that the only solution I see is to design a look-up table which provides you with the date sequence you require, then populate the table by hand using Excel say to do so.

-Stewart
May 21 '11 #2
Hi Stewart,

If you look at the attached excel spreadsheet you may be able to get an idea of what I want to do.

The first tab has "RawData".

The second tab has the data arranged "RawData2PullIntoAccess".

The third tab has the "OutputRequired".

What I want to acheive is in the third tab "OutputRequired" . At present I am doing this manually and to prepare each document it takes me 2-3 hours! I have a back log of nearly 500 such files to prepare. If I am able to put data into a table in Access as shown in tab "RawData2PullIntoAccess" and click a button and get the output required, it will make my life very easy.

Thanks

Raghu
Attached Files
File Type: zip ARMY_Doe_8888888.zip (8.2 KB, 106 views)
May 22 '11 #3
Stewart Ross
2,545 Expert Mod 2GB
I really wish I could assist you, but unfortunately I can make no more algorithmic connection between the dates you provided in the sample and the output required than I could from post #1.

In the table below I list the dates you provided in the sample with their day of week and the difference in days between the date from and date to (using leading zeros to make formatting the table easier). The left pair of dates comes from your input table. The right pair comes from your required output table. I have corrected two typos where dates from 2007 were listed instead of 2008 (one in each table).

The sample worksheet relates to fortnightly payments, but as you will see from the number of days between the dates the output is not evenly spaced at all, nor does it appear to depend much on the dates of the input.

Expand|Select|Wrap|Line Numbers
  1. 18/02/08 Mon ; 30/04/08 Wed ; 072 ; 18/02/08 Mon ; 01/03/08 Sat ; 012
  2. 18/02/08 Mon ; 29/04/08 Tue ; 071 ; 02/03/08 Sun ; 04/03/08 Tue ; 002
  3. 02/03/08 Sun ; 04/03/08 Tue ; 002 ; 05/03/08 Wed ; 12/03/08 Wed ; 007
  4. 13/03/08 Thu ; 30/04/08 Wed ; 048 ; 13/03/08 Thu ; 14/03/08 Fri ; 001
  5. 15/03/08 Sat ; 19/03/08 Wed ; 004 ; 15/03/08 Sat ; 19/03/08 Wed ; 004
  6. 31/03/08 Mon ; 30/04/08 Wed ; 030 ; 20/03/08 Thu ; 30/03/08 Sun ; 010
  7. 31/03/08 Mon ; 30/04/08 Wed ; 030 ; 31/03/08 Mon ; 29/04/08 Tue ; 029
  8. 01/05/08 Thu ; 03/09/08 Wed ; 125 ; 30/04/08 Wed ; 30/04/08 Wed ; 000
  9. 01/05/08 Thu ; 11/09/08 Thu ; 133 ; 01/05/08 Thu ; 01/06/08 Sun ; 031
  10. 01/05/08 Thu ; 05/09/08 Fri ; 127 ; 02/06/08 Mon ; 11/06/08 Wed ; 009
  11. 01/05/08 Thu ; 01/06/08 Sun ; 031 ; 12/06/08 Thu ; 08/07/08 Tue ; 026
  12. 02/06/08 Mon ; 11/06/08 Wed ; 009 ; 09/07/08 Wed ; 11/07/08 Fri ; 002
  13. 12/06/08 Thu ; 08/07/08 Tue ; 026 ; 12/07/08 Sat ; 17/07/08 Thu ; 005
  14. 09/07/08 Wed ; 11/07/08 Fri ; 002 ; 18/07/08 Fri ; 20/07/08 Sun ; 002
  15. 12/07/08 Sat ; 17/07/08 Thu ; 005 ; 21/07/08 Mon ; 26/07/08 Sat ; 005
  16. 18/07/08 Fri ; 20/07/08 Sun ; 002 ; 27/07/08 Sun ; 29/07/08 Tue ; 002
  17. 21/07/08 Mon ; 26/07/08 Sat ; 005 ; 30/07/08 Wed ; 04/08/08 Mon ; 005
  18. 27/07/08 Sun ; 29/07/08 Tue ; 002 ; 05/08/08 Tue ; 03/09/08 Wed ; 029
  19. 30/07/08 Wed ; 04/08/08 Mon ; 005 ; 04/09/08 Thu ; 05/09/08 Fri ; 001
  20. 04/09/08 Thu ; 11/09/08 Thu ; 007 ; 06/09/08 Sat ; 11/09/08 Thu ; 005
If you could tell us what the algorithm you use when processing these manually we may be able to help, but the dates in and out remain unrelated so far as I can tell.

For clarification, what I mean when I say that there is no algorithm I can determine which will take me from dates in to dates out is that I cannot deduce from what you have posted any step-by-step instructions for processing the dates in your input table which will transform your input dates to the dates shown as outputs. This suggests that there are rules you are applying which are as yet unrevealed in your posts so far, and without which we are simply stuck.

-Stewart
May 22 '11 #4
Thanks for your reply. I have to write a full explination and post it this coming weekend.

Cheers
Raghu
May 23 '11 #5
Lysander
344 Expert 100+
I can sort of see what you are trying to do, but can see no way of doing it in a single query, or even a set of queries. You could do it by writing VBA code to process your data into a new table.

The 1st date in your From list is 18-Feb. You need to search the From and To list for the next date. That is 2-Mar in the From list, so take 1 day away from this to make 1-Mar for the To.

You write a record out to the new table 18-Feb - 1-Mar, make 2nd Mar the next From date and then search From and To for the next date after 2nd Mar. If the next date is in the To list, then use that as To, and add 1 day for the next From.

Keep writing the records out to the new table until you run out of dates.
May 23 '11 #6
will try your suggestion.

Cheers
Raghu
May 24 '11 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time =...
3
by: Ken Mylar | last post by:
I have a problem that just showed up and I'm not sure why. I have a query that is supposed to show all records that have an Inspection Due Date any time in the past up until the end of the...
16
by: William Buchanan | last post by:
Hi folks I want to show 2 records on a page side by side. Each record has an image which will be displayed and a bit of text. How can I do this? Thanks Will
1
by: NewUser | last post by:
I try this: A first "group" query (sorry if this isn't the right name) count the records from a table with a criteria (field1="A") and return counter1=10 records. A second "group" query count the...
1
by: scottmachado | last post by:
I am currently running a marco that run a query and emails the results as an attachment in excel format. If the query has no records, I would like to email "no records found" in the first cell in...
2
by: Zeljko | last post by:
I'm creating Address book. Header of the main form (frmAddress) contains combo box (cboFilter) to filter records by Occupation on main Form(Ocupation1). That's working. Combo box also have "Show...
1
mkremkow
by: mkremkow | last post by:
Access 2003 I am trying to make my query exclude all records where the Request Delivery Date is not blank AND is not 03/31/2008. The SQL reads as follows: WHERE (((.) Is Not Null) AND...
1
by: veaux | last post by:
Might not have explained this correctly in subject, but query results look like below: Name ID Phone Bill 001 123 Bill 001 234 Bill 001 ...
3
by: darnnnel | last post by:
Hey guys, i have this code: SELECT * FROM QueryCases WHERE Weekday(QueryCases.StartDate) In ('1','7') AND QueryCases.Completed = False ORDER BY QueryCases.StartDate; the WHERE...
8
by: Dr Al | last post by:
I have a table with four date fields, some of which may not be filled in based on our data entry needs. I have a criteria set as <date()-180 which is supposed to pull dates older than 180 days ago....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.