Jet SQL help
Jet SQL help
Jet SQL help
Jet SQL help
Jet SQL help
Jet SQL help Jet SQL help Jet SQL help Jet SQL help Jet SQL help Jet SQL help Jet SQL help Jet SQL help
Jet SQL help Jet SQL help
Jet SQL help
Go Back  Xtreme Visual Basic Talk > > > Jet SQL help


Reply
 
Thread Tools Display Modes
  #1  
Old 06-23-2017, 11:20 AM
Daigon Ali Daigon Ali is offline
Regular
 
Join Date: Aug 2014
Location: London
Posts: 59
Default Jet SQL help


Hi, I have two tables in an Access (mdb) database.

The first one contains items (Fruit in this example) and a target sales figure.
The second contains dates and how many items were sold.

ITEM Target
Apple 100
Pear 150

ITEM Date Quantity sold
Apple 19/06/2017 25
Apple 20/06/2017 3
Apple 21/06/2017 9
Apple 22/06/2017 2
Apple 23/06/2017 14
Pear 16/06/2017 54
Pear 17/06/2017 6

What I need to do is select a distinct list of ITEM where the sum of Quantity Sold is less than the Target for that item for the current year.
Reply With Quote
  #2  
Old 06-23-2017, 08:13 PM
Kluz's Avatar
KluzJet SQL help Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

You would need to use nested SQL statements. The inner SQL does the summing and the outer does the selecting. Selecting from the smaller table will eliminate the 'DISTINCT' aspect. Look into the difference between 'WHERE' and 'HAVING' in SQL language.
Please post your best attempt and where you thing you're having problems.
__________________
No the other right mouse click
Reply With Quote
  #3  
Old 06-26-2017, 04:51 AM
Daigon Ali Daigon Ali is offline
Regular
 
Join Date: Aug 2014
Location: London
Posts: 59
Default

Thank you. I managed to get it working with this.

Code:
"SELECT [ID] FROM tblItem WHERE [Target] > (SELECT SUM[Sold]) FROM tblSales WHERE YEAR([Date]) = 2017 AND tblItem.[ID] = tblSales.[Item ID])"
Reply With Quote
Reply

Tags
apple, item, sold, pear, target, quantity, items, 16/06/2017, 17/06/2017, 21/06/2017, 22/06/2017, 23/06/2017, sum, current, list, select, distinct, mdb, access, database, fruit, tables, sql, jet, date


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Jet SQL help
Jet SQL help
Jet SQL help Jet SQL help
Jet SQL help
Jet SQL help
Jet SQL help Jet SQL help Jet SQL help Jet SQL help Jet SQL help Jet SQL help Jet SQL help
Jet SQL help
Jet SQL help
 
Jet SQL help
Jet SQL help
 
-->