Search results

Hi There I have now solved this. I took the count off this query and left it as an unmatched query. I then created another query and left joined to the original EMPLOYEE DETAILS table and used the single output field from the original query as the destination field in the new query and counted...

Sorry for updating this but I am just wondering if I have asked the question incorrectly - what I want to do if possible is detect whether this query returns zero results even in another query. The reason I ask is that when I try any test on this query I get a blank records. I have tried IsNull...

Yes that's correct - I have got myself that confused now that I just can't work out what is the way around this

Sorry - it was just an initial question. The query is as follows and sometimes it will return records but sometimes it will have no records SELECT Count([EMPLOYEE DETAILS].[People No]) AS [CountOfPeople No] FROM [EMPLOYEE DETAILS] LEFT JOIN [People attended allergen] ON [EMPLOYEE...

Hi There I currently use an unmatched query in access that counts the number of records that meet the unmatched criteria. Sometimes this query will return no records. I need to use the results of this query in a following query that totals two queries together (the one mentioned above and...

Hi There I am using append queries to populate a results table using various imported spreadsheets. Some months there are two spreadsheets that could be blank they being Leavers and Starters. If these are blank I would like the append query to add a default record with 2 columns. The first...

Just so you both know I actually ended up taking CJ London's advice and used a crosstab query and the world is a better place today I was just struggling to get my head around it in the first place but as I add months to the table that the crosstab query is based on the Averages I need are...

CJ thanks As I said I am quite rusty as I have been away from Access for a while Your help is much appreciated Rich

Hi Mark There are no dates involved it is just figures relating to a date in the year and I need to calculate averages on a YTD basis. Thanks Rich

Hi CJ Its just a standard append query but I need to get the amount to append to a column called '4' as its the april figures etc So the Monthly table has two columns, Measure and Amount. the Measure column doesn't need to append as there are standard measures in the table - it is just that...

Hi CJ Thanks for that I am not trying to view the data I will be using the different months to calculate a YTD average and using the 12 seperate columns in a table seemed to be the easiest way to do this So as I am not displaying them what would be the best way to achieve this ? Thaks Rich

Hi there Hopefully someone can help - I am sure this is fairly basic but I am a bit rusty because not used Access for a while and just getting back into it I have a table that collates 12 months worth of data and each month I want to add the current data to the latest field i.e. at the end...

Rain Thanks for even looking at it - if I could get that SQL correct I can then move on and complete the outputs I do think it is not being able to put quotation marks around the NOCOSS part of the SQL - if I could do that I think it may work I am working on trying to sort it myself also...

Hi Rain The spreadsheet in question will always be the same and the output will always be the same Thanks Rich

Hi Rain I think I may have confused you - the two subroutines that I included in my latest post were the ones that are actually working. If I explain the process I am trying to automate from start to finish and provide the initial data maybe that may help. I am receiving data from an external...

Rain I import a spreadsheet and then use a query to populate my main table using the column names of that spreadsheet. I then copy a template query however many times dependant on the number of columns that are imported using the following code: Private Sub CopyQueries_Click() Dim db As...

Rain I am importing a spreadsheet that has dates in around 200 columns I need to create initial queries from this and then i then need to create queries from those queries into a specific format It is to make data conversion easier and automatic Cheers Rich

Rain I do think the problem in the SQL is to get the NOCOSS with quotes round it If you look at the SQL for the original Query and then the one that is generated the original has "PTS AC" and the generated one is NOCOSS without quotes and I can't seem to get the quotes around the NOCOSS or...

Rain If you look at Paul's post from above I have added the extra lines in regarding tmpSQL so that I could debug the line as it posts directly to the immediate window If the SQL worked it would amend each querydef in order and save them. I have done exactly the same for another set of...

Rainlover I am changing queries but i am using a table of values to change those queries I have copied a query using the same table so have created say 20 queries and then I want to amend thos queries using the same table Cheers Rich