Loop and variable number of table

[laurentc via AccessMonster.com]

Hi.

My issue is a little hard to explain, but I will try to.

I have several tables that are based on the same structure (I know that it is
BAD in MS Access to do so, but the amount of data is too huge to avoid this
issue, as far as I know).


So my data tables are like this:

TableA : Date/Price of Product A (and other information)
TableB : Date/Price of Product B (and other information)
and so on.
The table can have as many as 10 000 rows.
I have several thousands of 'data tables' built like this, and my access
database uses some of this huge amount of data depending on the users' needs.


Here is the issue:

- the user click on the tables on which he want to make calculations: this is
done by a listbox which loops the tables names.

- only the tables names selected by the users are in the table: "tblSelected"
There can be as less as 1 name, or as much as 100 -or even more.

Let's say the user ask for the Product A, B, and D.

- I want to build a loop in VBA that will follow the selected table names
and
+ add the "price" of the a, B and D product in another table, for all the
dates available (the dates are not the same on each products)
+ these prices would be "well placed", that is to say that the date of the
prices would be the good ones.
+ the results would be placed in another table, named "TblSelectedPrices"

Do you know i can do this?

Thanks in advance.

Laurent

 

[Allen Browne]

IMHO, you are wasting your time trying to do it this way.

You cannot use a UNION query with 100+ tables, so you are talking about code
that loops through each table to append values to a temp table, and then
using it as your source.

It would be far better to spend the time building a relational design so you
don't have 100+ tables with the same fields. The effort it will take to do
this will pay off in this one task alone, let alone the flexibility and
efficiency you will gain for other tasks too.

 

[laurentc via AccessMonster.com]

Thanks for your opinion Allen.

However, I cannot see how I could change the table structures, as the data
amounts are huge.

Moreover, if I would place all the prices/informaton of each tables in only
one BIG table (or even, only the "price" informations on each table), I would
have a number of rows equal to tens (maybe hundreds) of millions, and my
computer would not be able to do calculations / add filters on such a great
amount of data.

I am also thinking or "changing" the structure of my tables, but I do not
know how I could figure this without for instance:
- adding a tool which would clean the database
- importing each time EXACTLY the data I need
- doing calculations on only these data ("only" some millions rows this time)



However, I also think of doing things like this:
1. I could add in a temporary table the following fields:
MyDate/Price of products/TableName

So that I would have for instance:
31/10/2006 / 100 / TableX
01/11/2006 / 102 / TableX
02/11/2006 / 101 / TableX
31/10/2006 / 250 / TableY
01/11/2006 / 260 / TableY
02/11/2006 / 240 / TableY
31/10/2006 / 150 / TableZ
01/11/2006 / 153 / TableZ
02/11/2006 / 156 / TableZ


So this time, i would have a 'well-structured table' with only the data I
need.

2. Then I would have to build a second temporary table with a second "loop"
which would change the data into the format I previously gave:

MyDate / Price of Product X/ Price of product Y / Price of product Z / and so
on

The issues would be that:
- I would have a variable number of fields in this second table (the number
of products selected can be equal to 2 or more than 100) and I do not know
how to get that.

- I would want to do other calculations then, and especially I need to build
a chart based on that data.
When the number of rows is fixed, I can draw all data on a chart without any
issue.
However, I do not know how to show a variable number of data field in a chart
(and is it possible to show 100 lines on a chart?)


Does anyone know how to solve the 3 issues?
1. the first code, to get the first table (I should be able to write most of
the code and will revert to you shortly with it)
2. The second VBA code which would have to manage the variable number of
fields
3. The issue of drawing the chart with the same variable number of fields

Many thanks for your help.
Best regards,

Laurent

IMHO, you are wasting your time trying to do it this way.

You cannot use a UNION query with 100+ tables, so you are talking about code
that loops through each table to append values to a temp table, and then
using it as your source.

It would be far better to spend the time building a relational design so you
don't have 100+ tables with the same fields. The effort it will take to do
this will pay off in this one task alone, let alone the flexibility and
efficiency you will gain for other tasks too.

My issue is a little hard to explain, but I will try to.

[quoted text clipped - 39 lines]

Laurent

 

[Allen Browne]

You have millions of products to price? Are you sure Access is a suitable
database for this project? By the time you add (presumably) millions of
customers who want to place tens or hundreds of millions of orders, each
with many line items, I think you will have problems with stability, file
size, and hundreds or thousands of users trying to enter all that data.

Perhaps someone else can help, but I still don't understand your Q.2. If
there are many products that have the same price, they will be different
records, not multiple fields, so I don't get the "variable number of fields"
bit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your opinion Allen.

However, I cannot see how I could change the table structures, as the data
amounts are huge.

