Recent content by IanH

Thanks for this - I may give it a miss, and suggest that the team use reusable code and amend for each database as needed. Cheers Ian

Afternoon all, We want to create a central database for common stored procedures (call it SP_DB). Assuming we are working on tables in Database_A does anyone have any simple thoughts on how to call stored procedures from SP_DB so as they run on the tables in the working database. At the...

Ignore last - should have done a search on previous Cheers Ian

... anyone know how to insert these? I'd like to put a couple of small animated gifs in one of my forms. Importing them as a picture doesn't give the animation (it's a static picture). Any thoughts on a simple solution would be appreciated Cheers Ian

That's quite simple. If you add the Year function into both the query and sub-query and use this when joining the two (just like we did with the month value) you get the following for the weekly report: SELECT Year(T.Date_field) AS Year_Value, T.Date_field, T.WeekNo, T.Demand FROM UserTable AS...

A simple group / count query should suffice SELECT Product, SUM(QTY) AS SumQuantity FROM UserTable GROUP BY Product; Regards Ian

One option for this is to use something called a correlated sub-query: bascially you use the results of one query (the sub-query) to filter out the results for your main query. For the weekly query: In essence what you do is select the date where the demand was highest for each week and then...

He probably doesn't have the necessary libraries installed / available. On your PC open up the database and go into one of the modules. On the menu click on TOOLS ... REFERENCES and have a look to see which ones you have got a check mark against. Then do the same on your colleagues PC and see...

Perhaps you are using an more up to date version of Access than I - as a poor public sector worker, we only have Office 2000 :-( This was one of those functions that certainly didn't work previously, so may only be flawless if used on a later version. On the other hand, the VBA code should work...

As I recall, REPLACE is a SQL and VBA function that cannot be used directly in a query. There are a few ways you can use it though. One simple option is to create your own function in a VBA module as below ... Function UDF_Replace(Orig_String As String, Find_Str As String, Replacement_Str As...

Apologies, I misunderstood slightly, but nevertheless you can still build on the query I gave you, using it as a subquery. This would look like ... SELECT Table2.* FROM Table2 INNER JOIN (SELECT Address, MAX(PrimaryKey) AS MaxPrimaryKey FROM Table2 GROUP BY Address) AS SubQry1 ON...

It depends on which version of the record you wish to pull out. I would suggest that you might want the last / latest one that was added. In that case a simple SELECT Address, MAX(PrimaryKey) FROM Table2 GROUP BY Address would give you each unique version of the address field with the...

You can also do this as a single query, using a more intricate join criteria (either in the 'FROM' or as a 'WHERE' clause ... SELECT Table1.PartNumber, Sum(IIF(RIGHT(Table2.PartNumber,1) <> "W", Table2.OrderQuantity, 0)) AS OrderQty, Sum(IIF(RIGHT(Table2.PartNumber,1) = "W"...

This does look OK - it gives you the standard commission for each rep and multiplies this by the amount paid for the accounts table. I have re-read what I wrote and am not surprised it still confused you - (I'm more shocked by my appalling typos,but can only say I was a little in a bit of a...

Dear Jersey, Scenario 1: SELECT firstname, lastname, billingaddress, phonenumber FROM DataTable WHERE billingaddress like "*eureka*" Alternatively you could create a parameter query, so that when you run the query it asks you for an input, like this: SELECT firstname, lastname...