Confused on Design or Application

[JavyD]

Hello everyone, I'am so stumped.

I'm pretty good with Excel, so I know I can do this in excel if I really try
with a few formulas. But it would be something that I would have to do every
week. So this is my problem.

I have a spreadsheet with item numbers, and the parts that are used in the
items like so
item plastic balls staples
12345 2 4
67890 0 5
And theres many more parts,

and then I have a monthly projected demand by item spreadsheet, with the
item number and the monthly demand like so
item jan feb
12345 20 22
67890 2 15

So now, how do I get a query done that will bring back the parts in a row,
then months as the column headings with the quantities that I will need for
each month once the query figures out that a part belongs to an item then it
multiples the quantity that item needs of a part times the fcst for that
month. I hope someone can help.

Thanks everyone.

 

[Duane Hookom]

You need to pull your head out of Excel. I would expect tables like:
tblItems
===============
Item
ItemDescription

tblParts
===========
Part
PartDescription

tblItemParts
=================
Item
Part
Quantity

tblItemDemand
=================
Item
DemandDate
DemandQuantity

You could then use a crosstab query based on the above tables to get your
desired result.

 

[JavyD]

If that works, then youre amazing, let me get working and this and I shall
let you know.