PeterBaileyUk
asked on
access query to sql server
I had this query in access which gave output specifically only where an item went from no value to a value or either value changed
AbiCode minYM maxYM Tprev Tchange
81140561 2015-08 2015-08 A
54682560 2015-08 2015-08 A M
the data returned is cut down by the query so that where a data change occurred in either tprev or tchange where both have data or only where a null went to a value are given. the only major difference in the sql server table is that fieldname is added in this case would contain transmission instead of the column.
in access each component this was transmission had its own table now the data is stored in one sqlserver table TblCompareEvents which contains all data changes.
PK_ID ClientName ClientCode Change ChangeYearMonth Prev
48536197201702GROUP_5013 Abi 48536197 13 201702 NULL
ActualVariance VehicleCategory Matched Actioned FieldName
NULL C NULL NULL GROUP_50
not sure how to achieve a similar query as I dont know how to achieve the first and last Ive sketched out so far:
SELECT v.AbiCode, Min(v.ChangeYearMonth) AS minYM, Max(v.ChangeYearMonth) AS maxYM, First(v.transmissionPrev) AS Tprev, Last(v.transmissionChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, transmissionPrev, transmissionChange FROM Tbltransmission) AS v
GROUP BY v.AbiCode
HAVING (((Nz(First([v].[transmissionPrev]),""))<>Last([v].[transmissionChange])));
AbiCode minYM maxYM Tprev Tchange
81140561 2015-08 2015-08 A
54682560 2015-08 2015-08 A M
the data returned is cut down by the query so that where a data change occurred in either tprev or tchange where both have data or only where a null went to a value are given. the only major difference in the sql server table is that fieldname is added in this case would contain transmission instead of the column.
in access each component this was transmission had its own table now the data is stored in one sqlserver table TblCompareEvents which contains all data changes.
PK_ID ClientName ClientCode Change ChangeYearMonth Prev
48536197201702GROUP_5013 Abi 48536197 13 201702 NULL
ActualVariance VehicleCategory Matched Actioned FieldName
NULL C NULL NULL GROUP_50
not sure how to achieve a similar query as I dont know how to achieve the first and last Ive sketched out so far:
select v.clientcode, min(changeyearmonth)AS minYM, MAX(ChangeYearMonth) AS maxYM, v.FieldName, v.Prev, v.Change
FROM (SELECT DISTINCT ClientCode, ChangeYearMonth, Prev, Change, FieldName FROM [dbo].[TblCompareEvents]) AS v
GROUP BY clientCode, FieldName,Prev,Change
HAVING FieldName='Transmission'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you
ASKER
Open in new window