1. multiple queries into a crosstab query in VB
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.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.
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
2. Deleting DAO Database Records
3. DATA WAREHOUSE @ SYBASE CORP, MOST CITIES
4. Fake a crosstab query
5. Test A: drive for disk
6. Can I use crosstab query in ADP?
7. Crosstab Query?
8. Crosstab Query
9. crosstab query
10. Crosstab Query Question -- Thanks
11. Crosstab Queries
by