Any idea for a query?

[raghuprabhu]

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.

Raghu Prabhu

 

[Guus2005]

Just sort the records on the column StartDate.

Perhaps i am missing the point. Please explain.

 

[raghuprabhu]

Hi,

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

 

[Guus2005]

Is the format of the RawData sheet fixed?
Does "Date From" always start on A6 and L6?

You can automate this proces when the format is fixed more easily then when it is not.
If it is not you need to search for the "Date From" field programmatically and work your way from there.

You could make it a lot easier if you remove the empty lines on L15 - P15 etc.

It is not easy but it can be done.
I can only point you in a direction and help you along the way. It is too specific and time consuming to do it for you.

HTH

 

[raghuprabhu]

Hi Guus2005,

Please look at the tab in the spreadsheet "RawData2PullIntoAccess" and the attached database.

The database has the same data as in the tab "RawData2PullIntoAccess".

I don't know how to take it further after this.

Any suggestions will be welcome.

Thanks in advance.

Regards
Raghu

 

[Guus2005]

You can read the spreadsheet into Access using the TransferSpreadsheet method of the DoCmd command. It should look like this more or less.

docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12,"FirstPart","C:\temp\army_doe_88.xls",true, "A5:J7"
docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12,"SecondPart","C:\temp\army_doe_88.xls",true, "A11:D11"
docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12,"ThirdPart","C:\temp\army_doe_88.xls",true, "L5:P23"

If the spreadsheet looks exactly like the sample, this is your solution to import the spreadsheet into Access.

Something tells me that is not the case...

 

[raghuprabhu]

Thanks a lot GUUS2005. It is not that simple. Please look at the attached database I attached here in ForComsuper.zip. I am working on queries to make the dates look like in the list I want. Once I do that I may be adle to do the rest slowly. If and when I solve it I will post it on the net.

Cheers
Raghu

 

[raghuprabhu]

You can read the spreadsheet into Access using the TransferSpreadsheet method of the DoCmd command. It should look like this more or less.

docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12,"FirstPart","C:\temp\army_doe_88.xls",true, "A5:J7"
docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12,"SecondPart","C:\temp\army_doe_88.xls",true, "A11:D11"
docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12,"ThirdPart","C:\temp\army_doe_88.xls",true, "L5:P23"

If the spreadsheet looks exactly like the sample, this is your solution to import the spreadsheet into Access.

Something tells me that is not the case...

Hi Guus2005 and other members of the Access family,

I have attached a small excel spread sheet. Please look at it. It has two work sheets. "List of Overlapping Dates" and "Dates in Continuous Order"

My database has a table with many fields of which two are "FromDate" and "ToDate".

The dates are as in "List of Overlapping Dates" and I want a query whose output will be as the dates as in "Dates in Continuous Order"

If you have any ideas it would be thankful. Once I get this dates in continous order I can manage the other things.

Thanks in advance

Cheers
Raghu

 

[Guus2005]

You are changing the question as you go. Looking at both sheets there doesn't seem to be any logic or relation between the two sheets.

In the picture i attached you can see that column A and B are copied from sheet "List of overlapping dates".

28-jun-10, 10-nov-10 is translated to 28-jun-10, 13-jul-10
14-jul-10, 22-jul-10 is translated to 14-jul-10, 22-jul-10

I can follow the logic so far but row 6 and 7 both start with 27-jul-10 and then the logic is gone...

This is very difficult to automate with queries only. You need to write some code to create the additional records.

HTH

 

[raghuprabhu]

Hi Guus2005,

If you look at both the worksheets you can see that the dates are overlapping in the first sheet and they are continious in the second. I have drawn a pictorial graph to make it simpler. I am trying some things. Hope I will be able to sort things. Will keep you posted.

Thanks.

Raghu

 

[Uncle Gizmo]

Hi Raghu,
We spoke earlier on the telephone. This is my take on your problem.

I think the main problem is that you know what you want, however it is not clear to me and I guess so from their comments, the other respondents are not clear on what you want either.

You also are not answering the questions asked of you; you just direct our attention to your spreadsheet. I believe you are doing this because your question is so clear to yourself that you don’t realise others are having difficulty understanding it. However the consequence of not answering people’s questions is that they lose confidence in
you and will withdraw from the thread.

I will now post a question and await your answer with interest.

 

[Uncle Gizmo]

From your first list in this thread:
http://www.access-programmers.co.uk/forums/showpost.php?p=1068755&postcount=1

You have:

Date From ----- Date To
18-Feb-08 ----- 30-Apr-08
18-Feb-08 ----- 29-Apr-08

In the second list you show:

Date From Date To
18-Feb-08 ----- 01-Mar-08

What rules govern the result produced in the second list?

 

[Uncle Gizmo]

From your first list in this thread:
http://www.access-programmers.co.uk/...55&postcount=1

Again you have:

Date From ----- Date To
01-May-08 ----- 03-Sep-08
01-May-08 ----- 11-Sep-08
01-May-08 ----- 05-Sep-08
01-May-08 ----- 01-Jun-08

In the second list you show:

Date From Date To
01-May-08 ----- 01-Jun-08

What rules govern the result produced in the second list in this second case?

 

[Uncle Gizmo]

Now this one is somewhat different, you have:

Date From ----- Date To
13-Mar-08 ----- 30-Apr-08
02-Mar-08 ----- 04-Mar-08
15-Mar-08 ----- 19-Mar-08
31-Mar-08 ----- 30-Apr-08 --- (Note 2 Entries the same)
31-Mar-08 ----- 30-Apr-08 --- (Note 2 Entries the same)

In the second list you show:

Date From Date To
02-Mar-08 ----- 04-Mar-08

What rules govern the result produced in the second list in this 3rd case?

 

[Uncle Gizmo]

Now this one is completely different, you have:

Date From ----- Date To
13-Mar-08 ----- 30-Apr-08
02-Mar-08 ----- 04-Mar-08
15-Mar-08 ----- 19-Mar-08
31-Mar-08 ----- 30-Apr-08 --- (Note 2 Entries the same)
31-Mar-08 ----- 30-Apr-08 --- (Note 2 Entries the same)

In the second list you show:

Date From Date To
05-Mar-08 ----- 12-Mar-08

In this example the date 05-Mar-08 does not appear in the “Date From” of the 1st list

What rules govern the result produced in the second list in this 4th case?

 

[raghuprabhu]

Tony,

Thanks a lot for your response. I couldn't log on to the forum from work. Hence the delay.

I have attached a small txt document. Please read it and tell me if it makes any sense. Best font size is 10 on screen rsolution of 1024x768

thanks in advance

Raghu

 

[Uncle Gizmo]

Tony,

Thanks a lot for your response. I couldn't log on to the forum from work. Hence the delay.

I have attached a small txt document. Please read it and tell me if it makes any sense. Best font size is 10 on screen rsolution of 1024x768

thanks in advance

Raghu

You still haven’t got it have you, yet again you ignore some relevant questions and just provide more information.

My function here, and I believed the others, although I obviously cannot speak for them, is to help you by pointing you in the right direction. I am quite happy to do that, all you need do is answer the questions I have already provided. If you do not understand my questions, then please raise any points you may have.

I will not engage further until you have provided answers, or at least queried the questions.