Consolidated Records date query

[ajay_afmet]

Hi,

I m new to MS ACCESS. I have a database as follow :

Location DOE DOC No. Of Days
Delhi 12 Mar 12 28 Mar 12
Delhi 12 Mar 12 20 Mar 12
Delhi 01 Mar 12 10 Mar 12
Mumbai 29 Feb 12 12 Mar 12

I want a query to calculate days as per location. like
Delhi 16+10=26
Mumbai 12
This query should not generate separate no. of days for individual location.
I am able to calculate individual records days. But i want consolidated No. of days as per location. Please help me in this regard.

 

[plog]

First, when posting sample data seperate it by commas or semi-colons or some character that isn't in the data or field names themselves--I have no idea where each field name ends and begins and the same thing goes for the actual data.

Second, "Delhi 16+10=26" doesn't help me because I don't see the number 16 in your data at all.

Could you repost your data, seperating each field by commas and further explain where you are getting 16.

 

[ajay_afmet]

Hi,

I m new to MS ACCESS. I have a database as follow :

Location ; DOE ; DOC ; No. Of Days
Delhi ; 12 Mar 12 ; 28 Mar 12 ; 16
Delhi ; 12 Mar 12 ; 20 Mar 12 ; 08
Delhi ; 01 Mar 12 ; 10 Mar 12 ; 10
Mumbai ; 29 Feb 12 ; 12 Mar 12 ; 12

I want a query to calculate days as per location. like
Location ; Net Days
----------------------------------------------------------
Delhi ; 16+10=26
Mumbai ; 12
Here No. of days is calculated based on DOC-DOE Fields. hence i want location wise net number of days by adding the no. of days together for individual location leaving those records whose range of date coincide together. E.g. Delhi 12 Mar 12 to 20 Mar 12 lies in the range of Delhi 12 Mar 12 to 28 Mar 12. So only 12 Mar 12 to 28 Mar 12 should be used as 20 Mar 12 is less. whereas 01 Mar 12 to 10 Mar 12 does not lie in the range. So it is considered as individual record. I hope now u understand.
This query should not generate separate no. of days for individual location.
I am able to calculate individual records days. But i want consolidated No. of days as per location. Please help me in this regard.

 

[recyan]

I have a database as follow :

Location ; DOE ; DOC ; No. Of Days
Delhi ; 12 Mar 12 ; 28 Mar 12 ; 16
Delhi ; 12 Mar 12 ; 20 Mar 12 ; 08
Delhi ; 01 Mar 12 ; 10 Mar 12 ; 10
Mumbai ; 29 Feb 12 ; 12 Mar 12 ; 12

Not the answer, but a few questions :

1) Is there a possibility of overlapping range ? For eg.
Delhi ; 05 Mar 12 ; 15 Mar 12 ; 11

2) Is the No off Days stored in the table?
3) As an example, Why is the
No off Days for
Delhi ; 01 Mar 12 ; 10 Mar 12 ; 10
10
AND
No off Days for
Mumbai ; 29 Feb 12 ; 12 Mar 12 ; 12
12. Should this not be 13 ?

Thanks

 

[ajay_afmet]

