Append query to a specific column

[thedawn]

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 of April I want the data to go into the column for period 4.

All I want to do is get the user to select a column number and then get the append query to append it to this column

I can't seem to apply this to the append to column

What am I doing wrong

Thanks in advance

Rich

 

[CJ_London]

you should not store data in this way - use a crosstab query to view the data

 

[thedawn]

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

 

[CJ_London]

OK - what does your query look like at the moment?

 

[thedawn]

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 months amounts need to append to the relevant column so for April it will be 4 but I have a form that the user will select the period from a combobox so I tried:

INSERT INTO tblYTDHCbyPeriod ( Measure, [Forms]![frmDP]![Combo3].[Text] )
SELECT tbl_Headcount.Measure, tbl_Headcount.Amount
FROM tbl_Headcount;

But that is giving me an error

Thanks Rich

 

[MarkK]

Dates should not be divided into columns by month. This is a normalization problem. A month is a measure of time and should be data in a row.

 

[thedawn]

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

 

[CJ_London]

you can't reference a column in this way, you would need to do it in vba

 dim sqlstr as string

sqlstr="INSERT INTO tblYTDHCbyPeriod ( Measure, " & [Combo3] & ") SELECT tbl_Headcount.Measure, tbl_Headcount.Amount FROM tbl_Headcount"
 currentdb.execute sqlstr

in the after update event of combo3 - note also the text property is only available when the control has the focus

 

[thedawn]

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

 

[MarkK]

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.

A measure of time is a critical part of the math you are trying to do. My point is that different quantities of time should NOT be distinguished by separate columns in a database table, rather, all your time measures should be in a single field (column).

Consider an accounting system. You do not have separate columns for transaction amounts that equal $10.00, or transaction amounts that equal $100.00. "Amount," in an accounting system is an abstract idea that may hold any value, and this value is not stored in separate fields based on quantity. The same should be true of measures of time. Separate columns for separate measures of time is a normalization design flaw that makes your work harder.

 

[thedawn]

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 calculated on an ongoing basis.