calculate value, hold, then update, then based on that result run next

[tractorvix]

Ok, so I appreciate that my header isn't that great, but what I'm trying to achieve is quite difficult to explain succinctly!

I have a table called tbl-balances, which has it's first column (Jan 08) populated. I then need to use this number to calculate the next column worth of data (Feb 08), which I've done as a make table query with the following:
SELECT [qry-VarFlowDetail - 3].ProdXrefID, [tbl-Balances]![Jan 08]+[qry-VarFlowDetail - 3]![Feb 08]+[qry-IntCred - 4]![Jan 08] AS Feb INTO [tbl-hold]
FROM [tbl-Balances] INNER JOIN ([qry-VarFlowDetail - 3] INNER JOIN [qry-IntCred - 4] ON [qry-VarFlowDetail - 3].ProdXrefID = [qry-IntCred - 4].ProdXrefID) ON [tbl-Balances].ProdXrefID = [qry-IntCred - 4].ProdXrefID;

This tbl-hold is then used to populate the next column in the tbl-balances:
UPDATE [tbl-Balances] INNER JOIN [tbl-hold] ON [tbl-Balances].ProdXrefID = [tbl-hold].ProdXrefID SET [tbl-Balances].[Feb 08] = [tbl-hold]![Feb];

I then need to use this data to calculate the next column (Mar 08), again hold the data in tbl-hold, then update the next column etc.

I'm sure there must be a way of looping through this code without me having to create lots of make table and update table queries, but I've not really used the SQL side of things before and certainly never created a module.

Any help would be much appreciated.

Thanks
Vicky

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

It appears that today is the day for un-normalized tables to make an appearance.

A fundamental principle of relational design (as PHV's link will attest) is that a field should be dependent on the Key, the whole Key, and nothing but the Key.

You are building internal non-key dependancies into your table and that is why the process is complex and destined to become more so as time passes.

 

[tractorvix]

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 there is something that I can use to concatenate the productID from the left column and the date from the top row which I could then use to populate a normalised table so basically take:

Prod ID Jan Feb Mar
A 10 11 12
B -3 0 3

into
ProdIDDate Value
AJan 10
AFeb 11
AMar 12
BJan -3
BFeb 0
BMar 3

Would anyone be able to help me with this? Do I need to perform in Excel, or can I create in Access?

Thanks

Vicky

 

[PHV]

You may use a normalization union query:
SELECT [Prod ID], 'Jan' AS theMonth, Jan AS Value FROM yourTable WHERE Jan Is Not Null
UNION SELECT [Prod ID], 'Feb', Feb FROM yourTable WHERE Feb Is Not Null
UNION ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tractorvix]

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 alternative post) which compiles ok and I've set up in a module, but I don't know what I need to do with it! I've only ever called queries from a button on a form and never used a function, could I ask for some further guidance?

Function MyData()
Dim rs As DAO.Recordset
Dim x As Integer
Dim sql As String

Set rs = CurrentDb.OpenRecordset("tbl-balances")

If rs.EOF And rs.BOF Then
Exit Function
End If

Do Until rs.EOF
For x = 3 To 4

sql = "INSERT INTO tblNew(ProdID, Date, Value)" & _
"VALUES(" & rs(0) & "," & rs(1) & ",'" & rs(x).Name & _
"','" & rs(x) & "');"
DoCmd.RunSQL sql

Next x
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function


Does this look right? How do I go about using it?

Thanks again,

Vicky