append cross tab query

[El-d]

Hi All,

I'm new to this, so apologies if I'm asking silly questions, but I've looked and can't see a solution.


Setup is:- I import a daily csv , query (filter)the relevant columns into a temp table.

Headings: Date_actual, Company ,xxx,xxx,xxx,Price
data is : actual date, company 1-30, xxx,xxx,xxx, number value for each company

I've created a query

TRANSFORM First(Imported_Data.Price) AS FirstOfPrice
SELECT Imported_Data.Date_Actual
FROM Imported_Data
GROUP BY Imported_Data.Date_Actual
PIVOT Imported_Data.Company;

This creates a datasheet as I want
date_actual, company1, company 2 etc
2/1/2012,price for company 1, price for company 2 etc

I wish to append this to Table2 so that I end up with a history of Prices per company over a year.
ie
2/1/2012,price for company 1, price for company 2 ..... comp30
3/1/2012,price for company 1, price for company 2 ..... comp30
4/1/2012,price for company 1, price for company 2 ..... comp30
5/1/2012,price for company 1, price for company 2 ..... comp30

My Table 2 is setup with ID, Date_actual, Comp1, comp2..... comp30
Whilst my query works on its own, as soon as I try to append it , It changes my query to

INSERT INTO History ( Date_Actual )
SELECT Imported_Data.Date_Actual
FROM Imported_Data
GROUP BY Imported_Data.Date_Actual;

and doesn't work. i've tried merging the 2 but get syntax errors... ..always hated syntax issues, I know what I want but a missing comma/bracket thwarts me .

:banghead:

I'm sure its something simple ..been a long day and probably shouldn't have had the 2nd glass of wine before deciding to look at this but I'm grabbing moments when I can .

Hope it makes sense and thanks in advance
El-d

 

[JHB]

No, it's not even so simple the way you want to do it!
Will it always be 30 company and will the same company always at the same place?
Why do you want to import the data into TABLE2, why are not you just keeping the data as they are, and run a cross-table query at the end of the year?
Or is it that you are trying to do using this query?

INSERT INTO History ( Date_Actual )
SELECT Imported_Data.Date_Actual
FROM Imported_Data
GROUP BY Imported_Data.Date_Actual;

It's a little difficult to determine in one place you say:
"I wish to append this to TABLE2 .... "
But in the query you show you are calling the table="History" and you are only inserting data into one field!
Can you clarified that.

 

[El-d]

At least its not so simple... makes me feel better

The initial imported data consists of 3000+ records of which my query extracts the 30 prices for company. There's a lot of redundant data in the 3000+ records and so I wanted to filter down to a table that gives the company price for a set date.

The number of companies may change so the query needs to tie in the price to company correctly. New companies would add data from the date of insertion and have a new column heading added to the History table.

But in the query you show you are calling the table="History" and you are only inserting data into one field!
Can you clarified that.

Ok... so

INSERT INTO History ( Date_Actual )

should be

INSERT INTO History ( Date_Actual , comp1, comp2,comp3....comp30)

Is there not an append row? I know that the query will return the same format / columns every time..... or am I just thinking in spreadsheet mode ?

Thanks

El-d

 

[JHB]

Forget History table, run the cross-table query when you need to look at the data.
And yes - you are thinking too much in the spreadsheet method.

 

[El-d]

I guess I was just trying to reduce the excess data. I've been working with test files initially - had a look at the proper file and it would import 8000+ records per day (86 companies).

I figured by querying the relevant info into a table then I would only ever have 250ish (working days) rows of 86 columns plus a daily temp file of 8000 records and a nice compact data set rather than 2 million + records to query at the end of the year.

I suppose in the grand scheme of things, 2mill. records isn't that many. It just appears to be unnecessary to me and my spreadsheet mind

El-d

 

[JHB]

Where does the 8000+ come from, which data contains they, (isn't it 86 companies, it should give 86 rows)?
Maybe it is here you should look at which data you store!

 

[El-d]

Each company has values assigned to it. Can be up to 80 records that have the same date but differing info. For my history requirement, the price data is replicated across the 80 records. Not ideal, which is why I was looking to clean out this part.
What I've actually done is Make a temp table from my crosstab query and query append this to my history Table. I then delete my temp table.

Probably not the cleanest method but it does append my data as expected.
Thanks for the help.

El-d