Help Modifying Query Please

[pldd4]

Hi everyone,

Very new here!

I'm creating a database to keep track of orders and stock nothing too complicated. Its virtually finished, just one little bit left which I'm not sure how to do. Hopefully someone can help me!

Database description.
I have a Customer Table (Showing customer details address etc)
A Product Table (Product ID etc also OS(Opening Stock for each product))
A Production Table (Product ID, Producewk1, producewk2, producewk3, producewk4)
A Order Table (Customer Name, Product ID, Orderwk1, orderwk2, order wk3, orderwk4)

At the moment I have a query which works out the total order of each product (from any customer) for each week separately (1-4). It also works out the stock left for each week by subtracting the order from the opening stock in the Product Table.
Here's the sql for that query

---------------------
SELECT [product id] AS Product_Code, SUM([wk 1 order]) AS Orders_For_WK1, (SELECT [OS] FROM [Product Table] WHERE [Product id]=[order table].[Product id])-Orders_For_Wk1 AS Stock_Left_WK1, SUM([wk 2 order]) AS Orders_For_WK2, (SELECT [OS] FROM [Product Table] WHERE [Product id]=[order table].[Product id])-Orders_For_Wk2 AS Stock_Left_WK2, SUM([wk 3 order]) AS Orders_For_WK3, (SELECT [OS] FROM [Product Table] WHERE [Product id]=[order table].[Product id])-Orders_For_Wk3 AS Stock_Left_WK3, SUM([wk 4 order]) AS Orders_For_WK4, (SELECT [OS] FROM [Product Table] WHERE [Product id]=[order table].[Product id])-Orders_For_Wk4 AS Stock_Left_WK4
FROM [order table]
GROUP BY [product id];
-------------

But this is what I want to change/improve the stock part of it with help!
Here is what I'd like to try and do but don't know how!!?

Stock_Left_WK1 = (ProductTable.OS - orders_for_wk1)
Stock_Left_WK2 = ( (Stock_Left_WK1 - orders_for_wk2) + (Production.ProduceWk1) )
Stock_Left_WK3 = ( (Stock_Left_WK2 - orders_for_wk3) + (Production.ProduceWk2) )
Stock_Left_WK4 = ( (Stock_Left_WK3 - orders_for_wk4) + (Production.ProduceWk3) )

Please can someone help me? Really stuck on this one!

If you need me to try and explain some more don't hesitate to ask.

Thanks for any help whatsoever.

Paul

 

[pldd4]

Can no one help me on this??



Anything at all will help i''m sure

Thanks

 

[Mile-O]

On initial looking at the problem I'd suggest that you are having problems because your tables are not properly designed.

You have repeating groups i.e. Producewk1, Producewk2,...Producewkn and Orderwk1, Orderwk2,...Orderwkn.

Such repeating groups are a 'no-no' in accepted database design - it shows that you have treated your database tables, and the whole relational concept, as an Excel spreadsheet.

To get accurate and workable solutions you would be better off normalising your table structure.

There have been numerous posts on normalisation on this forum - have a search for them.

Other related search topics may be Normal Form.

 

[neileg]

I'm creating a database to keep track of orders and stock nothing too complicated.

Unfortunately, stock control is complicated. The little bit you have left to do is the bit you should have started with. I agree with Mile's observation that your design is wrong.

You also have a fundamental conceptual problem. In your structure, you are treating customer orders as if they are immediately fulfilled. When an order is received that exceeds stock, you will end up with negative stock. This is clearly a nonsense.

There are other transactions that your design does not accomodate, customer returns, stock adjustments, and so on.

Search these forums. You will find that stock control (or inventory as our US cousins call it) is a perennial subject.

 

[pldd4]

I completly agree with you.

But this is how the company wants it doing. (Friend of family). Theyve been using a similar paper method, as you said the stock goes into a minus figure if an order comes in (this is what they want!!)

However you mentioned i was duplicating fields in tables, the orderwk (1-4) and Producewk(1-4) are completly different. An order is an actual order as you would imagine! But produce is how much the company decides to produce that particualar week before any order or anything just from their knowledge & expenceicne.

