Solved Handling data from a spreadsheet (1 Viewer)

[bob fitz]

In my app I have a table called "tblHistoricalDataMASTER" that is derived from a spreadsheet. It has data about the price of shares e.g.

ID	Open	High	Low	Close	Ticker	StockDate2
1	42.049999	45	38	38.23	FB	5/18/12

I have written a function that steps through this table and writes the values to another table called "tblHistoricalDataMASTERTrans" that looks like this:

HistID	Ticker	StockDate	PriceType	StockValue	MasterID
1	FB	5/18/12	Open	42.049999	1
2	FB	5/18/12	High	45	1
3	FB	5/18/12	Low	38	1
4	FB	5/15/12	Close	38.23	1

The function does the job but looping through 200k records and creating 800k new records takes longer than I would like it to.

Can anyone suggest a quicker/better way of achieving this. Perhaps with some kind of query?

 

[MajP]

I would think you could do that in a xtab.

 

[NauticalGent]

Good morning Bob,

I do know that Access LOVES batch queries and that Appened and Update queries run a LOT faster then looping procedures.

 

[bob fitz]

Good morning Bob,

I do know that Access LOVERS batch queries and that Appened and Update queries run a LOT faster then looping procedures.

Hi NauticalGent,

Thanks for your interest. Yes, I've read many times that queries are faster than looping through recordsets with code I'm just looking for some help/guidance with creating the required query

 

[bob fitz]

I would think you could do that in a xtab.

Thanks for your interest. What is an xtab?

 

[NauticalGent]

If it's as straightforward as it sounds, it should be as simple as linking to the spreadsheet and then doing the Append and/or updates queries. Am I misunderstanding your question?

 

[MajP]

TRANSFORM First(tblStock.[StockValue]) AS FirstOfStockValue
SELECT tblStock.[MasterID], tblStock.[Ticker], tblStock.[StockDate], First(tblStock.[StockValue]) AS [Total Of StockValue]
FROM tblStock
GROUP BY tblStock.[MasterID], tblStock.[Ticker], tblStock.[StockDate]
PIVOT tblStock.[PriceType];

tblStock_Crosstab tblStock_Crosstab

MasterID	Ticker	StockDate	Total Of StockValue	Close	High	Low	Open
1​	FB	5/18/2012​	38​	38.23​	45​	38​	42.049999​

 

[bob fitz]

@MajP I am having problems understanding the SQL statement offered.

I have attached a copy of my db with just a few records to keep its' size small.

Would you please create the query and then post it back here.

 

[MajP]

I am not very good at cross tabs, but there is a wizard to do this. You pick the rows, pick the columns, and what to pivot. Select the query wizard, that is the way I usually do it.

 

[Pat Hartman]

Bob, if you're starting with all the columns in a single row, the fastest solution is 4 append queries. One for each of the prices. What I don't understand is why you are turning them into four rows. The data as it comes in is properly normalized.

 

[MajP]

I read this backward. I thought it started with the 4 records and wanted 1 record.

 

[CJ_London]

you can do this with one append query

create a new table called say tblColumns
tblColumns

PK	ColName
1​	Open
2​	High
3​	Low
4​	Close

now create a cartesian append query

INSERT INTO tblHistoricalDataMASTERtrans ( Ticker, StockDate, PriceType, StockValue, MasterID )
SELECT Ticker, StockDate2, ColName, Choose([PK],[open],[high],[low],[close]), ID
FROM tblHistoricalDataMASTER, tblColumns

 

[arnelgp]

make a Union Query (qryUnion) and then Create an Append Query (qryAppend).

 

[bob fitz]

you can do this with one append query

create a new table called say tblColumns
tblColumns

PK	ColName
1​	Open
2​	High
3​	Low
4​	Close

now create a cartesian append query

INSERT INTO tblHistoricalDataMASTERtrans ( Ticker, StockDate, PriceType, StockValue, MasterID )
SELECT Ticker, StockDate2, ColName, Choose([PK],[open],[high],[low],[close]), ID
FROM tblHistoricalDataMASTER, tblColumns

Thank you Chris. This works perfectly and gives me exactly what I need.

 

[bob fitz]

make a Union Query (qryUnion) and then Create an Append Query (qryAppend).

Thanks for your interest arnelgp. Your proposed solution works perfectly.

 

[NauticalGent]

Choose()...never heard of it. Learn something new everyday

 

[bob fitz]

Bob, if you're starting with all the columns in a single row, the fastest solution is 4 append queries. One for each of the prices.

Thanks for your interest Pat. I'll look into your suggestion, although Arne and Chris have both offered very acceptable solutions.

What I don't understand is why you are turning them into four rows. The data as it comes in is properly normalized.

The app that I'm working on needs to search through each days' stock value prices and find the date that a particular price is met.
I thought it would be better to search a single column rather than four columns.

 

[bob fitz]

I am not very good at cross tabs, but there is a wizard to do this. You pick the rows, pick the columns, and what to pivot. Select the query wizard, that is the way I usually do it.

Thank you for your interest. I have used crosstab queries on a few occasions but only using the wizard. Oh I love that wizard

 

[NauticalGent]

Curious Bob, how is the speed?

 

[bob fitz]

Choose()...never heard of it. Learn something new everyday

Thanks for your interest NauticalGent. I've never heard of it either but I've just "googled" it. It seems that it is an Excel function.
I don't use Excel and although that's where this data is coming from I'd like to deal with it in Access.