VB and Excel.

[vishalsomani]

Hello,
I have attached a database which will be in use for the question i am about to ask.

The question is ... Write queries to retrieve the following sets of information.
a) An annual report showing the number of orders and the value of the orders placed by each supplier, as a bar chart.
b) A list of orders exceeding a value specified by the user.

How do i start and what code shud i put in ? Please if anyone can help.
If you can create a VB form and send me that on [e-mail] , it will be much appreciable.
Thanks.

So far whatever I have done, I have attached it but it doesn't seem to work.

[Yomet]

vishalsomani,

Since this is an assignment I will not give you a full answer to your question, that is against the rules of this forum. However, I will give you the SQL queries to get the data you want since you could easily get those through Access anyway.

Here goes:

For the first problem of yearly sales per supplier you can use the following query to get your data:
SELECT Orders.SupplierID,
Suppliers.CompanyName,
Sum([Quantity]*[UnitPrice]) AS TotalAmt,
Count(Orders.OrderID) AS CountOfOrderID
FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID
WHERE Year([OrderDate])=2003
GROUP BY Orders.SupplierID, Suppliers.CompanyName;

What you need to do here is to replace the year, i.e. 2003, with the year you are interested in when you build the query in your code, something like:

Dim SQLString As String

SQLString = "SELECT Orders.SupplierID, Suppliers.CompanyName, Sum([Quantity]*[UnitPrice]) AS TotalAmt, Count(Orders.OrderID) AS CountOfOrderID " 
SQLString = SQLString & "FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID " 
SQLString = SQLString & "WHERE Year([OrderDate])= " & Inputbox("Please enter the year for the annual report:") 
SQLString = SQLString & " GROUP BY Orders.SupplierID, Suppliers.CompanyName"

For the second problem, of getting orders above a certain amount you should use the following query:
SELECT Orders.OrderID,
Orders.Product,
Orders.UnitPrice,
Orders.Quantity,
[Quantity]*[UnitPrice] AS TotalAmt,
Orders.OrderDate,
Orders.Received,
Orders.SupplierID,
Suppliers.CompanyName
FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID
WHERE [Quantity]*[UnitPrice] >= 1000;

Here the trick is to get the total amount above which the users are interested you can use the same technique as above and then the code would look something like this:

Dim SQLString As String

SQLString = "SELECT Orders.OrderID, Orders.Product, Orders.UnitPrice, Orders.Quantity, [Quantity]*[UnitPrice] AS TotalAmt, Orders.OrderDate, Orders.Received, Orders.SupplierID, Suppliers.CompanyName " 
SQLString = SQLString & "FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID " 
SQLString = SQLString & "WHERE [Quantity]*[UnitPrice] >=  " & InputBox("Please enter the order total to report on:")

If you want to use other ways of getting the variable input into your queries just replace the InputBox() statements with your variables and everything should work OK.

Hope this helps

Happy coding

Yomet

[vishalsomani]

I thank you very much for your reply but this question is of Excel.. and i dont know if i have to make an excel chart in Excel first or just make it. However the queries which you have given me, i dont really understand where to put them in code because we have to show this as a bar chart and not about database if you understand me.
I have attached the Excel form i have tried, but it doesn't work. Please help. Thanks. Can you explain me my mistakes i have done in the excel form and help me correct them and explain me where this queries have to come please. Thanks once again. Vishal.

Hello,
I have attached a database which will be in use for the question i am about to ask.

The question is ... Write queries to retrieve the following sets of information.
a) An annual report showing the number of orders and the value of the orders placed by each supplier, as a bar chart.
b) A list of orders exceeding a value specified by the user.

How do i start and what code shud i put in ? Please if anyone can help.
If you can create a VB form and send me that on [e-mail] , it will be much appreciable.
Thanks.

So far whatever I have done, I have attached it but it doesn't seem to work.

[vishalsomani]

Then there is this other question.
1Write queries to retrieve the following sets of information.
a) A monthly order report for a specific supplier.

2) Embed each of the above queries in procedural code attached to a form. The queries are to be executed in response to events occuring to one or more controls within the form. An excel chart is to be used for query 1b) which is An annual report showing the number of orders and the value of the orders placed by each supplier, as a bar chart.

Hope you understand this.
Cheers, Vishal.

I thank you very much for your reply but this question is of Excel.. and i dont know if i have to make an excel chart in Excel first or just make it. However the queries which you have given me, i dont really understand where to put them in code because we have to show this as a bar chart and not about database if you understand me.
I have attached the Excel form i have tried, but it doesn't work. Please help. Thanks. Can you explain me my mistakes i have done in the excel form and help me correct them and explain me where this queries have to come please. Thanks once again. Vishal.

[Yomet]

vishalsomani,

First of all I can tell you that are on the right track.

