[RESOLVED] [Help] SQL for Datareport Complex Query

[ayankent]

Hi to all.

I need some help with my project.
I have a database that contain 4 tables: Items, Purchased, Sold, Stocks
I want to make a recordset from these database.
The Items database contains Item Information: BarCode, Name, Size, Price, Category
The Purchased database records Purchase ordered Items: BarCode, Units, Date
The Sold database records Sold out Items: Barcode, Units, Date
The Stocks database records available Items in stock: Barcode, Units, Date

I wanted to query the Database to get a result like this:

Items.Barcode - Items.Name - Items.Size - Sold.Units

But Sold database contains records with the same Barcode but different Date and Units. So I am looking for a way to automatically add Unit from records with same Barcode in a span of days.
I will need the result to display in a datareport.
I also want it in order by category.

I hope someone can help.

[Grifter]

Hi to all.

I need some help with my project.
I have a database that contain 4 tables: Items, Purchased, Sold, Stocks
I want to make a recordset from these database.
The Items database contains Item Information: BarCode, Name, Size, Price, Category
The Purchased database records Purchase ordered Items: BarCode, Units, Date
The Sold database records Sold out Items: Barcode, Units, Date
The Stocks database records available Items in stock: Barcode, Units, Date

I wanted to query the Database to get a result like this:

Items.Barcode - Items.Name - Items.Size - Sold.Units

But Sold database contains records with the same Barcode but different Date and Units. So I am looking for a way to automatically add Unit from records with same Barcode in a span of days.
I will need the result to display in a datareport.
I also want it in order by category.

I hope someone can help.

don't understand your problem - the bit in bold - what actually do you mean?

G

[GaryMazzone]

What is the database it makes a difference in what kind of SQL you will run. It sounds like you are mixing terms here also is Stocks a table or a database?

[ayankent]

oh... sorry.. its all a table in a single database...

But Sold database contains records with the same Barcode but different Date and Units. So I am looking for a way to automatically add Unit from records with same Barcode in a span of days.

Sold table cantains records with same Barcode but in different date and units.
I am looking for a query that will automatically add the Units with same Barcode in a specified span of date...

[si_the_geek]

Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB, it is certainly not specific to VB)

[ayankent]

sorry sir... and thanks...

[GaryMazzone]

Once again what is the backend datadase? MS Access? SQL Server? Oracle? MySQL?

[ayankent]

MS Access...

I am using DataEnvironment to populate the Datareport (VB6)

Example on the table Sold there are these data:

8851717200014 - 5 - 10/14/2011
8851717200015 - 5 - 10/14/2011
8851717200018 - 5 - 10/14/2011
8851717200014 - 3 - 10/15/2011
8851717200015 - 4 - 10/15/2011
8851717200018 - 3 - 10/15/2011
8851717200014 - 2 - 10/16/2011

the Items table contains some info about the 3 products.

     Barcode      -      Item Name      -  Size - Price  - Category
8851717200014 - Strawberry Yogurt - 90ml - 18.00 - Beverages
8851717200015 - Chocolate Waffer  - 1.5g - 5.00- Biscuits
8851717200018 - Supreme Seafoods - 65g - 23.00 - Noodles

I wanted my query to provide this kind of result when I query the table of Sold along with Items base on date Sold.Date from 10/14/2011 to 10/16/2011:

     Barcode      -      Item Name      -  Size - Units  
8851717200014 - Strawberry Yogurt - 90ml - 10
8851717200015 - Chocolate Waffer  - 1.5g - 9
8851717200018 - Supreme Seafoods - 65g - 8

[GaryMazzone]

Try something like this:

Select 
	Items.BarCode ,
	Items.Name ,
	Items.Size
	SUM(Sold.Units)
From 
	Items
INNER JOIN Sold
    ON Items.BarCode = Sold.BarCode
Where Sold.[Date] BETWEEN "Date1# AND #Date2#

(This is a bad field name (DATE) to use by the way as it is a Reserved word)

[ayankent]

Try something like this:

Select 
	Items.BarCode ,
	Items.Name ,
	Items.Size
	SUM(Sold.Units)
From 
	Items
