Search results

There are no criteria I'm applying which should reduce the final number of records, thats why I don't understand the count not matching. All I'm doing is splitting into two sets which form the whole, and on many occasions, works perfectly, but in a few others it doesn't, so there must be some...

I'm going to see if I can better explain exactly what it is I need to do. I'm being given files with a large number of records in them. Some of these records may be exact duplicates of each other, while a great many or not exact duplicates but have the same Premise Name, Address Line 1 and...

Ok, I have tried SELECT DISTINCT Q.* FROM (SELECT InputTable.[Address Source], InputTable.[External Unique ID], InputTable.URN, InputTable.[Premises Name (Full)], InputTable.[Address 1], InputTable.[Address 2], InputTable.[Address 3], InputTable.City, InputTable.County, InputTable.Postcode...

I believe I tried SELECT DISTINCT Q.* FROM (Select ..... Union All Select ....) AS Q; before I used my current method, and it was providing more records than were contained in the original file, suggesting that it wasn't working correctly, tho I will try again making sure...

The reason for the unions is because I have to do three seperate joins of the duplicate records back to the full record set, one for each field, to account for posssible nulls in any of the fields. Any record where all three fields is null is being dealt with seperately, and thats being taken...

I have been using aggregation queries to seperate data into those where there is a count of more than one for that particular combination of premise name, address line 1 and postcode, and those where there is only one. Here is the code for these queries: SELECT [InputTable].[Premises Name...

sorry I meant to mention that I have actually tried that as well, it still tells me that I haven't used MEDIAN(DecPrice) in the aggregate function

Hi, I am trying to write a query which aggregates data from a table by year, month and postcode, then produces info:average price of houses sold that year, min price, max price and median price. I've got functions for all of these, but the Median function I'm using is causing problems. If I...

Thanks very much, it works perfectly now. :)

After a bit of fiddling around I've got the query running and providing only the numbers present in the string. :) Or blank if there are no numbers. Unfortunately I am now having a problem whereby Records which contained nothing in that particular field are returning an #Error Message, as far...

Ok, I've been playing around with the query, and think I've managed to create a module and run it using the commented line at the top of the code you gave me, which has run properly. Unfortunately it's not doing what I need it to do, I need to view only the numbers which are present in the...

Thanks for the reply, it sounds like something that should be useful, unfortunately I've only worked with SQL in Access before and never used functions or modules before. I've looked about online but nothing is particularly clear about exactly how they work. Could you possibly explain to me...

Hi, I'm not very experienced with SQL at all and am attempting to create a query which will allow me to select all numeric characters from a specific field of every record in a table, regardless of how many numeric characters there are, or the order they are in. The Field being examined is an...

Thanks, that sounds helpful. I don't know when I'll get a chance to try it out, but when I can I will, and will let you know how it goes. :) Thanks again.:)

Sorry, I'll try to explain better. The table which I'm working with and which I provided an example of contains three columns, homepingcode, workpingcode and mobileping code, for each record. Each of these columns contains a code representing the result of trying to ping a customer's three...

I've attached a sample of the data I've got to work with for you to have a look at.

Hi there, I'm still very new to Access and am struggling to do something I'm sure must be possible. I have been given a table containing three columns, 'homephonetestcode' 'mobilephonetestcode' and 'workphonetestcode'. Each of these columns contains one of a fairly limited number of values. My...

Thanks, I've managed to get the count doing something similar, and have created some bar charts which look like they're what I want, though they're not particularly neat. Think I'm managing ok for now, but I may need to post more in this thread as I discover other graphing problems. :P Ta for...

Hello, I am trying to graph differences in dates between when two different companies both last verified their version of data on an address, in terms of which is the most recent. I have used the date difference function in order to determine the difference in days between the two dates for...

Thanks, that works great. :)