Last from Access to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Last from Access to SQL

Author

Topic

Mrka
Starting Member

5 Posts

Posted - 2006-06-01 : 13:37:20

Hi, I am trying to do access query in SQL and not getting the same results as in Access for Last()...
here is my query in Access:
SELECT DISTINCT Itm_forecast_cur.Brc, "TOTAL" AS [Super Family], Sum(Itm_forecast_cur.OhHand) AS SumOfOhHand,
Sum(Itm_forecast_cur.Usage6) AS SumOfUsage6, Sum(Itm_forecast_cur.Usage12) AS SumOfUsage12, Sum(Itm_forecast_cur.SchdIn) AS SumOfSchdIn,
Last(t_Oper_Branch.Operating_ID_Nbr) AS LastOfOperating_ID_Nbr, Last(t_Operating_ID.Operating_ID_Name) AS LastOfOperating_ID_Name,
Sum([Usage12]*[Average Cost]) AS [Usage12$], Sum([Usage6]*[Average Cost]) AS [Usage6$],
Sum([ExpOut]*[DIS_ALL]![LB Factor])/2000 AS ExpectOutTons, Sum([PreReceipt]*[DIS_ALL]![LB Factor])/2000 AS PreRcptTons,
Last(t_Branch_Table.DAPBranchRVP) AS LastOfDAPBranchRVP, Last(t_Branch_Table.DAPBranchRegMgr) AS LastOfDAPBranchRegMgr,
Last(t_Branch_Table.DAPBranchName) AS LastOfDAPBranchName, Sum([OhHand]*[Average Cost]) AS [TotOnHand$WOP],
First(t_Branch_Table.DAPCurrency) AS FirstOfDAPCurrency, First(t_Branch_Table.DAPCurrencyType) AS FirstOfDAPCurrencyType
FROM (((Itm_forecast_cur LEFT JOIN DIS_ALL ON (Itm_forecast_cur.Brc = DIS_ALL.Branch) AND (Itm_forecast_cur.[Item#] = DIS_ALL.Item))
LEFT JOIN t_Oper_Branch ON (DIS_ALL.Branch = t_Oper_Branch.BranchNbr) AND (DIS_ALL.[Super Family] = t_Oper_Branch.CommDesc)) LEFT JOIN t_Operating_ID
ON t_Oper_Branch.Operating_ID_Nbr = t_Operating_ID.Operating_ID_Nbr) LEFT JOIN t_Branch_Table
ON t_Oper_Branch.BranchNbr = t_Branch_Table.DAPBranchNbr
GROUP BY Itm_forecast_cur.Brc
ORDER BY Itm_forecast_cur.Brc

And here is in SQL:

SELECT DISTINCT Itm_forecast_cur.Brc, 'TOTAL' AS [Super Family], Sum(Itm_forecast_cur.OhHand) AS SumOfOhHand,
Sum(Itm_forecast_cur.Usage6) AS SumOfUsage6, Sum(Itm_forecast_cur.Usage12) AS SumOfUsage12, Sum(Itm_forecast_cur.SchdIn) AS SumOfSchdIn,
min(t_Oper_Branch.Operating_ID_Nbr) AS LastOfOperating_ID_Nbr, min(t_Operating_ID.Operating_ID_Name) AS LastOfOperating_ID_Name,
Sum([Usage12]*[Average Cost]) AS [Usage12$], Sum([Usage6]*[Average Cost]) AS [Usage6$],
Sum([ExpOut]*[DIS_ALL].[LB Factor])/2000 AS ExpectOutTons, Sum([PreReceipt]*[DIS_ALL].[LB Factor])/2000 AS PreRcptTons,
min(t_Branch_Table.DAPBranchRVP) AS LastOfDAPBranchRVP, min(t_Branch_Table.DAPBranchRegMgr) AS LastOfDAPBranchRegMgr,
min(t_Branch_Table.DAPBranchName) AS LastOfDAPBranchName, Sum([OhHand]*[Average Cost]) AS [TotOnHand$WOP],
min(t_Branch_Table.DAPCurrency) AS FirstOfDAPCurrency, min(t_Branch_Table.DAPCurrencyType) AS FirstOfDAPCurrencyType
FROM (((Itm_forecast_cur LEFT JOIN DIS_ALL ON (Itm_forecast_cur.Brc = DIS_ALL.Branch) AND (Itm_forecast_cur.[Item#] = DIS_ALL.Item)) LEFT JOIN t_Oper_Branch
ON (DIS_ALL.Branch = t_Oper_Branch.BranchNbr) AND (DIS_ALL.[Super Family] = t_Oper_Branch.CommDesc)) LEFT JOIN t_Operating_ID
ON t_Oper_Branch.Operating_ID_Nbr = t_Operating_ID.Operating_ID_Nbr) LEFT JOIN t_Branch_Table ON t_Oper_Branch.BranchNbr = t_Branch_Table.DAPBranchNbr
GROUP BY Itm_forecast_cur.Brc
ORDER BY Itm_forecast_cur.Brc

min(t_Oper_Branch.Operating_ID_Nbr) AS LastOfOperating_ID_Nbr and min(t_Operating_ID.Operating_ID_Name) AS LastOfOperating_ID_Name are not working correctly others are OK...how can I change my query to work for all the same way as Access? Can anybody help please, any help is greatly appreciated.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 15:01:44

Just a guess:

The data type may be different than u think
(eg. u think of numeric, real data is varchar)

Srinika

Mrka
Starting Member

5 Posts

Posted - 2006-06-01 : 15:38:51

Might be, but it is the same data type in Access to, and it is giving different results...

Is there any way I can make it to work the same as in Access?
Thanks,
Mrka

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-01 : 23:58:39

You can get it the same, but it might take some tweaking.

Pls post your table designs, sample data and expected results.

Any pls don't cross-post.....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-02 : 03:33:06

http://sqlteam.com/forums/topic.asp?TOPIC_ID=67100

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources