Calculate / Infer / Deduce value from different fields in different tables

[Vincent001]

I have two tables. Table 1, Items (of work) which has a field (ItemDate) for the date the work was done. Table 2, Periods (of work) specifies chronological periods (each being PeriodID) between StartDate and EndDate when work 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 work is recorded in the Items table which includes the ItemDate of each Item of work. The Items table does not include a field PeriodID because the start and end dates of each Period are often changed. For example the company may be charging £1 per hour for work between 01/01/10 and 31/03/10. After the data has been input the company may then alter the hourly rate to charge £0.5 from 01/02/10 so that we would amend the records to have two PeriodIDs 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 have been entered.


To make the whole thing work I have a 'Source' query which selects the ItemDate from Items and the relevant PeriodID from Periods. In short ItemDate between StartDate and EndDate from Periods. This seems to result in very slow query operation but I then have effectively a table which contains both the ItemDate and PeriodID. The 'Source' is then referenced by other queries 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 hopes to merge with another organisation which has about 200 customers. The main attraction for the joint venture company is that our database appears better 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 / more efficient to have PeriodID in the Items table and then run an update query so that the Items table would contain not only the date but also the PeriodID. To date I have always thought that having both the ItemDate and PeriodID in the Items table wastes resources. The problem is that it is slow.

Any comments or suggestions would be very welcome

[Patrick Finucane]

Although you have detailed your problem with lots of info your problem is still hard for me to understand what the problem is. Are you saying you have Table1 and Table2 that have no relation to each other and you need to merge data between the two tables?

[Phil]

On 08/10/2012 00:06:46, Patrick Finucane wrote:
> On Tuesday, October 2, 2012 4:50:19 PM UTC-5, Vincent001 wrote:
>> I have two tables. Table 1, Items (of work) which has a field (ItemDate)
> for the date the work was done. Table 2, Periods (of work) specifies chro
> nological periods (each being PeriodID) between StartDate and EndDate when
> work might be done. To eliminate duplication the Items Table does not have
> > a field PeriodID
>>

> > a field PeriodID
>>
> > a field PeriodID

>>
> > a field PeriodID
>> There is an hourly charging rate for work undertaken in each Period but w

> a field PeriodID ork is recorded in the Items table which includes the

> ItemDate of each Item of work. The Items table does not include a field

> PeriodID because the st art and end dates of each Period are often
> changed. For example the compan y may be charging £1 per hour for work
> between 01/01/10 and 31/03/10. Af ter the data has been input the company
> may then alter the hourly rate to c harge £0.5 from 01/02/10 so that we
> would amend the records to have two P eriodIDs 01/01/10 - 31/01/10 and
> 01/02/10 - 31/03/10. This happens a lot a s hourly rates and periods are

> adjusted frequently after the Items of work have been entered.
>>
>>
>>
>>
>>
>> To make the whole thing work I have a 'Source' query which selects the It
> emDate from Items and the relevant PeriodID from Periods. In short ItemDat
> e between StartDate and EndDate from Periods. This seems to result in very
> slow query operation but I then have effectively a table which contains bo
> th the ItemDate and PeriodID. The 'Source' is then referenced by other que
> > ries to create multiple reports.
>>

> > ries to create multiple reports.
>>
> > ries to create multiple reports.

>>
> > ries to create multiple reports.
>> Currently we have about 50,000 Items for 6 customers but we are about to

> ries to create multiple reports. do a joint venture with another company
> which has about 50 customers. It h opes to merge with another organisation

> which has about 200 customers. The main attraction for the joint venture
> company is that our database appears better than its present one which is

> very old. It wants to use our databa se for work done for its existing 50
> and 200 prospective customers. For thi s reason I am reviewing the design

> and wonder whether it would be faster / more efficient to have PeriodID in

> the Items table and then run an update q uery so that the Items table
> would contain not only the date but also the P eriodID. To date I have
> always thought that having both the ItemDate and P eriodID in the Items

> table wastes resources. The problem is that it is slo w.
>>
>>
>>
>> Any comments or suggestions would be very welcome
>
> Although you have detailed your problem with lots of info your problem is

> s till hard for me to understand what the problem is. Are you saying you
> hav e Table1 and Table2 that have no relation to each other and you need
> to mer ge data between the two tables?
>

Might I sugest you forget what you have and tell us what data you want to
input and what you want out. I have a feeling that your structure is wrong -
so back to first principals.

I have the feeling that this is a job costing requirement, when the price
varies depending on the day (time) the job was done

Phil

Phil