1) Is there a possibility of overlapping range ? For eg.
Delhi ; 05 Mar 12 ; 15 Mar 12 ; 11
ans: offcourse there may be overlapping of days, but the overlapping days should not be used only the entire range in which the overlapping occurs should be extracted.
2) Is the No off Days stored in the table?
ans: Currently I am using the query itself using datediff() function to calculate in a new column and am not storing it to any table. The date is extracted from the table to query and in the query itself i m calculating the no. of days.
3) As an example, Why is the
No off Days for
Delhi ; 01 Mar 12 ; 10 Mar 12 ; 10
10
AND
No off Days for
Mumbai ; 29 Feb 12 ; 12 Mar 12 ; 12
12. Should this not be 13 ?
ans : okay it can be 13, that doesn't make a difference. Its just a sample database. I just want to know how to get the consolidated result for a single location in one record row only like if there are 3 entries for delhi and out of that 2 are overlapping then the overlapping should be ignored and the whole number of days should be counted and should be added to the number of days of non overlapping record entry and this no. of day should be there in the query for single record.
e.g .
Location ; DOE ; DOC
Delhi ; 05 Mar 12 ; 28 Mar 12
Delhi ; 10 mar 12 ; 15 Mar 12
Delhi ; 01 Mar 12 ; 03 Mar 12
In this case the final query should result
Delhi ; 25 (No. Of days)
05 Mar 12 to 28 mar 12 is 24 days
10 Mar 12 to 15 Mar 12 is 06 days but it is overlapping with the previous range. so it should be ignored.
01 Mar to 03 Mar 12 is 03 Days.
So 24 and 03 should be summed up.
and in a new query it should be like
Delhi ; 27(because 24+03)
I hope now you understand my case completely.

 

[recyan]

3)
ans : okay it can be 13, that doesn't make a difference.

Amazing.

Just see if below gives some guidelines, till someone comes along :

1) Use the Minimum of DOE & Maximum of DOC in VBA to make a table, say tblReportDates with a single field TheDate. This table on generation will contain all the distinct Dates from the Minimum to Maximum possible Range in a single field.

2) Then make a query say query1 with this table with its field TheDate to join with your main table on both the DOE & the DOC fields (Note : This will not be a normal join. Will involve >= & <=).

3) Group the results of this query1 on Location and TheDate in another query say query2.

4) Now Group the results of query2 on Location & get Count on TheDate.

Thanks

 

[plog]

First, I would be remiss if I didn't tell you that No. Of Days shouldn't be a field in your table because its a calculated value. You shouldn't store values you can calculate with other data you already store.

Second, I was able to get what you want in 3 queries. Be sure to name the queries as I have and to replace instance of 'YourTableNameHere' with the actual name of your Table/Query that contains the source data.

The first query is called 'LocationDays_sub1'

SELECT YourTableNameHere.Location, YourTableNameHere.DOE, YourTableNameHere.DOC, Min(IIf([YourTableNameHere].[DOE]>=[YourTableNameHere_1].[DOE] And [YourTableNameHere].[DOE]<=[YourTableNameHere_1].[DOC],[YourTableNameHere_1].[DOE],[YourTableNameHere].[DOE])) AS newDOE, Max(IIf([YourTableNameHere].[DOC]>=[YourTableNameHere_1].[DOE] And [YourTableNameHere].[DOC]<=[YourTableNameHere_1].[DOC],[YourTableNameHere_1].[DOC],[YourTableNameHere].[DOC])) AS newDOC
FROM YourTableNameHere INNER JOIN YourTableNameHere AS YourTableNameHere_1 ON YourTableNameHere.Location = YourTableNameHere_1.Location
GROUP BY YourTableNameHere.Location, YourTableNameHere.DOE, YourTableNameHere.DOC;

The second is called 'LocationDays_sub2'

SELECT LocationsDays_sub1.Location, LocationsDays_sub1.newDOE, LocationsDays_sub1.newDOC, DateDiff("d",[newDOE],[newDOC])+1 AS DaysBetween
FROM LocationsDays_sub1
GROUP BY LocationsDays_sub1.Location, LocationsDays_sub1.newDOE, LocationsDays_sub1.newDOC, DateDiff("d",[newDOE],[newDOC])+1;

The last query and the one you need to run to get your totals is 'LocationDays'

SELECT LocationDays_sub2.Location, Sum(LocationDays_sub2.DaysBetween) AS TotalDays
FROM LocationDays_sub2
GROUP BY LocationDays_sub2.Location;

Let me know if this does exactly what you want.

 

[ajay_afmet]

thanks alot for the responses, but for m very new to vba. so please help me a bit where to write these codes in MS Access?

 

[plog]

No VBA needed. For each of my codes, open a new query, go into the SQL view, paste the code in, replace 'YourTableNameHere' with the name of your table and then save it as I have instructed.