Conceptual problem when

[kuhni]

Hi everybody!

Writing this time, I'm really desperate. Basically, I have a
conceptual problem of how to solve a certain "problem" in MS Access
97.

General objective:
In order to categorise certain products, I need to know whether a
specific product has been promoted in a specific week. To find this
out, I have a linked table, which has all promotions for each period
(1-2 months) and an EAN code for each promoted product in it. The
target table (where I want to add the category if it is in the linked
table) has EAN codes as well as a week and year. However, in the
target table, I have to use one column called week and another called
year (no changes are allowed). Therefore, I need to find out whether a
product in the target table is within the time period of the linked
table.

Example:
Linked Table
StartDate | EndDate | EAN
1.12.04 1.2.05 abc
1.4.05 1.6.05 abc
1.4.05 1.6.05 xyz

Target Table
EAN | Week | Year
abc 1 05
xyz 1 05

Objective would be:
EAN | Week | Year | Category
abc 1 05 Promo
xyz 1 05 'not xyz coz not promoted in that period

Explanation:
In the target table, there is the EAN code "abc" in week 1, year 05. I
now want to check, whether this product was pormoted during this week.
When looking at the Linked Table, you can see that this product was
promoted from the end of the year 2004 until February 2005. This means
that it was promoted in the first week of 2005 and therefore, in the
column category, it should say e.g. "Promo".

My Approaches/Ideas:
I've already created a table which translates each date into a
corresponding week number and year. But how can I connect those tables
to solve this problem? I've already tried using a macro, but I failed
because you cannot use the SEEK command when working with linked
tables... is there any SQL statement? E.g. a JOIN?

I would be really grateful for any thoughts or ideas on that.

THANKS TO ALL FOR READING MY "NOVEL"!

Cheers,
Stephan Kuhnert

[Ed Robichaud]

Look at Access Help for explanation of the DatePart, Year, DateSerial, and
DateDiff functions. You'll be able to use them to convert values to a
common unit and cmpare them.
-Ed
"kuhni" <st************@gmx.de> wrote in message
news:88**************************@posting.google.c om...

Hi everybody!

Writing this time, I'm really desperate. Basically, I have a
conceptual problem of how to solve a certain "problem" in MS Access
97.

General objective:
In order to categorise certain products, I need to know whether a
specific product has been promoted in a specific week. To find this
out, I have a linked table, which has all promotions for each period
(1-2 months) and an EAN code for each promoted product in it. The
target table (where I want to add the category if it is in the linked
table) has EAN codes as well as a week and year. However, in the
target table, I have to use one column called week and another called
year (no changes are allowed). Therefore, I need to find out whether a
product in the target table is within the time period of the linked
table.

Example:
Linked Table
StartDate | EndDate | EAN
1.12.04 1.2.05 abc
1.4.05 1.6.05 abc
1.4.05 1.6.05 xyz

Target Table
EAN | Week | Year
abc 1 05
xyz 1 05

Objective would be:
EAN | Week | Year | Category
abc 1 05 Promo
xyz 1 05 'not xyz coz not promoted in that period

Explanation:
In the target table, there is the EAN code "abc" in week 1, year 05. I
now want to check, whether this product was pormoted during this week.
When looking at the Linked Table, you can see that this product was
promoted from the end of the year 2004 until February 2005. This means
that it was promoted in the first week of 2005 and therefore, in the
column category, it should say e.g. "Promo".

My Approaches/Ideas:
I've already created a table which translates each date into a
corresponding week number and year. But how can I connect those tables
to solve this problem? I've already tried using a macro, but I failed
because you cannot use the SEEK command when working with linked
tables... is there any SQL statement? E.g. a JOIN?

I would be really grateful for any thoughts or ideas on that.

THANKS TO ALL FOR READING MY "NOVEL"!

Cheers,
Stephan Kuhnert