This is just how they want it, i know its strange but can you think of a way to do they query i described?

Thanks again

 

[Mile-O]

However you mentioned i was duplicating fields in tables, the orderwk (1-4) and Producewk(1-4) are completly different

I never said you were duplicating; I said 'repeating'.

I know Orderwk(1-4) and Producewk(1-4) are different but

Orderwk1, Orderwk2, Orderwk3, and Orderwk4 are the culprits

You'd want to change the structure to:

Order Table
Customer Name
Product ID
Order
Wk

Same goes for the product table.

 

[pldd4]

Ok i'll change those.

I take it you meant the Production Table not the Product Table?

So if i do what you say can we try and sort that query out? Will it then be possible to do it?

 

[Mile-O]

Post an example of your database (Access '97 format) taking out any unnecessary stuff and confidential data and I'll have a look at it.

 

[pldd4]

Thanks mate.

Really appreciate it. Hopefully it should be attached. Ive renamed it .bmp
Its a zip file so rename it to .zip

Cheers

 

[pldd4]

Sorry try this one. This has the look ups on it!

again a rename to a .zip file

 

[Mile-O]

1) Is the customer another company or an individual?

2) Why are orders grouped into weeks when dates would provide more functionality?

3) I don't think the product ID is enough to stand as a primary key on its own given the other information stored within the product table unless this other information is given tables of its own.

e.g.

ProductID: Copper Sheet
Cut Size: 24 x 45

ProductID: Copper Sheet
Cut Size: 20 x 50

I, personally, would view these as two completely different products but your structure ensures that a Copper Sheet (in this case) can only be one product.

If a Copper Sheet can only be one product then the Cut Size, etc, should be in the Orders table.

Other than that, I'm having problems visualising this at all.

 

[Mile-O]

Out of interest - if these downloads are .zip files why are uploading them with a .bmp extension??

 

[pldd4]

ok,

1 - The customer is another company

2 - The company uses weeks, 1 - 4 weeks, its just the way they work. Not my company and thats the way they want it. I know its odd, ive spoke to them about these issues and they just dont like change!

3 - Product ID is completley unique with this company, i understand what your getting at. But in this case the product id is unique therefore suitable for a primary key. The rest of the information eg cut size is purely for descriptive purposes. Trust me on this

All that i really need to sort out is the query as mentioned in my first post. Just struggling with the sql etc.

I didnt notice you were allowed to attach zip files after trying the db file! Sorry.

 

[pldd4]

Hi,

Ok heres my database as i first had it before you suggested changing it. It might help to explain it better for you.

As you can see in the query Orders_Placed_By_Week this calculates nicely the total number of orders for each product for each week, which is great, thats what i want.

But i also want it to work out the correct stock level. (As described in the 1st post) Stock_Left_WK1 is correct as this subtracts the orders_for_wk1 from ProductTable.OS but the rest arnt!

Below is what i would like it to do to work out the stock levels for each week.


Stock_Left_WK1 = (ProductTable.OS - orders_for_wk1)
Stock_Left_WK2 = ( (Stock_Left_WK1 - orders_for_wk2) + (Production.ProduceWk1) )
Stock_Left_WK3 = ( (Stock_Left_WK2 - orders_for_wk3) + (Production.ProduceWk2) )
Stock_Left_WK4 = ( (Stock_Left_WK3 - orders_for_wk4) + (Production.ProduceWk3) )

hope this helps shed some more light on this for you.

Thanks again for your time

 

[Mile-O]

The company uses weeks, 1 - 4 weeks, its just the way they work. Not my company and thats the way they want it. I know its odd, ive spoke to them about these issues and they just dont like change!

More fool them, then.

I can't get anything to work with such a structure - I've tried messing about with the relationships too as you have one-to-one relationships on the tables when, logically, one customer can have more than one order.

Is the intention of this to be typed directly into the tables by the user without forms?

