multiple queries into a crosstab query in VB 
Author Message
 multiple queries into a crosstab query in VB

For some reason this post has been 'lost' from the news group so I am
reposting it. Please try and answer the question asked, and not
provide architectual answers or opinions to something that I did not
ask about!

I have the following 4 queries in A97 that I would like to be able to
do in VB6 using ADO

This is the first query, it is called 'setup Grand total per day'
SELECT DWN.Dte, WSTN.Description, Sum(DWN.Duration) AS GrandTotalTime
FROM (DWN INNER JOIN WSTN ON DWN.WstnID = WSTN.WstnID) INNER JOIN
Major ON (DWN.MajID = Major.MajID) AND (DWN.WstnID = Major.WstnID)
GROUP BY DWN.Dte, WSTN.Description
ORDER BY DWN.Dte, WSTN.Description;

This is the secod query, it is called 'setup total Production per
day'
SELECT DWN.Dte, WSTN.Description, Sum(DWN.Duration) AS
TotalProducingTime
FROM (DWN INNER JOIN WSTN ON DWN.WstnID = WSTN.WstnID) INNER JOIN
Major ON (DWN.MajID = Major.MajID) AND (DWN.WstnID = Major.WstnID)
GROUP BY DWN.Dte, WSTN.Description, Major.Descr
HAVING (((Major.Descr)="PRODUCING"))
ORDER BY DWN.Dte, WSTN.Description;

The above 2 queries get linked together to form the third query, it is
called 'Setup Crosstab Downtime by day by workstation'
SELECT [setup Grand total per day].Dte, [setup Grand total per
day].Description, [GrandTotalTime]-[TotalProducingTime] AS DownTime
FROM [setup Grand total per day] INNER JOIN [setup total Production
per day] ON ([setup Grand total per day].Dte = [setup total
Production per day].Dte) AND ([setup Grand total per day].Description
= [setup total Production per day].Description);

the results of the third query are processed in the fourth query, a
crosstab, called 'Crosstab_Downtime_by_day_by_workstation'
TRANSFORM Sum([Setup Crosstab Downtime by day by
workstation].DownTime) AS [The Value]
SELECT [Setup Crosstab Downtime by day by workstation].Dte
FROM [Setup Crosstab Downtime by day by workstation]
GROUP BY [Setup Crosstab Downtime by day by workstation].Dte
PIVOT [Setup Crosstab Downtime by day by workstation].Description;

To get the above to work in VB6 thus far I have done the following
' adoWaiver is the adodc control
adoWaiver.ConnectionString = conStringOFIR
adoWaiver.RecordSource = "Crosstab_Downtime_by_day_by_workstation"
adoWaiver.Refresh

Set TDBGrid1.DataSource = adoWaiver
TDBGrid1.Refresh
TDBGrid1.ReBind
TDBGrid1.Refresh

This all works fine by as you can see, the query that I am calling,
query 4 above actually resides in the A97 database. I do not wish to
have it in the database at all as I want to code it up in pure VB. Can
somesome please show me how I could code up the above 4 queries into 1
superquery so that I can get the results of the crosstab into my True
Db Grid control.

I kno it can be done using nested queries, but am yet to see an
example of something this complex.

PS
That dates are comming back in American format m/dd/yy, which play
havoc when trying to load the results up into excel under Australian
format 'dd/mm/yyyy'. I need to get the results into excel as
'dd/mm/yyyy'. Does anyone know how to fix this ?

Thanks
Stu-man



Sun, 16 Oct 2005 08:35:01 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. multiple queries into a crosstab query in VB

2. how can I build 2 queries in 1 query(VB SQL query)

3. how to create a crosstab query in VB

4. Howto execute one query over multiple query results

5. Howto execute one query over multiple query results

6. VBA for crosstab query

7. Opening a CrossTab query via QueryDef.OpenRecordset

8. Crosstab Query

9. Sort or limit row displayed in a crosstab query

10. create a table from a crosstab parameter query in VBA

11. QueryDefs Fields Collection on a CrossTab Query

12. report based on a crosstab query

 

 
Powered by phpBB® Forum Software