INNER JOIN Sold
    ON Items.BarCode = Sold.BarCode
Where Sold.[Date] BETWEEN "Date1# AND #Date2#

(This is a bad field name (DATE) to use by the way as it is a Reserved word)

thanks... i will try it right away... and thanks for the advise about Date being a reserved word... I totally forgot about it... I will surely change it right aways...

[ayankent]

I tried your code but it wont show any result on my DataEnvironment and thus, wont populate the Datareport... this is the actual code I used...

select Items.ICode, Items.IName, Items.USize, sum(Sold.Units) from Items inner join Sold on Items.ICode = Sold.ICode

I tried this query on Visual Data Manager but all I get is errors...

[GaryMazzone]

This was written directly in to the webpage. I do not have your DB so can not test on it. It is just SQL what are the errors?

[ayankent]

in dataenvironment... it doest show an error...

[GaryMazzone]

Run it in Access then an see what it tells you..... I can't guess as to what is wrong as I can't see your computer screen from here.

[GaryMazzone]

OK Try like this (I missed the Group By clause)

Select 
	Items.BarCode ,
	Items.Name ,
	Items.Size ,
	SUM(Sold.Units)
From 
	Items
INNER JOIN Sold
    ON Items.BarCode = Sold.BarCode
Where Sold.[Date] BETWEEN #Date1# AND #Date2#
GROUP BY Items.barcode , items.NAME  , items.SIZE

Leave out the Where clause if you don't care about the date range

[ayankent]

OK Try like this (I missed the Group By clause)

Select 
	Items.BarCode ,
	Items.Name ,
	Items.Size ,
	SUM(Sold.Units)
From 
	Items
INNER JOIN Sold
    ON Items.BarCode = Sold.BarCode
Where Sold.[Date] BETWEEN #Date1# AND #Date2#
GROUP BY Items.barcode , items.NAME  , items.SIZE

Leave out the Where clause if you don't care about the date range

Thanks... It worked...

[GaryMazzone]

HP-- If this solves the problem please use the Thread Tools to mark this as Resolved.

[ayankent]

Another questions...

1. How can I make it that DataEnvironment take date value from a textbox and append it in a query dynamically at runtime?

2. How can I make my datareport dynamically? My "Items" table contain a "Category" column, therefor I made 4 DataEnvironment Command for each category that I have which are "Biscuit", "Beverage", "Noodle" and "New Product".

[GaryMazzone]

Don't know for either of these questions. I code everything by hand and don't use the DataEnviorment.

But if you can pass a completed SQL statement to that then I would code the SQL in the code and pass to the DataEnvirronment as shown in my example and also adding a second where condition to include the Category.

[ayankent]

I also wanted to code all by hand but I do not know how... This is my first time using DataReport... Could you please help me with this? I am not also familiar with ADODC... Only done code in DAO since I started programming...

This is what I Want my report to show:

Barcode Driven Inventory System

Barcode Beverages Size Units
8851717200014 DM Blueberry Yogurt 90mlx48 98
8851717200015 DM Strawberry Yogurt 90mlx48 98
8851717200016 DM Mix Fruit Yogurt 90mlx48 98
8851717200017 DM Blueberry Yogurt 180mlx48 98
8851717200018 DM Strawberry Yogurt 180mlx48 98
8851717200019 DM Mix Fruit Yogurt 180mlx48 98
Barcode Biscuit Size Units
8851717200020 Yummy Wafer 20x20x1.5g 98
8851717200021 Chocolate Wafer 20x20x1.5g 98
8851717200022 Vanilla Wafer 20x20x1.5g 98
8851717200023 Yummy Wafer 20x20x3.5g 98
8851717200024 Chocolate Wafer 20x20x3.5g 98
8851717200025 Vanilla Wafer 20x20x3.5g 98


and so on...

[GaryMazzone]

As I said I do not use ADODC or data reports.

[ayankent]

how do you do it? using DAO?

[GaryMazzone]

I use ADODB. Coded directly

[ayankent]

can you help me with it... I am not familiar with ADODB...

[GaryMazzone]

There are example in the FAQ section of the database area

[ayankent]

I solved my problem on this... thanks to you GaryMazzone....