Received: by 10.224.78.209 with SMTP id m17mr1989940qak.3.1349214616288;
        Tue, 02 Oct 2012 14:50:16 -0700 (PDT)
Received: by 10.52.93.229 with SMTP id cx5mr14886vdb.19.1349214616248; Tue, 02
 Oct 2012 14:50:16 -0700 (PDT)
Path: e10ni165558057qan.0!nntp.google.com!l8no22251428qao.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-access
Date: Tue, 2 Oct 2012 14:50:16 -0700 (PDT)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=92.239.111.217;
 posting-account=recKPgoAAADkqmySjulKdebZ0r0xoSs5
NNTP-Posting-Host: 92.239.111.217
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <867a7fff-dec1-4a4a-a4bf-5aa329229803@googlegroups.com>
Subject: Calculate / Infer / Deduce value from different fields in different tables
From: Vincent001 <off...@vincentoakley.com>
Injection-Date: Tue, 02 Oct 2012 21:50:16 +0000
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I have two tables.  Table 1, Items (of work) which has a field (ItemDate) f=
or the date the work was done.  Table 2, Periods (of work) specifies chrono=
logical periods (each being PeriodID) between StartDate and EndDate when wo=
rk might be done. To eliminate duplication the Items Table does not have a =
field PeriodID

There is an hourly charging rate for work undertaken in each Period but wor=
k is recorded in the Items table which includes the ItemDate of each Item o=
f work.  The Items table does not include a field PeriodID because the star=
t and end dates of each Period are often changed.  For example the company =
may be charging =A31 per hour for work between 01/01/10 and 31/03/10.  Afte=
r the data has been input the company may then alter the hourly rate to cha=
rge =A30.5 from 01/02/10 so that we would amend the records to have two Per=
iodIDs 01/01/10 - 31/01/10 and 01/02/10 - 31/03/10.  This happens a lot as =
hourly rates and periods are adjusted frequently after the Items of work ha=
ve been entered.


To make the whole thing work I have a 'Source' query which selects the Item=
Date from Items and the relevant PeriodID from Periods.  In short ItemDate =
between StartDate and EndDate from Periods.  This seems to result in very s=
low query operation but I then have effectively a table which contains both=
 the ItemDate and PeriodID.  The 'Source' is then referenced by other queri=
es to create multiple reports.

Currently we have about 50,000 Items for 6 customers but we are about to do=
 a joint venture with another company which has about 50 customers.  It hop=
es to merge with another organisation which has about 200 customers.  The m=
ain attraction for the joint venture company is that our database appears b=
etter than its present one which is very old.  It wants to use our database=
 for work done for its existing 50 and 200 prospective customers. For this =
reason I am reviewing the design and wonder whether it would be faster / mo=
re efficient to have PeriodID in the Items table and then run an update que=
ry so that the Items table would contain not only the date but also the Per=
iodID.  To date I have always thought that having both the ItemDate and Per=
iodID in the Items table wastes resources.  The problem is that it is slow.=
=20

Any comments or suggestions would be very welcome