finding week when whole quantity is covered

[umair434]

Hi guys,

I have been given this table which contains a list of products, the quantity of products - and the other fields are weeks from 1 - 26 - each week field representing how much orders are required.

I have to find out by when each product's quantity will be finished! for example

if quantity is 350 and

week 1 - 20
week2 - 100
week 3 - 30
week 4 - 200

then by week 4, the product will be out of the inventory!

I just need the product skus in order of weeks! how should I go about this.

p.s I have just been assigned this table - nothing else! and it's due tomorrow :/

Thanks!!

 

[jdraw]

Do you want to show us your table layout? And anything you have tried so far?

 

[umair434]

Hi jdraw!

I'm attaching pictures of my the table and the query which I have so far.

thanks!!!

 

[vbaInet]

I'm not sure what you're trying to do. Is this the main question? -->

I just need the product skus in order of weeks! how should I go about this.

 

[umair434]

Hi vbaInet!

This is what I want to see:

QTY_ON_HAND is the inventory we have right now
w1, w2, w3 etc = number of products sold in each week (data is upto 26 weeks)

I want the product skus in order of when the inventory is finished!

for example, for the first product, the QTY_ON_HAND will be over by 5 weeks! I want this information for all the skus..

thanks!

 

[vbaInet]

You are using terms that I don't understand. What is SKU?

What I would like to see is in a spreadsheet is:

1. Your current records
2. What you would like it to look like.

 

[umair434]

A Sku refers to 1 product here - like an identifier for each product

anyways, I'm attaching a database here which contains just 1 table!

The records look like this right now (as shown in the table).

what I want is to list the products in order of when the QTY_ON_HAND is depleted.

so for example:

for product number 0090069, the QTY_ON_HAND = 442

SumofUNITS1 = 125
SumofUNITS2 = 75.3
SumofUNITS3 = 101.1
SumofUNITS4 = 134.5
SumofUNITS5 = 4400.5


total after 4 weeks = SumofUNITS1+SumofUNITS2+SumofUNITS3+SumofUNITS4 = 436.1, which is still < QTY_ON_HAND (442)

so,
total after 5 weeks = 436.1+4400.5, which is > QTY_ON_HAND (442) -

hence this product's inventory will be over by 5 weeks!

I want this information for every product.


hope i'm making some sense

so QTY_ON_HAND becomes < = after SumofUNITS5

 

[vbaInet]

I'm pretty sure I explained to you that you need to invest your time in getting your database normalized.

There are now two things wrong with your db, unnormalized tables and storing calculated values.

Access works from Top to Bottom, not Left to Right like you have in Excel.

 

[umair434]

I'm pretty sure I explained to you that you need to invest your time in getting your database normalized.

There are now two things wrong with your db, unnormalized tables and storing calculated values.

Access works from Top to Bottom, not Left to Right like you have in Excel.

I know! and I am working on my database on the side as you suggested. As I mentioned in the first post, this is not my database. They just handed me a table and asked me to do some "analysis".

Unfortunately, every access application in here is not normalized, so I guess I'm up for a ride :/

 

[vbaInet]

Everytime you want to perform some sort of analysis on the table, you MUST always use code. This shouldn't be so.

It's a shame that all the databases are in this form. I guess your best bet is to look for an Excel solution in the meantime. You can post your question in the Excel section of the forum.

 

[umair434]

You can post your question in the Excel section of the forum.

Thanks! that's a good idea actually!