Now, the queries I gave you will (should) be used to populate the data area for your Chart. So what you do is simply open a connection to the database and set the recordset to be the query I gave you, i.e. for the Annual Report chart we would have something like:

Private Sub Command1_Click()
   Dim Prod As Connection
   Dim rs As Recordset
   Dim i As Integer
   
   Set Prod = New ADODB.Connection
   Prod.Provider = "Microsoft.Jet.OLEDB.4.0;"
'NEVER Hard-Code a path unless you have a VERY good reason for doing this.
'Rather use the app.path to see where the application currently is
'   Prod.ConnectionString = "C:\VB Assignment Uni\Assignment\OK.mdb"
   Prod.ConnectionString = App.Path & "\OK.mdb"
   Prod.Open
   Set rs = New ADODB.Recordset
   
   rs.Open "SELECT Orders.SupplierID,Suppliers.CompanyName,Sum([Quantity]*[UnitPrice]) AS TotalAmt,Count(Orders.OrderID) As CountOfOrderID " & _
           "FROM Orders LEFT JOIN Suppliers ON Orders.SupplierID = Suppliers.SupplierID " & _
           "Where Year([OrderDate]) = 2003 " & _
           "GROUP BY Orders.SupplierID, Suppliers.CompanyName", Prod, adOpenDynamic, adLockOptimistic
      
   Set xlApp = New Excel.Application
   'Remove the following line to hide Excel, I want to see it for debugging
   xlApp.Visible = True
   Set xlWkb = xlApp.Workbooks.Add
   Set xlsht = xlWkb.Worksheets(1)

From this point you scroll through the recordset populating the A, B and C columns of your spreadsheet with the data returned by the query. This is now the data range for your chart (so make sure you keep track of the range you are populating, i.e. rows and columns).

The next code I am giving you is straight from the best way of learning how to code a new MS application - the macro recorder. Try this next time, and I really want you to try it ;)

What I did was to:
- Open a new Excel spreadsheet
- Paste the results of my first query into cell A1
- Delete the header line
- Start recording the macro while I added the new bar chart as per the specs.

This is the code that Excel gave me

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 02 24 2006 by Yomet
'

'
    Charts.Add
    ActiveChart.ChartType = xlBarClustered
    ActiveChart.SeriesCollection(1).Name = "=""Sales (* 1000$)"""
    ActiveChart.SeriesCollection(2).Name = "=""Orders"""
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Annual Report"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Company"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales / Orders"
    End With
End Sub

From this I learn exactly how to use a lot of the functionality of Excel charts.
Now I realized that I could not see the number of orders in the chart, that was my own fault, the sales are in thousands of dollars (Euros, pounds, yen - whatever) while the number of sales are in the singles, so I divided the sales by 1000 and "Presto!" I now have orders and sales on the same chart.

Now you are able to deal with data into Excel and from there to a chart so building the list you asked about in the same (first) posting should be kids play.

For 1a) I would load the list box you already have with all the suppliers and then let the user select one of them before clicking the button to launch the monthly report.

Since you will then be using all these items in a form and the form's buttons produce different results depending on user input you have already answered 2).

I have included the modified project in a zip file with all my modified code included. Please read my comments, I put them there for a reason, and I am sure you will figure out the rest.

Hope this helps.

Happy coding

Yomet

[Comatose]

Thumbs up to Yomet on this one. Nice code, and good way to redirect focus back into the forum instead of to his e-mail. Well Done.

[Yomet]

Thanks Comatose

[vishalsomani]

Hello,
Thanks for your mail.
I have now created the VB chart.
Lastly, I needed yur help in this writing a letter.
I have attached the copy of the letter, which shows thats how the letter should be like.

I only have to show one letter for any supplier.., with a command button which is done on form5.

The letter format is also attached.
I don't know how to call the variables if you can help me please.

[Yomet]

Hi again Vishal,

Happy to hear that things are working well and nice to see some good coding on your part.

Now for your latest problem, I am not sure that trying to use the Word's MailMerge functionality is the best way to go. All you will do is to create a template that you can then use with any specific supplier from within Word, not your application.

I am assuming that your assignment needs to have you make a letter, a full letter not a MailMerge template, from within your program.

Here's what I would do:

Add two combo boxes to your form, one will contain all your suppliers and the other will then be dynamically populated with the orders for the supplier selected in the first combo box. In order to do this create a Sub that will populate the second combo box when the user selects a supplier in the first one.

Once the user has selected both a supplier and an order (hint: use the OnClick of the second combo to determine if the user has selected both) and clicks the button you open a recordset in your program that contains all the supplier information. You use this recordset to create your letter header, inserting the actual values into the Word document instead of inserting MailMerge fields. It should actually be easier since you can use the same technique as with the fixed MIS company address. All you need to change are fixed strings for recordset fields.

