Combined 2 recordsets into 1 Mshflexgrid using ADO. Possible ?

[ardneh]

Hi,

the title says it all. Is it possible if I want to join 2 recordsets (as the result of 2 queries) and then join the result into 1 mshflexgrid ? I am using Access 2000 database file and VB 6.0

Thx



hendra

[rudgej]

If the recordsets contain info in the same format as each other, could you not then just add them to the same grid by setting the grid rows property to the cumulative recordcount+1, and then use a For Next loop to add each recordset to the grid?

[ardneh]

the recordsets have different fields.

I cannot have a single query that can result with only 1 recordset..
And hence, I need to create 2 querries (and therefore 2 recordsets)..

any other idea ?

[rudgej]

Could you create a query in Access 2000 that combines the data, and then use this as a single recordset to fill the grid?

Alternatively, if there is a common field in both recordsets, you could fill the grid with one recordset, and then loop through the second recordset filling the grid at the relevent row:

' Enter first recordset into grid
rs1.MoveLast
rs1.MoveFirst

fg.rows = rs1.RecordCount+1

For row = 1 to rs1.RecordCount
    ' Fill record into grid
Next row

' Enter second recordset into grid
rs2.MoveLast
rs2.MoveFirst

For i = 1 to rs2.RecordCount

    For row = 1 to fg.Rows-1    
        If rs("your keyfield") = fg.TextMatrix(row, COLUMN) then
            ' Stick the current record's details into 
            ' the grid at the current row
            Exit For
        End If
    Next row

    rs2.MoveNext

Next i

You would obviously have to change the COLUMN variable to be whatever column number that your relevent field was stored in.

[HeVa]

ardneh:

How are the recordsets related that you want to display them in one heirarchical grid? Heirarchical grids are great for one-to-many relationships created in heirarchical recordsets. If you have one column in one recordset that you can relate to a column in the other recordset, then you'd be better off creating a single heirarchical recordset rather than two distinct recordsets. I apologize if I've missed something ... if you need more help let me know, I just don't want to go into too much detail if I'm not providing anything useful.

Good Luck with it!

[marex]

With SQL's SELECT statement you can do that.

Look in your VB help on SELECT statement, there are
samples.

This is one with two tables, Employees and Department


SELECT Employees.Department, Supervisors.supvname
FROM Employees Inner Join Supervisors
WHERE Employees.Department = Supervisor.Department;



Ray

[ardneh]

Forgive me for not giving more details on the question. Allow me to do so right now.

I have 3 tables. In table 1, I stored the information of each PO (Purchase Orders) , the important fields in this table is the week where PO is ordered, total input, total output and total waste.

In table 2, I stored information about the defects due to PROCESSING that I found in each PO. The fields in this table are PO Number, Defect Code and the Meter scrapped.

In table 3, I stored information about the defects due to DOCTORING that I found in each PO. The fields are similar to table 2, i.e PO Number, Defect Code, Meter Scrapped, Length in Roll Meter.

Now, I would like to create a summary of a specific defects (for example we take "Human Error" defect) throughout the year.
The result of the query, would have the following fields, they are
Week Number, Total Meter Scrapped, Frequency, and Total Waste.

In order to have both defects in table 2 and table 3, I combined these 2 tables and named the query set as "Combined Table".
Now I have all the defects with its individual meter in just one table.

So I have the following SQL statement

SELECT Sum([PO Information].[Waste Meter]) as [SumOfWaste Meter], Count([Combined Table].[Fault Code] as 
[CountOfFault Code]), Sum([Combined Table].[Metre Scrapped]) as [SumOfMetre Scrapped]), [PO Information].[Week No]

FROM [PO Information] INNER JOIN [Combined Table] ON [PO Information].[PO Number] = [Combined Table].[PO Number]
WHERE ([COmbined Table].[Fault Code] = "115")
GROUP BY [PO Information].[Week No] 

