number value needs valid date range

[norma.j.hildebrand]

I have a database that has a field [STD] (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards based on
the prior years performance.
Now they threw me a curve ball... They want to change the standards
every quarter. This standard [STD] is matched up with an item that is
referenced in a daily production table to figure out our percentage of
productivity.
Based on the date of the production entry, we want to pull over the
standard for that time frame. To complicate things, we run reports for
the entire year, which would involve 4 different standards one from
each quarter.

Does anyone have any suggestion how this can be done??

Any assistance or direction is greatly appreciated.

Norma

[pietlinden]

On Feb 2, 12:25 pm, [e-mail] wrote:

I have a database that has a field [STD] (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards based on
the prior years performance.
Now they threw me a curve ball... They want to change the standards
every quarter. This standard [STD] is matched up with an item that is
referenced in a daily production table to figure out our percentage of
productivity.
Based on the date of the production entry, we want to pull over the
standard for that time frame. To complicate things, we run reports for
the entire year, which would involve 4 different standards one from
each quarter.

Does anyone have any suggestion how this can be done??

Any assistance or direction is greatly appreciated.

Norma

Create a table of [Standard], [BeginDate], [EndDate] or
[Standard], [Quarter], [Year]
and then join on those. Then you can grab the quarters/years that
apply to your date ranges by doing a simple join.

[norma.j.hildebrand]

On Feb 2, 1:33 pm, "[e-mail]" <[e-mail]>
wrote:

On Feb 2, 12:25 pm, [e-mail] wrote:

I have a database that has a field [STD] (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards based on
the prior years performance.
Now they threw me a curve ball... They want to change the standards
every quarter. This standard [STD] is matched up with an item that is
referenced in a daily production table to figure out our percentage of
productivity.
Based on the date of the production entry, we want to pull over the
standard for that time frame. To complicate things, we run reports for
the entire year, which would involve 4 different standards one from
each quarter.

Does anyone have any suggestion how this can be done??

Any assistance or direction is greatly appreciated.

Norma

Create a table of [Standard], [BeginDate], [EndDate] or
[Standard], [Quarter], [Year]
and then join on those. Then you can grab the quarters/years that
apply to your date ranges by doing a simple join.- Hide quoted text -

- Show quoted text -

Thanks for your response, but that seems too simple... (or I am
thinking too deep)
If each record has the fields containing: Item, Std, ItemType,
STDstartdate, STDenddate. Would I need a new record for each item for
each qrt?

[norma.j.hildebrand]

On Feb 2, 1:33 pm, "[e-mail]" <[e-mail]>
wrote:

On Feb 2, 12:25 pm, [e-mail] wrote:

I have a database that has a field [STD] (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards based on
the prior years performance.
Now they threw me a curve ball... They want to change the standards
every quarter. This standard [STD] is matched up with an item that is
referenced in a daily production table to figure out our percentage of
productivity.
Based on the date of the production entry, we want to pull over the
standard for that time frame. To complicate things, we run reports for
the entire year, which would involve 4 different standards one from
each quarter.

Does anyone have any suggestion how this can be done??

Any assistance or direction is greatly appreciated.

Norma

Create a table of [Standard], [BeginDate], [EndDate] or
[Standard], [Quarter], [Year]
and then join on those. Then you can grab the quarters/years that
apply to your date ranges by doing a simple join.- Hide quoted text -

- Show quoted text -

That sounds really simple. So, for each item, I need four different
date ranges. Would I need to have 4 records for each item to allow all
4 quarters of the year? I just am not grasping this.
My table structure currently looks something like this.

Item
ItemType
ItemBrand
PeopleNeededToProduce
Standard
etc
etc

Production table like this:

Date
ItemRan
ManhoursToProduce
TotalCompleted
etc
etc

When I join these tables currently it is by item. Now with the new
date for the standard, would I need 4 records for each item, each
record having the same item # but different date ranges that the
standard is valid for?
How would I join these using both item and date range?

[Gord]

If you currently have
Item
ItemType
ItemBrand
PeopleNeededToProduce
Standard
etc
and you want multiple [Standard] values based on date then you'll have
to split [Standard] off into a separate table, e.g.
Item
ItemType
ItemBrand
PeopleNeededToProduce
etc
Item
StartDate
EndDate
Standard
and yes, you'll need four rows for each [Item] if the [Standard] is to
change quarterly.

>How would I join these using both item and date range?

Something like this (untested):

SELECT <fieldsFROM ((tblProd
INNER JOIN tblItem ON tblItem.Item=tblProd.Item)
INNER JOIN tblStd ON tblStd.Item=tblItem.Item
AND tblStd.StartDate<=tblProd.[Date]
AND tblStd.EndDate>=tblProd.[Date])
WHERE <whatever>

On Feb 2, 2:20 pm, [e-mail] wrote:

On Feb 2, 1:33 pm, "[e-mail]" <[e-mail]>
wrote:

On Feb 2, 12:25 pm, [e-mail] wrote:

I have a database that has a field [STD] (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards based on
the prior years performance.
Now they threw me a curve ball... They want to change the standards
every quarter. This standard [STD] is matched up with an item that is
referenced in a daily production table to figure out our percentage of
productivity.
Based on the date of the production entry, we want to pull over the
standard for that time frame. To complicate things, we run reports for
the entire year, which would involve 4 different standards one from
each quarter.

Does anyone have any suggestion how this can be done??

Any assistance or direction is greatly appreciated.

Norma

Create a table of [Standard], [BeginDate], [EndDate] or
[Standard], [Quarter], [Year]
and then join on those. Then you can grab the quarters/years that
apply to your date ranges by doing a simple join.- Hide quoted text -

- Show quoted text -

That sounds really simple. So, for each item, I need four different
date ranges. Would I need to have 4 records for each item to allow all
4 quarters of the year? I just am not grasping this.
My table structure currently looks something like this.

Item
ItemType
ItemBrand
PeopleNeededToProduce
Standard
etc
etc

Production table like this:

Date
ItemRan
ManhoursToProduce
TotalCompleted
etc
etc

When I join these tables currently it is by item. Now with the new
date for the standard, would I need 4 records for each item, each
record having the same item # but different date ranges that the
standard is valid for?
How would I join these using both item and date range?