Next comes the order details, you would then open another recordset, or reuse the first one with a different SQL string in your .Open statement, just remember to close the recordset first... ;)
Once you have opened this recordset you can easily loop through it to create your detail section, using a variable for the total amount of the order.

Seeing how far you came with your code from my last post I think this is about all you need in order to complete this assignment.

If you have more questions - go ahead and ask, I'll help as much as I can.

Happy coding

Yomet

[vishalsomani]

Hi Danny,
Thanks for your immediate reply.
I have got to hand in my Vb work on March 3rd so please reply as quick as possible.

I really don't know how to populate dynamically values from one combo box to another.

Can you tell me what code do i need to write and on which methods of which combo box please?
Once i get that,
I can start making my letter.
Please reply as soon as you can.

I need two combo boxes, the first one to be supplierID and secondone to be orderID.

I have populated supplierID combo box, the only problem is how to get related orderID's from supplierID.
Thanks Vish.

[Yomet]

Hi Vish,

What you need to do is to open a recordset that contains the orders for the selected supplier and then loop through that recordset while populating the combo box.

Since you already know how to open recordsets I will give you the query necessary. Assuming that your combo boxes are called Combo1 (for suppliers) and Combo2 (for orders) and that the bound column of Combo1 contains the SupplierID, here is the code that you put into the Combo1_Click event handler:

'Open your connection (Prod) and declare your recordset as rs
   Combo2.Clear   'Removes the current list from Combo2
   rs.Open "SELECT DISTINCT OrderID " & _
           "FROM Orders " & _
           "WHERE SupplierID = " & Combo1 & " " & _
           "ORDER BY OrderDate Desc", Prod, adOpenDynamic, adLockOptimistic
   While Not rs.EOF
      Combo2.AddItem rs.Fields("OrderID")
      rs.MoveNext
   Wend
'Since I don't trust ANY environment to do a proper cleanup of connections 
'and such I always do this explicitly in the code
   rs.Close
   Set rs = Nothing
   Prod.Close
   Set Prod = Nothing

Hope this helps

Happy Coding

Yomet

P.S. My name isn't Danny... ;)

[vishalsomani]

Thanks Yomet for all the help you have done.
I managed to hand in my assignment on time and got it working finally.. i hope for a good mark now.

I have this question in SQL.
There are three tables as follows: -
Student(studentId, studentSurname, StudentName, StudentDOB, credits)
Module(moduleId, moduleDescription)
Selection(studentId, moduleId, grade)

and i have attached the fields in the tables.
these three tables are linked by foreign keys as seen above.

Now the question I have to write a SQL command(query) to list all the modules for which the minimum grade achieved is 'B'. I have tried the query as follows:-
select DISTINCT moduledesc
from module mod, selection sel
where mod.moduleid=sel.moduleid
AND Grade='C';

The above query results in showing four moduledescriptions which are Database Systems, Networks, Data Structures and Alrogithms.

What i want is the other two which are languages and programming and not all this. I have also trieed where grade!='C' for which the result is all 6 module descriptions.

All module descriptions have got A,B,C apart from languages and programming which have only got A and B so that should be the result of the query I am asking for but it doesn't show up. Can you help in this?
Thanks,
Vish.

[vishalsomani]

sorry forgot to attach the table which shows all the records and fields in the tables.

[Yomet]

Hi again Vish,

Happy to hear that you got your assignment in on time and that everything worked out.

For this question I suggest trying by changing
AND Grade='C';
to
AND Grade < 'C';

However, since you want the minimum grade to be 'B' you cannot make the selection by saying "='C'" or "!='C'", the first will only give you those modules that have a 'C' as a grade (any grade) and the second one will also give you 'D' and 'F' (as well as any other grade) it will also give you any module that has 'A' or 'B' and 'C' since A and B exists. What you need is something much fancier than this. Here goes:

SELECT DISTINCT moduleDescription
FROM module mod, selection sel
WHERE mod.moduleId = sel.moduleId
AND mod.moduleId NOT IN (SELECT DISTINCT s.ModuleId
                         FROM Selection s
                         WHERE s.grade >= 'C')

The inner query of this SELECT statement, i.e.

SELECT DISTINCT s.ModuleId
FROM Selection s
WHERE s.grade >= 'C'

will give you all the moduleId's of modules that have a grade below 'B', no matter if they also have 'A' and 'B', so this should give you the modules to eliminate. That's why the outer query, i.e.

SELECT DISTINCT moduleDescription
FROM module mod, selection sel
WHERE mod.moduleId = sel.moduleId
AND mod.moduleId NOT IN (...)

uses a NOT IN syntax. This eliminates the moduleId's that the inner query produces and should give you the correct results to your question.

There are other ways to produce the same result by using the NOT EXISTS syntax in the outer query but it can become quite confusing, that's why I prefer this syntax.

Hope this helps

Happy coding

Yomet