2 Replies - 1344 Views - Last Post: 21 October 2009 - 06:59 AM

#1 rabihtawil   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 26-September 09

Best Select to Get balance (Data Access)

Post icon  Posted 20 October 2009 - 08:14 AM

Hello Community,

I have 2 tables: stock, inventory


Posted Image

Posted Image


1) inventory table is read-only
3) the query should take time efficiency in consideration.
4) it should be 1 query, moreover a SELECT statement
5) we can not change the table structure
6) simplicity is the key

i added a table called query_result just to show what the resulting cursor should look like with correct values.

the idea:
get balance of inventory based on deduction of non-expired movie rents in order table.
means that everything after DATE() is still rented, everything before DATE() should not be taken into consideration anymore.

the result:
same as query_result table, that hold the same fields as inventory table but with UP TO DATE balances.

Posted Image

The database is attached in a zip file. I experimented with this quite some time & have couple of results but are far from perfection.

http://www.coderisland.com/data.zip

here is my non-working far from perfect query, that is more sql like then access like:
Code:

SELECT INVENTORY.item_id,INVENTORY.ITEM_NAME,INVENTORY.qty,(SELECT NVL(SUM(ORDER.qty),0) FROM ORDER WHERE to_date=< DATE() AND ORDER.item_id=INVENTORY.item_id) AS IQTY FROM inventory


I hope i get some help.

Best regards.

Is This A Good Question/Topic? 0
  • +

Replies To: Best Select to Get balance (Data Access)

#2 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Best Select to Get balance (Data Access)

Posted 20 October 2009 - 06:41 PM

Inventory does not have field named item_id, it is inventory_id.

Inventory.inventory_id and Order.item_id are not same data type so cannot join on these fields. Have to fix tables then try queries. Use the Access report builder. The grouping and sorting features should be able to get the the output you are looking for.

This post has been edited by June7: 20 October 2009 - 06:41 PM

Was This Post Helpful? 0
  • +
  • -

#3 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 451
  • View blog
  • Posts: 2,187
  • Joined: 07-April 08

Re: Best Select to Get balance (Data Access)

Posted 21 October 2009 - 06:59 AM

try this:

SELECT I.Inventory_ID,I.Item_Name,I.qty,NVL(SUM(O.qty),0) AS IQTY
FROM Inventory I 
INNER JOIN Order O ON I.Inventory_ID = O.Item_ID 
WHERE O.to_date <= DATE() 


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1