Moreover, if I would place all the prices/informaton of each tables in
only
one BIG table (or even, only the "price" informations on each table), I
would
have a number of rows equal to tens (maybe hundreds) of millions, and my
computer would not be able to do calculations / add filters on such a
great
amount of data.

I am also thinking or "changing" the structure of my tables, but I do not
know how I could figure this without for instance:
- adding a tool which would clean the database
- importing each time EXACTLY the data I need
- doing calculations on only these data ("only" some millions rows this
time)



However, I also think of doing things like this:
1. I could add in a temporary table the following fields:
MyDate/Price of products/TableName

So that I would have for instance:
31/10/2006 / 100 / TableX
01/11/2006 / 102 / TableX
02/11/2006 / 101 / TableX
31/10/2006 / 250 / TableY
01/11/2006 / 260 / TableY
02/11/2006 / 240 / TableY
31/10/2006 / 150 / TableZ
01/11/2006 / 153 / TableZ
02/11/2006 / 156 / TableZ


So this time, i would have a 'well-structured table' with only the data I
need.

2. Then I would have to build a second temporary table with a second
"loop"
which would change the data into the format I previously gave:

MyDate / Price of Product X/ Price of product Y / Price of product Z / and
so
on

The issues would be that:
- I would have a variable number of fields in this second table (the
number
of products selected can be equal to 2 or more than 100) and I do not know
how to get that.

- I would want to do other calculations then, and especially I need to
build
a chart based on that data.
When the number of rows is fixed, I can draw all data on a chart without
any
issue.
However, I do not know how to show a variable number of data field in a
chart
(and is it possible to show 100 lines on a chart?)


Does anyone know how to solve the 3 issues?
1. the first code, to get the first table (I should be able to write most
of
the code and will revert to you shortly with it)
2. The second VBA code which would have to manage the variable number of
fields
3. The issue of drawing the chart with the same variable number of fields

Many thanks for your help.
Best regards,

Laurent

IMHO, you are wasting your time trying to do it this way.

You cannot use a UNION query with 100+ tables, so you are talking about
code
that loops through each table to append values to a temp table, and then
using it as your source.

It would be far better to spend the time building a relational design so
you
don't have 100+ tables with the same fields. The effort it will take to do
this will pay off in this one task alone, let alone the flexibility and
efficiency you will gain for other tasks too.

My issue is a little hard to explain, but I will try to.

[quoted text clipped - 39 lines]

Laurent

 

[laurentc via AccessMonster.com]

Thanks Allen.

The project is not as big as that: there are not millions of customers, just
a few now.
My "products" are the track record of many STOCK prices, and as the data are
computed daily, that lead to a huge amount of data (there are thousands
stocks...)

So the project allow the user to use the stocks he wants and do
statistics/charts/other things on these, that is to say doing things that
other financial software do not propose.