With the whole structure and the mindset of the company you are doing this for, I cannot see a way to get anything going on.

But the examples are posted, so maybe someone else can try further.

 

[pldd4]

ok thanks for your efforts.

Eventually i believe the user will enter and view data via forms. But ive not done that at this stage, just wanted a basic working structure first!

So how would you of done this?
I'd be interested to know what you'd do whilst trying to keep it as similar to what ive attempted so far.

Surely it cant be that hard. Cant the values from the current query for each of the 4 stock levels be put into a seperate table (already created) then perform some calculations to work out the correct figure like a balance sheet as below!


Stock_Left_WK1 = (ProductTable.OS - orders_for_wk1)

Stock_Left_WK2 = ( (Stock_Left_WK1 - orders_for_wk2) + (Production.ProduceWk1) )

Stock_Left_WK3 = ( (Stock_Left_WK2 - orders_for_wk3) + (Production.ProduceWk2) )

Stock_Left_WK4 = ( (Stock_Left_WK3 - orders_for_wk4) + (Production.ProduceWk3) )


Cheers,

 

[neileg]

Our company firewall won't let me post back here so I'll describe a way you might get what you want. I still think it's all wrong though. Your approach would be better suited to a spreadsheet.

Create a totals query based on your Orders table for each week with a qualifyer on the week number. That's four queries giving you the total orders.

Do the same with the Production table. Four more queries.

Then construct a form and create controls for these totals for each week, and a calculated control for the closing stock. The calculated control will follow the formulae you quoted in your first posting. So your form will have four rows. The rows will have controls like this:

Row 1 [OpStk] [OrdWk1] [ClStkWk1=OpStk-OrdWk1]
Row 2 [ClStkWk1] [ProdWk1] [OrdWk2] [ClStkWk2=ClStkWk1-OrdWk2+ProdWk1]
Etc.

You might like prototyping in tables but there are things that are difficult or impossible to do in tables.

 

[pldd4]

Not to sure what you mean fully with regards to the forms

You can email me it on pldd4@aol.com

Thanks

 

[pldd4]

Ok ive done those 8 querys, just never done a form like you described.

Plz help.

I cant attach new db with querys in as its too big zipped.

Cheers,

 

[neileg]

On reflection, you can do it in a query! Here's the SQL based on the 8 queries so far and the product table.

SELECT [Product Table].[Product ID], Nz([OS])-Nz([OrdWk1.SumOfOrder]) AS StkEndWk1, Nz([StkEndWk1])-Nz([OrdWk2.SumOfOrder])+Nz([ProdWk1.SumOfProduce]) AS StkEndWk2, Nz([StkEndWk2])-Nz([OrdWk3.SumOfOrder])+Nz([ProdWk2.SumOfProduce]) AS StkEndWk3, Nz([StkEndWk3])-Nz([OrdWk4.SumOfOrder])+Nz([ProdWk3.SumOfProduce]) AS StkEndWk4
FROM ((((((([Product Table] LEFT JOIN OrdWk1 ON [Product Table].[Product ID] = OrdWk1.[Product ID]) LEFT JOIN OrdWk2 ON [Product Table].[Product ID] = OrdWk2.[Product ID]) LEFT JOIN OrdWk3 ON [Product Table].[Product ID] = OrdWk3.[Product ID]) LEFT JOIN OrdWk4 ON [Product Table].[Product ID] = OrdWk4.[Product ID]) LEFT JOIN ProdWk1 ON [Product Table].[Product ID] = ProdWk1.[Product ID]) LEFT JOIN ProdWk2 ON [Product Table].[Product ID] = ProdWk2.[Product ID]) LEFT JOIN ProdWk3 ON [Product Table].[Product ID] = ProdWk3.[Product ID]) LEFT JOIN ProdWk4 ON [Product Table].[Product ID] = ProdWk4.[Product ID];

Important points to note:
Nz converts any values that are null to a zero. Without this, the arithmetic will fail any time there is no value.
You need to use a left join between the Product table and each of the queries otherwise you will only get an answer if all queries produce a non null result.