Based on the amazing help I have received, I'm coming back with three more questions!
BTW I do google these things first and try to intuit the answers, but there really is not that much assistive materials on the internet for T-SQL.
- How do I - or CAN I, use COUNT in T-SQL? Here is the code I am having trouble with:
WHERE ((([dbo].[tbl_2017_CRD_Trips].Destination)=1002) AND (([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null)) OR ((([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null) AND (([dbo].[tbl_2017_CRD_Trips].TripPurpose)=10 Or ([dbo].[tbl_2017_CRD_Trips].TripPurpose)=999))
GROUP BY [dbo].[tbl_2017_CRD_Trips].HouseholdID, [dbo].[tbl_2017_CRD_Trips].PersonID, Now()
HAVING (((Count([dbo].[tbl_2017_CRD_Trips].TripID))>20 Or (Count([dbo].[tbl_2017_CRD_Trips].TripID))>20))
ORDER BY [dbo].[tbl_2017_CRD_Trips].HouseholdID, [dbo].[tbl_2017_CRD_Trips].PersonID;
Relating to this same query, how do I use a HAVING function properly in T-SQL, it seems to throw up its hands at this - also, I am not sure if the GROUP BY is functional in T-SQL since the MS SQL Server Management Studio provides very ambiguous error messages 'Msg 195, Level 15, State 10, Line 8' may not be Line 8... If anyone can answer the HAVING question that would be great! If anyone can also answer the GROUP BY that would be amazing!
Relating to VBA, how is this used in T-SQL via the MS SQL Server Management Studio (or any other)- I do not see how I can place the needed function into the database as it is not a table I can add nor a query I can create....Here, the VBA function is the GetMiles
WHERE (((GetMiles([tbl_2017_CRD_Trips]![OSrchLat],[tbl_2017_CRD_Trips]![OSrchLon],[tbl_2017_CRD_Trips]![DestLat],[tbl_2017_CRD_Trips]![DestLon]))>200) AND ((GetMiles([tbl_2017_CRD_Trips]![OSrchLat],[tbl_2017_CRD_Trips]![OSrchLon],[tbl_2017_CRD_Trips]![DestLat],[tbl_2017_CRD_Trips]![DestLon])*1000)>"200000") AND ((tbl_2017_CRD_Trips.OSrchLat) Is Not Null) AND ((tbl_2017_CRD_Trips.DestLat) Is Not Null) AND ((tbl_2017_CRD_Trips.FlagForRemoval) Is Null) AND ((tbl_2017_CRD_Person.FlagforRemoval) Is Null) AND ((tbl_2017_CRD_Household.FlagForRemoval) Is Null));
Thank you so much, and yes I am studying T-SQL via tutorialspoint but even this great site has sparse info on this subject.
Even if you can only tackle on of my questions it would be appreciated (there is a pack of 20 coders waiting for a database so they can have work-hours, so you are helping more than just a confused nuube analyst!
I just realized a 4th major question! How can I use one table twice in a query in T-SQL.
e.g. I have a table "Trips" which is all the trips a person made, one column in this is "NextTripID" and often I have to link the NextTripID (TripID is the key in this table) to TripID in a second Trips table to do a validation such as "two trips to Home in a row". In SQL its just tbl_1, but if that table does not exist I do not expect T-SQL would know what I am requesting. An example join in SQL would be as below:
SELECT tbl_2016_TTS_Trips.TripID, tbl_2016_TTS_Trips_1.TripID
FROM tbl_2016_TTS_Trips INNER JOIN tbl_2016_TTS_Trips AS tbl_2016_TTS_Trips_1 ON tbl_2016_TTS_Trips.NextTripID = tbl_2016_TTS_Trips_1.TripID;
I know I am asking alot, even a hint in the right direction would be greatly appreciated!