The resulting querry, seems correct, but once I change the Fault Code (e.g. from "115" to "100"), I got different Sum of Total Waste .. And after so much time wasted, I found that, the querry will do 2 criteria before they total up the Total Waste. First they are grouped into their week number and then select only those that has Fault code "115".
And hence, the total Waste is not the TRUE refelection of Total Waste of each POs found in any week.

Have I confused you yet ?

Let me rephrase:
For example: in week 31 total waste s'pose to be 1000 m. And there are 20 POs .. but out of 20, only 10 POs have Defect Code "115"..
So the querry will sum of the Total waste of the 10 POs only and not 20 PO s.


Hence my idea is to have 2 querries from VB. One is the one that calculate the lenth of meter scrapped and its frequency. And another querry to total the Waste grouped by week number.
And then combined these 2 querries into 1 mshflexgird.

I appreciate for all those reply. I have someone can help me out here.

I have another question: How Do I count Unique Records ??
For example.. there are total 20 rows, but out of 20 rows, there are only 5 different unique records.. how do I count this ?
Using COUNT, will give me 20 instead.

I'm using Access 2000 db file and VB 6.0

Thank you very much




hendra

[HeVa]

hendra:

Thanks for the details -- I think I understand now, and that I can help you. First of all, don't try to do everything in one query. It's not a bad thing to need to use two or more queries to get the information you need (but you don't want to get all crazy with the queries either). In some cases, creating one overloaded query will actually run slower than two individual queries that produce the same result. I've seen it happen.

That said, I think that your query could be simplified if your database design was a little less complicated. If it were me, I wouldn't use two different tables to store the PROCESSING and DOCTORING errors. I would simply create a signle ERRORS table and create a boolean field called, say, DEFECT_CLASS; 1 would refer to PROCESSING errors and 0 would refer to DOCTORING errors. This field could be used to group and/or filter the records in any query you might want to create. Not only would this eliminate the step in which you query the two separate tables to create a single table, it would also make the aggregate functions that you need to include in your query much more straight forward.

The next point I want to make is that if you need to get a sum of total waste you will never be able to do it with any query that filters the records in anyway (i.e., nothing can be in the "WHERE" clause of your query). Since you're trying to get data at two different summary levels, you will need to create at least two queries. But then you can query the results of each query to produce one recordset for your MSHflexgrid. I still believe that you should be able to link the tables in your database through a SHAPE command to create a heirarchical recordset. But, since you only posted one query, I can't help you with any code to do that.

To get a count of unique records (and I think this depends on what kind of database & cursor you're using) you should be able to use the DISTINCT keyword (e.g., "SELECT DISTINCT ...").

[ardneh]

HeVa,

Thank you for your explanation. I agree totally with you that I should not have 2 tables to separate the 2 types of defects. But, the codes have been written for 2 tables and hence I do not feel like joining these two tables into 1.

All the querries I created are called from Visual Basic and hence I wonder if what you suggested is really possible.
If I created 2 querries from Visual Basic (ADO connection), can I joined these 2 querries as one ??

I've come across the "SHAPE" command but totally not familiar with it. The SQL is much easier to learn, because I can use the Querry design from Access to get the SQL command.

Thank a lot for your reply Heva...



hendra

[HeVa]

hendra:

I can totally relate to you having to deal with legacy code. You can always store results of queries in the database using a "SELECT ... INTO" statement (i.e., Access Make-Table query) and then join the tables as you would any other. The question is, if your process has this many steps, will the user need to wait too long for the data to fill the MSHFlexGrid.

I don't want to spin your wheels with a bunch of guesses about your database. But if you think it would be useful, you can post the code for both queries (there's only one in the previous post) and a list of tables involved and their fields. If you give me enough information, I'm confident that I can help you come up with a shape command to create a recordset for you MSHFlexGrid. Otherwise, I'm not so sure your data belongs in an MSHFlexGrid.

I think I said this before, but the MSHFlexGrid is really meant to hold a recordset created with a SHAPE command. A heirarchical grid to store a heirarchical recordset. Once you get the SHAPE command right, the MSHFlexGrid 'knows' what do with it. I wouldn't be intimidated by the SHAPE command ... it's just an extra command that ADO adds to SQL. It gets messy fast, so I can see why some people might read a little about it and not want to touch it with a ten-foot pole. But in the same way that you use access to give you SQL language, you can use the VB Data Environment Designer (DED) to get the SHAPE command. All you have to do is use DED to create a heirarchical one-to-many type relationship between two or more tables. Then right click on the top-most table to get the 'heirarchy info ..." What you see is the shape command. You can either use this connection and the commands to fill your MSHFlexGrid or you can copy the SHAPE command and open a recordset directly, using the shape command as you would an SQL string. I'm not sure if you can combine joins and shape commands (though I don't see why not), so you may need to do your join and then open a recordset on a shape command.

Anyway, I'm starting to ramble, so maybe you should take a look at an example and get some ideas from there:

MSHFlexGrid Sample [Planet Source Code]

Heirarchical DED sample [Planet Source Code]

Again, best of luck with it!

[ardneh]

HeVa,

totally grateful for you spending some extra time answering my question and explaining stuff.

Shape command seems to hard to be understand in just a few days and my project deadline is coming within 4 days from now.

I wish you can give me some hand with this.. but if you not, it should be allright.

Here are the tables with its fields (I only stated the fields needed)
[PO Information] table with the following fields
- PO Number
- week number
- setup waste
- unaccounted waste
- total waste

[combined table] --> (which is the combination of Proccessing and Doctoring waste). It has the following fields
- PO Number
- fault code
- meter scrapped

Now, I need the following output grouped by the week number
- sum of setup waste
- sum of unaccounted waste
- sum of printing waste
- sum of laminating waste
- sum of slitting waste
- sum of total waste

So how can we get the printing waste, laminating waste and slitting waste ??
We can get the information from the Combined Table. The fault

codes are grouped into 3 groups (Printing, laminating and slitting defects). We can differentiate them by the first number.. All those

start with "1" is printing defects, "2" is laminating and "5" is slitting

I can do 4 separate querries to get what i want. The first querry will be using [PO Information] table , from there I should be able

to get setup waste, unaccounted waste and total waste. All these grouped by the week number

Another 3 querries are to read from the [Combined table] and then extract all the codes for Printing only and sum the meter

(not to forget to Group the PO number by week number), another querry to extract the laminating and another one to extract only

the slitting waste.

And then, how do I combined all these 4 tables into 1 mshflexgrid ?

1st querry SQL

SELECT [PO Information].[Week No], Sum([Printing setup]+[Laminating Setup]+[Slitting setup]) AS [Setup Waste], Sum([PO Information].[UnAccounted Waste]) AS [Unaccounted Waste], Sum([Waste metre]+[Printing Setup]+[Laminating Setup]+[Slitting Setup]) AS [Factory Waste]
FROM [Combined Table] INNER JOIN [PO Information] ON [Combined Table].[PO Number] = [PO Information].[PO Number]
WHERE ((([PO Information].[Week No]) Between 1 And 53) AND (([Combined Table].[Fault Code]) Like '1*') AND ((Year([Approved Date]))=2001))
GROUP BY [PO Information].[Week No]; 

2nd querry SQL

SELECT [PO Information].[Week No], Sum([Combined Table].[Metre Scrapped]) AS [SumOfMetre Scrapped]
FROM [Combined Table] INNER JOIN [PO Information] ON [Combined Table].[PO Number] = [PO Information].[PO Number]
WHERE ((([Combined Table].[Fault Code]) Like "1*"))
GROUP BY [PO Information].[Week No]; 

3rd and 4th querries are similar.. except that you changed the number from 1 ,2 and 5

My plan right now is to get all these querries and then add all the info into the mshflexgrid one by one.

if you can help me to come wout with some SHAPE function..that will help me a lot.

Rgds


hendra wijaya

[ardneh]

Heva,

I have wrote the program to add all the 4 querries into the mshflexgrid. time is really limited right now, so I dont' think i could afford to wait for your help.

But I really would like to once again thank you for your help.

Rgds



hendra wijaya