Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Chris_H]

Here endeth the sequel sequel? 

[cem_devecioglu]

Here endeth the sequel sequel? 

yep..ended. if I wrote the actual code would be long enough but brain not performing well at the moment

also there are other alternative solutions but too much alcohol in blood

[TheBoy]

Cheers cem - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat

Thank you very much


Expect a harder query next time (probably over the weekend)

[cem_devecioglu]

Cheers cem - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat

Thank you very much


Expect a harder query next time (probably over the weekend)

your are nearly passing my knowledge limits I hope I can solve next time

Good night

[TheBoy]

Cheers cem - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat

Thank you very much


Expect a harder query next time (probably over the weekend)

your are nearly passing my knowledge limits I hope I can solve next time

Good night

Oh, its going to be a pig of a query.  If you can even think you can get the data out with a single query, then you are a better man than me

I need to look at the tables and relationships again before I can give you a clue

[cem_devecioglu]

Cheers cem - I used your SQL, worked out how to manipulate into my flexgrid, seems to work a treat

Thank you very much


Expect a harder query next time (probably over the weekend)

your are nearly passing my knowledge limits I hope I can solve next time

Good night

Oh, its going to be a pig of a query.  If you can even think you can get the data out with a single query, then you are a better man than me

I need to look at the tables and relationships again before I can give you a clue

I hope I can help..sometimes I use temporary tables to hold the calculated tables if I'm lazy to use  matrix..

[TheBoy]

Cem, that query is now in production use in my Brother's shop

Ready for the next one?

[cem_devecioglu]

Cem, that query is now in production use in my Brother's shop

Ready for the next one?

yep..

[TheBoy]

I need a query that will get the total stock value (actual cost, and current retail).

Current retail is easy: = Qty in stock * current retail price.

Actual cost is more tricky, as it needs to ascertain how many in stock, then step back through invoices to work out actual cost paid for those items in stock - ie, last invoice may say £100 cost, previous invoice may say £90. So if there are 3 in stock, 2 were on last invoice, and 2 were on previous invoice, it needs to work out 2 x £100 + 1 x £90. If that makes sense?

Tables:
Products
ProductID - unique key
Description - description of product
RetailPrice - current retail price
CostPrice - current cost price (but not used in this query?)

StockLevel
StockLevelID - unique key
BranchID - link to branches (query needs to simply work for all branches added together)
UnitsInStock - integer for how many in stock at branchid

Invoices:
InvoiceID - unique key
ProductID - link to products
Quantity - integer for number of this item recieved
CostPrice - price for each item on this invoice


Does that make sense?

[TheBoy]

Would it help if I sent you an blank Access DB to show the DB layout?

[cem_devecioglu]

I need a query that will get the total stock value (actual cost, and current retail).

Current retail is easy: = Qty in stock * current retail price.

Actual cost is more tricky, as it needs to ascertain how many in stock, then step back through invoices to work out actual cost paid for those items in stock - ie, last invoice may say £100 cost, previous invoice may say £90. So if there are 3 in stock, 2 were on last invoice, and 2 were on previous invoice, it needs to work out 2 x £100 + 1 x £90. If that makes sense?

Tables:
Products
ProductID - unique key
Description - description of product
RetailPrice - current retail price
CostPrice - current cost price (but not used in this query?)

StockLevel
StockLevelID - unique key
BranchID - link to branches (query needs to simply work for all branches added together)
UnitsInStock - integer for how many in stock at branchid

Invoices:
InvoiceID - unique key
ProductID - link to products
Quantity - integer for number of this item recieved
CostPrice - price for each item on this invoice


Does that make sense?

yep.. will try now.. 

[cem_devecioglu]

I see one problem, there must be product ID field
in stocklevel table or I cant sum up .. probably you forgot to write :-/

[cem_devecioglu]

Would it help if I sent you an blank Access DB to show the DB layout?

nope not necessary.. only field and table names are adequate..

[TheBoy]

I see one problem, there must be product ID field
in stocklevel table or I cant sum up .. probably you forgot to write :-/

Yeah, my bad, there is a ProductID in table StockLevel...

[cem_devecioglu]

I see one problem, there must be product ID field
in stocklevel table or I cant sum up .. probably you forgot to write :-/

Yeah, my bad, there is a ProductID in table StockLevel...

now the simple one currentretail calculation


assuming you dont want calculate per branch instead global otherwise we must use Branchid in group by

select p.ppid,p.Description,p.Retailprice,curretail=p.Retailprice * s.cumstockforprod from
(select ppid=ProductID,Description,RetailPrice,CostPrice from Products) as p,
(select spid=ProductID,cumstockforprod=sum(UnitsInstock) from StockLevel group by ProductID) as s
on p.ppid=s.spid

and now solving the other..