Query Help for Inventory Tracking

[Henry Stockbridge]

Hi,

I am working on a query that displays depleted inventory. I need the
criteria to be:

CURRENT WEEK (Format(Now(),"ww") QTY = 0
and
PREVIOUS WEEK (Format(Now(),"ww")-1) QTY <> 0.

Ultimately, the query results would be similar to this:

PartNo Description Week 41 Week 42
123 Widget 1 234 0
236 Widget 7 29 0
420 Widget 53 89 0

Any help you can lend would be appreciated.

Henry

[Brian Wilson]

"Henry Stockbridge" <hs***********@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi,

I am working on a query that displays depleted inventory. I need the
criteria to be:

CURRENT WEEK (Format(Now(),"ww") QTY = 0
and
PREVIOUS WEEK (Format(Now(),"ww")-1) QTY <> 0.

Ultimately, the query results would be similar to this:

PartNo Description Week 41 Week 42
123 Widget 1 234 0
236 Widget 7 29 0
420 Widget 53 89 0

Any help you can lend would be appreciated.

Henry

You have not given any details of your table structure so it's hard for
anyone to help. Are there, say, two tables tblProduct and tblTransaction
from which the details can be worked out? What are the fields?

[Henry Stockbridge]

Table structure is Part Number (Text), Description (Text), Date (Short
Date), Week (Long Integer), Month (Long Integer), Qty (Long Integer.)

Each part has 52 records to accommodate the weeks of the year. That
way I can accommodate cross-tabbing the data. Perhaps not a good
structure?

[Brian Wilson]

"Henry Stockbridge" <hs***********@hotmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Table structure is Part Number (Text), Description (Text), Date (Short
Date), Week (Long Integer), Month (Long Integer), Qty (Long Integer.)

Each part has 52 records to accommodate the weeks of the year. That
way I can accommodate cross-tabbing the data. Perhaps not a good
structure?

Sorry for the delay - suddenly called away.
Anyway, it is a pretty bad structure. That is, you would not generally find
this in a professionally designed database - since the table is limited to
52 weeks and getting the data out can be difficult if you want to use a
standard query.
Normally, you might have a long skinny table PartNo, CheckDate, CheckQty
which is related to tblParts. Or perhaps you would have a table of
transactions, where the quantity in stock is calculated by the sum of the
transactions for each part.
Anyway, if you decide to keep your current table structure, then the only
way I can see for this query to work is to write a function in vba which
looks at today's date, works out the week number and then decides which
columns to select. This approach is OK but it means you can no longer
simply double-click a query. You could write the vba code in a report, so
you double-click the report which is similar.

[Brian Wilson]

"Henry Stockbridge" <hs***********@hotmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Table structure is Part Number (Text), Description (Text), Date (Short
Date), Week (Long Integer), Month (Long Integer), Qty (Long Integer.)

Each part has 52 records to accommodate the weeks of the year. That
way I can accommodate cross-tabbing the data. Perhaps not a good
structure?

Sorry for the delay - suddenly called away.
Anyway, it is a pretty bad structure. That is, you would not generally find
this in a professionally designed database - since the table is limited to
52 weeks and getting the data out can be difficult if you want to use a
standard query.
Normally, you might have a long skinny table PartNo, CheckDate, CheckQty
which is related to tblParts. Or perhaps you would have a table of
transactions, where the quantity in stock is calculated by the sum of the
transactions for each part.
Anyway, if you decide to keep your current table structure, then the only
way I can see for this query to work is to write a function in vba which
looks at today's date, works out the week number and then decides which
columns to select. This approach is OK but it means you can no longer
simply double-click a query. You could write the vba code in a report, so
you double-click the report which is similar.