I have added several functions in this project, such as importing the data
from .cvs (of course...), seeing chart of 1 or 2 stocks, and several other
things.
The method used by the the 2 stocks-chart would not be suitable to build a
100-stocks chart, as I used INNER JOIN queries that would not be possible
here (100 INNER JOIN of tables with 10 000 rows could not be handled by
Access, isn't it?)


Now, I would like to be able to see the chart of several of them at a time.

The question 2. is linked to the issue of separating the Price of each
product in a new different field, to help building the chart.

I need to chart the price of the stocks selected by the user an all data
available.
I think I would need to have a table based on the following structure to be
able to draw the stock performances.
The structure of the useful table would be:
Mydate/Price1/Price2/Price3

and the Chart Form would call a query which would pick the data in this table,
allowing the user to see what he wants.

Do you think of any other way to build a graph of the stock prices without
doing things like that?
If yes, please tell me.

Thanks for your answers.

Laurent

You have millions of products to price? Are you sure Access is a suitable
database for this project? By the time you add (presumably) millions of
customers who want to place tens or hundreds of millions of orders, each
with many line items, I think you will have problems with stability, file
size, and hundreds or thousands of users trying to enter all that data.

Perhaps someone else can help, but I still don't understand your Q.2. If
there are many products that have the same price, they will be different
records, not multiple fields, so I don't get the "variable number of fields"
bit.

Thanks for your opinion Allen.

[quoted text clipped - 90 lines]

 

[laurentc via AccessMonster.com]

Adding note:

Allen, just to try be clearer:
- agreed, in my 'clean table', I would have several rows per day, and many
rows per product, such as this:
MyDate / Price / ProductName
25/10/2006 10 xxx
26/10/2006 11 xxx
27/10/2006 12 xxx
28/10/2006 14 xxx
29/10/2006 15 xxx
30/10/2006 14 xxx
31/10/2006 11 xxx

Then the rows dedicated to the prodcut YYY
25/10/2006 15 YYY
26/10/2006 16 YYY
27/10/2006 15 YYY
28/10/2006 14 YYY
29/10/2006 12 YYY
30/10/2006 11 YYY
31/10/2006 10 YYY

Then the product ZZZ:
25/10/2006 200 ZZZ
26/10/2006 210 ZZZ
27/10/2006 230 ZZZ
28/10/2006 220 ZZZ
29/10/2006 250 ZZZ
30/10/2006 270 ZZZ
31/10/2006 290 ZZZ


That is the reason why I would need to separate these datas so that i would
get a table like this one:

MyDate / Product1 / Product2 / Product3/ ...
25/10/2006 10 15 200
26/10/2006 11 16 210
27/10/2006 12 15 230
28/10/2006 14 14 220
29/10/2006 15 12 250
30/10/2006 14 11 270
31/10/2006 11 10 290

And then I would chart these data...

I do not know the VBA code to "change" the first table into the second one,
as the second one is drawable on a chart, but I do not think the first one is
"drawable" also.

Is my issue clearer?
Has anyone had an idea?

Regards,

Laurent

Thanks Allen.

The project is not as big as that: there are not millions of customers, just
a few now.
My "products" are the track record of many STOCK prices, and as the data are
computed daily, that lead to a huge amount of data (there are thousands
stocks...)

So the project allow the user to use the stocks he wants and do
statistics/charts/other things on these, that is to say doing things that
other financial software do not propose.

I have added several functions in this project, such as importing the data
from .cvs (of course...), seeing chart of 1 or 2 stocks, and several other
things.
The method used by the the 2 stocks-chart would not be suitable to build a
100-stocks chart, as I used INNER JOIN queries that would not be possible
here (100 INNER JOIN of tables with 10 000 rows could not be handled by
Access, isn't it?)

Now, I would like to be able to see the chart of several of them at a time.

The question 2. is linked to the issue of separating the Price of each
product in a new different field, to help building the chart.

I need to chart the price of the stocks selected by the user an all data
available.
I think I would need to have a table based on the following structure to be
able to draw the stock performances.
The structure of the useful table would be:
Mydate/Price1/Price2/Price3

and the Chart Form would call a query which would pick the data in this table,
allowing the user to see what he wants.

Do you think of any other way to build a graph of the stock prices without
doing things like that?
If yes, please tell me.

Thanks for your answers.

Laurent

You have millions of products to price? Are you sure Access is a suitable
database for this project? By the time you add (presumably) millions of

[quoted text clipped - 12 lines]

 

[Allen Browne]

Those 3 fields look right for a combined table.

In your example the xxx repeats on every row, so I still don't see why it
needs to be a separate table, i.e. the ProductName field adequately
distinguishes the values. In a normalized design this field would be a
foreign key to a table that contains the valid product names (so this table
would have 100+ records.) You would create a relation with referntial
integrity, so Access would automatically index this foreign key. The result
would be as efficient (perhaps moreso) than separate tables.

You could then create a crosstab query to give you the result you want:
- MyDate as Row Heading;
- ProductName as Column Heading;
- Price as Value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Adding note:

Allen, just to try be clearer:
- agreed, in my 'clean table', I would have several rows per day, and
many
rows per product, such as this:
MyDate / Price / ProductName
25/10/2006 10 xxx
26/10/2006 11 xxx
27/10/2006 12 xxx
28/10/2006 14 xxx
29/10/2006 15 xxx
30/10/2006 14 xxx
31/10/2006 11 xxx

Then the rows dedicated to the prodcut YYY
25/10/2006 15 YYY
26/10/2006 16 YYY
27/10/2006 15 YYY
28/10/2006 14 YYY
29/10/2006 12 YYY
30/10/2006 11 YYY
31/10/2006 10 YYY

Then the product ZZZ:
25/10/2006 200 ZZZ
26/10/2006 210 ZZZ
27/10/2006 230 ZZZ
28/10/2006 220 ZZZ
29/10/2006 250 ZZZ
30/10/2006 270 ZZZ
31/10/2006 290 ZZZ


That is the reason why I would need to separate these datas so that i
would
get a table like this one:

MyDate / Product1 / Product2 / Product3/ ...
25/10/2006 10 15 200
26/10/2006 11 16 210
27/10/2006 12 15 230
28/10/2006 14 14 220
29/10/2006 15 12 250
30/10/2006 14 11 270
31/10/2006 11 10 290

And then I would chart these data...

I do not know the VBA code to "change" the first table into the second
one,
as the second one is drawable on a chart, but I do not think the first one
is
"drawable" also.

Is my issue clearer?
Has anyone had an idea?

Regards,

Laurent

Thanks Allen.

The project is not as big as that: there are not millions of customers,
just
a few now.
My "products" are the track record of many STOCK prices, and as the data
are
computed daily, that lead to a huge amount of data (there are thousands
stocks...)

So the project allow the user to use the stocks he wants and do
statistics/charts/other things on these, that is to say doing things that
other financial software do not propose.

I have added several functions in this project, such as importing the data
from .cvs (of course...), seeing chart of 1 or 2 stocks, and several other
things.
The method used by the the 2 stocks-chart would not be suitable to build a
100-stocks chart, as I used INNER JOIN queries that would not be possible
here (100 INNER JOIN of tables with 10 000 rows could not be handled by
Access, isn't it?)

Now, I would like to be able to see the chart of several of them at a
time.

The question 2. is linked to the issue of separating the Price of each
product in a new different field, to help building the chart.

I need to chart the price of the stocks selected by the user an all data
available.
I think I would need to have a table based on the following structure to
be
able to draw the stock performances.
The structure of the useful table would be:
Mydate/Price1/Price2/Price3

and the Chart Form would call a query which would pick the data in this
table,
allowing the user to see what he wants.

Do you think of any other way to build a graph of the stock prices without
doing things like that?
If yes, please tell me.

Thanks for your answers.

Laurent

You have millions of products to price? Are you sure Access is a suitable
database for this project? By the time you add (presumably) millions of

[quoted text clipped - 12 lines]

Laurent

 

[laurentc via AccessMonster.com]

Allen, thanks for your new reply.

Someone else just gave me the solution to get the first "good table".
Now I have a temporary table in which I have ONLY the stock prices I want, in
ONE table, like that:
MyDate / Price / ProductName
25/10/2006 10 xxx
26/10/2006 11 xxx
27/10/2006 12 xxx
28/10/2006 14 xxx
29/10/2006 15 xxx
30/10/2006 14 xxx
31/10/2006 11 xxx
25/10/2006 15 YYY
26/10/2006 16 YYY
27/10/2006 15 YYY
28/10/2006 14 YYY
29/10/2006 12 YYY
30/10/2006 11 YYY
31/10/2006 10 YYY
25/10/2006 200 ZZZ
26/10/2006 210 ZZZ
27/10/2006 230 ZZZ
28/10/2006 220 ZZZ
29/10/2006 250 ZZZ
30/10/2006 270 ZZZ
31/10/2006 290 ZZZ

Now, the end remains the same.
I have to "convert" this table into another table in which I would have 1
field per product, so that i would be able to:
- build the graph of the selected stocks
- do other statistics on these data

Do you know the VBA code that would allow me to "convert" the first table
into the second one?

Thanks a lot.

Those 3 fields look right for a combined table.

In your example the xxx repeats on every row, so I still don't see why it
needs to be a separate table, i.e. the ProductName field adequately
distinguishes the values. In a normalized design this field would be a
foreign key to a table that contains the valid product names (so this table
would have 100+ records.) You would create a relation with referntial
integrity, so Access would automatically index this foreign key. The result
would be as efficient (perhaps moreso) than separate tables.

You could then create a crosstab query to give you the result you want:
- MyDate as Row Heading;
- ProductName as Column Heading;
- Price as Value.

Adding note:

[quoted text clipped - 110 lines]

 

[laurentc via AccessMonster.com]

Allen and others,

Of course a cross-query was the good solution!
It is working now.

Thank you for your help and suggestions.

Best regards,

Laurent

Allen, thanks for your new reply.

Someone else just gave me the solution to get the first "good table".
Now I have a temporary table in which I have ONLY the stock prices I want, in
ONE table, like that:
MyDate / Price / ProductName
25/10/2006 10 xxx
26/10/2006 11 xxx
27/10/2006 12 xxx
28/10/2006 14 xxx
29/10/2006 15 xxx
30/10/2006 14 xxx
31/10/2006 11 xxx
25/10/2006 15 YYY
26/10/2006 16 YYY
27/10/2006 15 YYY
28/10/2006 14 YYY
29/10/2006 12 YYY
30/10/2006 11 YYY
31/10/2006 10 YYY
25/10/2006 200 ZZZ
26/10/2006 210 ZZZ
27/10/2006 230 ZZZ
28/10/2006 220 ZZZ
29/10/2006 250 ZZZ
30/10/2006 270 ZZZ
31/10/2006 290 ZZZ

Now, the end remains the same.
I have to "convert" this table into another table in which I would have 1
field per product, so that i would be able to:
- build the graph of the selected stocks
- do other statistics on these data

Do you know the VBA code that would allow me to "convert" the first table
into the second one?

Thanks a lot.

Those 3 fields look right for a combined table.

[quoted text clipped - 16 lines]