Recent content by tractorvix

Hi all, I hope you'll be able to help me. I've got a data set that is added to each day and I need to dynamically be able to calculate an average over a specific time period eg: 11/05/2009 10.0 12/05/2009 10.0 13/05/2009 10.0 14/05/2009 10.0 16/05/2009 10.0 18/05/2009 5.0 19/05/2009 5.0...

Hey Y'all! I've set up a sub as below: Private Sub UpdateQRMMaster(Updatedate As Date) DoCmd.RunSQL "INSERT INTO QRM_MASTER ( Product, UpdateDate, Mat_Date, Bal ) " & _ "SELECT temp.Product, " & Updatedate & " AS UpdateDate, temp.Mat_Date, temp.Bal " & _ "FROM temp;" End Sub Variable...

Hi all, I'm having to use linked excel tables as the inputs for my database and am then trying to normalise the data so that I can perform the necessary calculations etc. It wasn't too bad when it was all stored on my local machine, but now it's on the server, it's soooooo slow (around 30...

I've managed to normalise most of my tables, but there is on that is causing me problems! It kind of contains 2 set of data and looks like: 01/01/08 01/02/08 etc base 5 5.5 Product...

I've set up a seperate table to show the two dates matched up and have then been able to set up the following query: SELECT tblNBaseRate.Scenario, tblNBaseRate_1.DateVal, [tblNBaseRate_1]![BaseRate]-[tblNBaseRate]![BaseRate] AS BRVariance FROM tblNBaseRate AS tblNBaseRate_1 INNER JOIN...

Hi All, I'm sure this has a really simple answer, but my brain just doesn't seem to be in gear today! I have normalised my data to show: Prod Date Value A 1/1/8 4.5 A 1/2/8 5 A 1/3/8 5 B 1/1/8 6 B 1/2/8 5.75 B 1.3.8 5.5 What I need to do is...

Remou, absolute genius mate. Thanks Vicky

Hi All, I'm still trying to get my data normalisation to work correctly! history is I'm using excel linked files that are set up as: prod id Jan 08 Feb 08 Mar 08 etc A 10 20 30 B 10 20 30 which I'm converting to: Prod ID Date Value A...

Hi all, I'm sure I've not dreamt this, but I thought that if you linked a table from MS Excel to MS Access and you opened the linked table in Access, providing you had exclusive use of the table you could amend and update it. I've tried opening one of my linked tables and it won't let me...

Thanks for the replies chaps. My sql code is now working, but the final field does not seem to be pulling through. Please note I've amended the field name VALUE to ProdRate. Function MyData() Dim rs As DAO.Recordset Dim x As Integer Dim sql As String Set rs =...

I'm trying to normalise a dataset that is currently linked in excel. I have created the following which I think should work based on research that I've carried out on here Function MyData() Dim rs As DAO.Recordset Dim x As Integer Dim sql As String Set rs =...

I suspected it would be something ridiculous simple that I was missing. Thanks for your help guys. Vicky

Hi all, I'm sure I'm missing something really simple here, but it's absolutely foxing me. I'm trying to pass a couple of values to a function to give me the option of looping. It works fine if I pass one string using the following: Private Function SendMsg(msg As String) MsgBox msg End...

Hi PHV, thanks for the reply. Unfortunately the data is a forecasting tool and I'd be looking at being able to forecast for at least the next 5 years (so 60 union queries!) I found some code which looks like it might perform what I need (actually I think you offered it as a solution to an...

Hi chaps, I appreciate that the design of the database is not ideal, however, the main issue is generated by having to use excel linked tables for data input. So maybe I need to start by addressing how I can convert an excel sheet with a crosstab layout into a normalised database. Presumably...