Search results

Gothca, I'll step out of this one.

Blimey, this thread has been going all this time and I never once took part in it? I'm frankly amazed that 16% say that they're atheist and not even conclusive proof of a divine being would change their mind. I'm atheist simply because the best evidence points to there not being a divine...

Spotted some kippers in one of the markets today and thought of this posts but didn't have the phone to hand to take a photo. Never heard of canned Kippers, but who knows they might be ok :) To add the general gist that's been posted so far... Very much a Breakfast dish and these days perhaps...

Can you elaborate? WITH FULLSCAN suggests updating of table statistics?

If you don't know how to install SQL Server have you considered using Access? No server maintenance required. With all due respect, SQL Server is not a zero maintenance system, express or not, and there are significant overheads to getting a SQL server project up and running compared to using...

You could: Use linked tables to the worksheets and have a query per linked table to perform the update. Use linked tables, but rather than have 5 explicit queries build the SQL in code to perform the update. Import all the data, do the updates and spit it back out again. Write masses of...

To further vbaInet and your own comment. Don't delete the query, modify it and the code to work properly: PARAMETERS [@startdate] DateTime, [@Enddate] DateTime [@chargecode] datatype; SELECT tbl_CompletedScans.ChargeCode , tbl_CompletedScans.CheckOutDate ...

I r teh simple me thinks :)

Long day of being challenged unnecessarily around making perfectly reasonable suggestions elsewhere and it spilled into here. My apologies.

As an introduction though it's fine :) Never too old to learn. To sign for Liverpool aged 50? Probably. To learn Access? God no.

It really shouldn't, you're adding a month to the prior date so that it matches the month that you're about to manipulate and gives you the value necessary to perform the calculation. I.e. if you're about to calculate March you need February's calculated data so we need to add a month to...

Ignoring the date issue for now, your SQLis not sorting your data at all so the rather un-necessary bit with the recordset (use queries, that's what they're there for) is going to the last record in your results, not necessarily the last record in the table. Do not assume an order of the data...

Well, I basically agree with Rainlover. If there is a need for some other candidate data within that table to be functionally unique, and in my experience it tends to be a compound series of columns, then I constrain it with a unique index. I have no problem with doing this nor see any real...

Having mulled it over some more and depending on circumstance, this may be a situation where it is actually preferable to hold the calculated value stored. The reason why everyone leaps in and says Don't hold a calculated value in a table (based on other values on that row) is that there is a...

I agree. It may well be worth trying the union query approach. It's clunky but it's a one off effort to write the query but I suspect it will introduce a significant performance penalty.

You can use an iif in the transform, unfortunately I'm typing on a phone at the moment so won't attempt it now. The +1 you added will cater for zero values but not NULL. If NULL shouldn't be present constrain it on the column by setting required = yes

If you create a query, switch to SQL view and paste that sql, save it called "initial_crosstab" and create that second query in the same way, you can call that one whatever you like. Moving forwards it should be possible to combine them into a single query, I say should as Access can be a bit...

Well, conceptually, it's relatively straightforward to achieve in a pivot. I just imported the spreadsheet as is, created a basic pivot: TRANSFORM SUM(1 + tbltest.num / tbltest.den) AS SumOfPart SELECT tbltest.name ,YEAR(tabletest.[Date]) AS ryear FROM tbltest GROUP BY tbltest.name...

Another vote for doing this in a query and not writing the calculated values back to a table.

It's messy but you can do something using a UNION query, you'll need to use the SQL editor in Access as the query designer doesn't do Unions. SELECT transactionNo ,Product1Price as ProductPrice ,Product1Type as ProductType FROM sales UNION ALL SELECT transactionNo ,Product2Price as...