Search results

Small world. The data has been obfuscated and made up. Sorry to the folks in CA :-) Churistchurch who ;-)

Thanks, I tried that but the results are a bit odd. The percent column if full of 1's or 0's?

Duh! My bad. I meant 1/2 = 0.5. Denom = 2.

Hi, Sacremento has two records in September but only one is answered yes, so the calculation would be 2/1 = 50%. Denominator therefore is 1. It the percentage of records for a given month and county that are answered yes. Hope that makes sense?

Hi All, I have a query which I need to refine. Currently it calculates a percentage across all records in the results. It actually needs to calculate percentage based on total volume by month AND area. SaleYN is discrete and has possible values 'Yes', 'No' and '' *** Mas TABLE *** DateStart...

Hi All, I have this query for counting number of records per week: SELECT DatePart("ww", [DateStart]) AS weekno, count(*) FROM MAS WHERE DateStart Between #11/01/2017# AND #01/30/2018# GROUP By DatePart("ww", [DateStart]); This works great and the results look like: WEEKNO COUNT 49...

Dont worry - it works nicely with round when I put the brackets in the right place.

Just gave it a go - seems to work, but the results are often expressed as : 6.02743646498E-02 etc Is there a way to control the formatting here to a few decimal places? perhaps INT ( COUNT(PathWay)/(SELECT COUNT(PathWay) FROM NTH) AS PathwayPercent ) or ROUND ( COUNT(PathWay)/(SELECT...

Hi All, I have a query which gives me a count of each value for Pathway SELECT NTH.Pathway, Count(NTH.Pathway) AS [Count] FROM NTH WHERE DateStart Between #01/07/2017# AND #10/11/2017# GROUP BY NTH.Pathway; This works nicely. I am wonderng if there is a way to add a colum which calculates...

Sorted - thanks again.

Windy Welly lol. I am now trying to get a bit more tricky with the query: SELECT MAS.Pathway, Count(*) As CountPathway, format([MAS.DateStart], "mmm yyyy") As MonthYear FROM MAS GROUP BY MAS.PAthway, MonthYear; However I am not getting a count per month. I am getting: Pathway Count...

That worked perfectly - thanks.

Yeah I want both.

Duh. That helped. Can I also return from datepart month and year?

Hi Guys, I am trying to use DatePart("mm", [MAS.DateStart]) in : SELECT DatePart("mm", [MAS.StartDate]) As Month FROM MAS; Some results are blank, which is fine, but the majority are #Func! The DateStart column is set to data type date. I am in NZ so we use dd/mm/yyyy. Any ideas? Cheers AL

Perfect. Thank you.

What you have done looks like exactly what I want - I assume its based on a table mapping city to state called CityState?

Hello, I want to create a query to sum by state - the data doesn't have state, it has city (in this data there is only a few cities per state). So I presume I need a table to Map city to state? The data looks like: TABLE MAS: ID | COLOR | CITY | 0 | Blue | San Deigo | 1 | Blue...

Hi, Good point - let me clarify - I am not attempting to tackle validation yet - there is already a lot of data in the database which I need to deal with. So the question is how to create this calculated field for each record in a result set. The logic iteself looks like: 1. Find all records...

Hi All, I have a query which gets a list of records from a table. Each record will, among other things, contain the details of a person and in most cases this will include a Social Security type number (SSN) for the format [0-9]{6-12} (thats all numeric length 6-12). I need to have a...