Jim Lawrence
accessd at shaw.ca
Sat Sep 10 12:39:54 CDT 2005
There are times there is simply insufficient data to complete both the
parent and child. In one system I worked on, there were the workers and then
the companies that they worked for. The company was the parent record and
the workers were the children.
Before a worker could even work for any company there had to be a security
check which might take up to a month to complete. After that the worker
could be assigned to a company. This system was sort of backwards to most
so...
...The worker was initially assigned to company '0' and then re-assigned to
a real company or to company '1' if they turned out to be 'a person of
interest' or left in the pending company all depending the check results and
the requirements of the employers.
By using this method the parent and child relationships were always
maintained.
My two cents worth.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Saturday, September 10, 2005 4:44 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FW: DateDiff problem in a query
Further to my previous post, if the time span for moving total is to
include only the period upto current date (not the whole current month),
apart from the two months previous to current one, the query would stand
modified as given below.
For getting the moving average in a similar manner, Sum in the subquery
can be replaced by Avg.
A.D.Tejpal
--------------
=======================================
SELECT T_Data.*, (Select Sum(Qty) From T_Data As T1 Where 12 *
Year(T1.SDate) + Month(T1.SDate) >= 12 * Year(T_Data.SDate) +
Month(T_Data.SDate) - 2 And T1.SDate <= T_Data.SDate) AS MovingTotal
FROM T_Data
ORDER BY T_Data.SDate;
=======================================
----- Original Message -----
From: A.D.Tejpal
To: Access Developers discussion and problem solving
Sent: Saturday, September 10, 2005 11:20
Subject: Re: [AccessD] FW: DateDiff problem in a query
Chester,
It transpires that your data is as per individual dates and you are
seeking a query that will show against each date, the moving total for a
span of three months covering the current month as well as two months prior
to the current month.
Sample query given below, should get you the desired results. Qty is
the number type field being totaled while SDate is date type field in table
T_Data. This can be adapted suitably for your specific situation.
A.D.Tejpal
--------------
======================================
SELECT T_Data.*, (Select Sum(Qty) From T_Data As T1 Where 12 *
Year(T1.SDate) + Month(T1.SDate) >= 12 * Year(T_Data.SDate) +
Month(T_Data.SDate) - 2 And 12 * Year(T1.SDate) + Month(T1.SDate) <= 12 *
Year(T_Data.SDate) + Month(T_Data.SDate)) AS MovingTotal
FROM T_Data
ORDER BY T_Data.SDate;
======================================
----- Original Message -----
From: Kaup, Chester
To: Access Developers discussion and problem solving
Sent: Thursday, September 08, 2005 21:50
Subject: Re: [AccessD] FW: DateDiff problem in a query
Tried your idea in the date DateDiff part of the query instead of
DateSerial with the same erroneous results
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Elam, Debbie
Sent: Thursday, September 08, 2005 10:59 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] FW: DateDiff problem in a query
You can use months in Date functions. Try DateAdd("m", 1, Date) to get
1
month from the Date.
Debbie
_____
From: Kaup, Chester
Sent: Thursday, September 08, 2005 10:39 AM
To: 'Access Developers discussion and problem solving'
Subject: DateDiff problem in a query
I have the following query to calculate a moving total. The theory is to
calculate a total of a given date and the 2 prior dates. For example the
row of the query with a date of 5/1/2005 should have a total of vm_wtri
for March, April and May. I am using the DateSerial and DateDiff
functions to go back 2 months. As you can see in the results some months
this works and some it does not. The number of days back appears to be
correct. What am I missing? Thanks for the assistance.
SELECT [tbl One Well Water Injection].PID, [tbl One Well Water
Injection].Date, [tbl One Well Water Injection].vm_wtri, (Select
Sum(vm_wtri) From [tbl One Well Water Injection] AS T1 Where T1.Date >=
[tbl One Well Water Injection].Date
-DateDiff("d",DateSerial(Year([Date]),Month([Date])-2,Day([Date])),[Date
]) And T1.Date <= [tbl One Well Water Injection].Date) AS
MovingTotal, DateSerial(Year([Date]),Month([Date])-2,Day([Date])) AS [2
Months Prior],
DateDiff("d",DateSerial(Year([Date]),Month([Date])-2,Day([Date])),[Date]
) AS [Days Diff]
FROM [tbl One Well Water Injection]
WHERE ((([tbl One Well Water Injection].Date)>=#1/1/1994#))
ORDER BY [tbl One Well Water Injection].Date;
The results:
PID Date vm_wtri
MovingTotal 2 Months Prior Days Diff
424150174300 9/1/2004
7/1/2004 62
424150174300 10/1/2004
8/1/2004 61
424150174300 11/1/2004 23546.37 23546.37
9/1/2004 61
424150174300 12/1/2004 61430.49 84976.86
10/1/2004 61
424150174300 1/1/2005 43755.91 128732.77
11/1/2004 61
424150174300 2/1/2005 45906.27 89662.18
12/1/2004 62
424150174300 3/1/2005 34813.93 124476.11
1/1/2005 59
424150174300 4/1/2005 44469.81 125190.01
2/1/2005 59
424150174300 5/1/2005 3881.54 48351.35
3/1/2005 61
424150174300 6/1/2005 3881.54
4/1/2005 61
424150174300 7/1/2005 3881.54
5/1/2005 61
424150174300 8/1/2005
6/1/2005 61
Chester Kaup
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com