VB or SQL for manipulating table?

[Guest]

I am trying to learn the best way to create a new table off of an existing
table based on the data found in each row of that table.

I have a general idea of the logic... I have worked with R-base and MYSQL
but need to do it in Access. I also have a beta of Access 2007.

Here is a run down... My existing table has several columns, two have data I
want to use in my query. One is min the other is max.
I need to set a pointer (in Rbase we called it a Cursor) to start at the
first row and check the min number and the max number. Compute the
difference, divide by two and then LOAD into the new table a copy(s) of the
Row I am pointing to based on "x" number calcuated.
So if the min is 10 and the max is 20, I need to load 5 rows (20-10/2=5)
into my new table.. made up of the data in the pointed (Cursor) row of the
first table and add a unique identifer to each row. Then...

Goto or Point to the next row in the main table and run the process again
untill I run through the entire table.

Wht would be the easiest way to do this in Access... VB or SQL? Or am I way
off base with my thinking?

 

[Stefan Hoffmann]

hi,

I am trying to learn the best way to create a new table off of an existing
table based on the data found in each row of that table.

This would be a table creating query:

SELECT Fieldlist
INTO TableToCreate
FROM ExistingTable
[WHERE Condition]

Here is a run down... My existing table has several columns, two have data I
want to use in my query. One is min the other is max.

I don't understand the intention behind that kind of algorithm...


mfG
--> stefan <--

 

[Guest]

I think you will need to do this in VBA.
Create your new table in design view.

The an approach like this would be the basics:

Dim rstOriginal As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim dbf As DAO.Database
Dim lngNewRecs as Long
Dim lngRecCtr as Long

Set dbf = CurrentDb
Set rstOriginal = dbf.OpenRecordset("ExitstingTable")
Set rstNew = dbf.OpenRecordset("NewTable")

If rstOriginal.RecordCount = 0 Then
MsgBox "No Records To Process"
Exit Sub
End If

With rstOriginal
.MoveLast
.MoveFirst
Do While Not .EOF
lngNewRecs = Int(![Max] - ![Min] /2)
For lngRecCtr = 1 to lngNewRecs
rstNew.AddNew
'
' Here is where you populate the new record from data in your existing record
'
'Example
rstNew![SomeField] = ![FieldFromOriginal]
rstNew.Update
Next lngRecCtr
.MoveNext
Loop
End With

rstOriginal.Close
rstNew.Close
Set rstOriginal = Nothing
Set rstNew = Nothing
Set dbf = Nothing

So if the min is 10 and the max is 20, I need to load 5 rows (20-10/2=5)

 

[kingston via AccessMonster.com]

It looks like you're going to have to do this in code but that doesn't mean
you can't use SQL in your code. I suggest creating a query that returns the
calculated number of new records required for each existing record. Then
cycle through that result in VBA to create and run append queries. BTW, this
will not work "(20-10/2=5)", you'll have to do this (20-10)/2. HTH

 

[Guest]

Either the other posters or I am misunderstanding your requirements. The way
I understand it is you have an existing table (A) with data.
You have another table (B) that currently has not data.
You want to append records to table (B) from data in table (A).
The number records to created in table (B) is based on the values in the
fields Max and Min (bad names, by the way) in table (A).
The number of records to be added to table (B) is calculated as:
The integer value of (tableA!Max - tableAMin) / 2
Here is some ambiquity. Is the integer value or a value rounded to
an integer, the result may be different.

If the above assumptions are correct, then the code I posted is what you
need, with the exception of choosing and/or calculating the data to load into
the newly created records.

 

[Guest]

Thanks Stefan.. here is a little more...
The table is an address list showing valid address ranges for a street.
the Min field is the lowest valid address for the street and the MAX is the
highest.

So if a record for Main Street has a MIN 10 and MAX 20 then I want my new
table to have a row for 10 Main Street, 12 Main Street, 14 Main Street, 16
Main Street, 18 Main Street, 20 Main Street. [ odd numbered address ranges
would be identified in the next row of my first table]

I think I can populate the first new row with the min address and then the
rest using the calculation results described earlier? I don't mind running
two seperate programs on this data because this will not be a reacurring
transaction.

hi,

I am trying to learn the best way to create a new table off of an existing
table based on the data found in each row of that table.

This would be a table creating query:

SELECT Fieldlist
INTO TableToCreate
FROM ExistingTable
[WHERE Condition]

Here is a run down... My existing table has several columns, two have data I
want to use in my query. One is min the other is max.

I don't understand the intention behind that kind of algorithm...


mfG
--> stefan <--

 

[Guest]

Sorry, but Stefan's approach will not work.

Thanks Stefan.. here is a little more...
The table is an address list showing valid address ranges for a street.
the Min field is the lowest valid address for the street and the MAX is the
highest.

So if a record for Main Street has a MIN 10 and MAX 20 then I want my new
table to have a row for 10 Main Street, 12 Main Street, 14 Main Street, 16
Main Street, 18 Main Street, 20 Main Street. [ odd numbered address ranges
would be identified in the next row of my first table]

I think I can populate the first new row with the min address and then the
rest using the calculation results described earlier? I don't mind running
two seperate programs on this data because this will not be a reacurring
transaction.

hi,

I am trying to learn the best way to create a new table off of an existing
table based on the data found in each row of that table.

This would be a table creating query:

SELECT Fieldlist
INTO TableToCreate
FROM ExistingTable
[WHERE Condition]

Here is a run down... My existing table has several columns, two have data I
want to use in my query. One is min the other is max.

I don't understand the intention behind that kind of algorithm...


mfG
--> stefan <--

 

[Guest]

In my last post to Stefan.. I indicated that table (A) has a street name
column, an AddressMin column and an AddressMAX column along with zip code,
carrier route coulmns.

The sets of min and max are either even number sets or odd number sets which
are always divisable by 2. So I don't see a major risk of result math errors.
But I could be wrong.

Now that you know more about Table(A) ... my goal is to populate Table(B)
with all the valid addresses that are represented in table(A) with the
min/max range.

Table(A) is normally used as a lookup table to check valid addresses. I want
to create a detailed table of each valid address.

thanks for your interest in this.

 

[Guest]

I fully understand what it is you are doing. The problem with the SQL query
approach is that it will not create multiple records for (B) for each record
in (A).

The code I posted will. Since I did not know at the time what your data
requirements are, I could not specify what you needed to do. Based on the
new information, the fields from A, excluding the street address field, can
be copied from A to B. If the street number and name are in the same field,
you will need code to strip out the number and replace it with the
incremented number.

One error in your most recent post - No odd number is evenly divisible by 2,
but you will not be dividing numbers to get the next street number, you will
be adding 2. The only division will be determining how many new records to
add to table (B). If you have an odd number, it will come up to something
with .5. For example, lets say Max = 35 and Min = 10. The formula will
result in 12.5. The question then is do you want to add 12 records or 13.
To add 12:
lngNewRecs = Int(![Max] - ![Min] /2)
To add 13:
lngNewRecs = Round(![Max] - ![Min] /2)

 

[Guest]

The streetname has no street number. An actual address will be created in
table(b) later after we have created a street_number entry in table(B) by
using the previously described calc on addressMin and AddressMax.

So shouldn't I be able to append the street name over to table(B) as well as
the other fields?

I failed to mention Rows with even address ranges always have min as an even
number and max as an even number. AND those rows listing the min/max fields
with odd numbers always are odd numbers .. exmpl: min 13 and a max 35. .
Which will result in 22 and of course is divisable by 2.

I fully understand what it is you are doing. The problem with the SQL query
approach is that it will not create multiple records for (B) for each record
in (A).

The code I posted will. Since I did not know at the time what your data
requirements are, I could not specify what you needed to do. Based on the
new information, the fields from A, excluding the street address field, can
be copied from A to B. If the street number and name are in the same field,
you will need code to strip out the number and replace it with the
incremented number.

One error in your most recent post - No odd number is evenly divisible by 2,
but you will not be dividing numbers to get the next street number, you will
be adding 2. The only division will be determining how many new records to
add to table (B). If you have an odd number, it will come up to something
with .5. For example, lets say Max = 35 and Min = 10. The formula will
result in 12.5. The question then is do you want to add 12 records or 13.
To add 12:
lngNewRecs = Int(![Max] - ![Min] /2)
To add 13:
lngNewRecs = Round(![Max] - ![Min] /2)

In my last post to Stefan.. I indicated that table (A) has a street name
column, an AddressMin column and an AddressMAX column along with zip code,
carrier route coulmns.

The sets of min and max are either even number sets or odd number sets which
are always divisable by 2. So I don't see a major risk of result math errors.
But I could be wrong.

Now that you know more about Table(A) ... my goal is to populate Table(B)
with all the valid addresses that are represented in table(A) with the
min/max range.

Table(A) is normally used as a lookup table to check valid addresses. I want
to create a detailed table of each valid address.

thanks for your interest in this.

 

[Guest]

If the street number and street name are different fields, yes. In fact, it
will be much easier than if you had to separate the number from the name,
change the number and put them back together again.

The streetname has no street number. An actual address will be created in
table(b) later after we have created a street_number entry in table(B) by
using the previously described calc on addressMin and AddressMax.

So shouldn't I be able to append the street name over to table(B) as well as
the other fields?

I failed to mention Rows with even address ranges always have min as an even
number and max as an even number. AND those rows listing the min/max fields
with odd numbers always are odd numbers .. exmpl: min 13 and a max 35. .
Which will result in 22 and of course is divisable by 2.

I fully understand what it is you are doing. The problem with the SQL query
approach is that it will not create multiple records for (B) for each record
in (A).

The code I posted will. Since I did not know at the time what your data
requirements are, I could not specify what you needed to do. Based on the
new information, the fields from A, excluding the street address field, can
be copied from A to B. If the street number and name are in the same field,
you will need code to strip out the number and replace it with the
incremented number.

One error in your most recent post - No odd number is evenly divisible by 2,
but you will not be dividing numbers to get the next street number, you will
be adding 2. The only division will be determining how many new records to
add to table (B). If you have an odd number, it will come up to something
with .5. For example, lets say Max = 35 and Min = 10. The formula will
result in 12.5. The question then is do you want to add 12 records or 13.
To add 12:
lngNewRecs = Int(![Max] - ![Min] /2)
To add 13:
lngNewRecs = Round(![Max] - ![Min] /2)

In my last post to Stefan.. I indicated that table (A) has a street name
column, an AddressMin column and an AddressMAX column along with zip code,
carrier route coulmns.

The sets of min and max are either even number sets or odd number sets which
are always divisable by 2. So I don't see a major risk of result math errors.
But I could be wrong.

Now that you know more about Table(A) ... my goal is to populate Table(B)
with all the valid addresses that are represented in table(A) with the
min/max range.

Table(A) is normally used as a lookup table to check valid addresses. I want
to create a detailed table of each valid address.

thanks for your interest in this.


:

Either the other posters or I am misunderstanding your requirements. The way
I understand it is you have an existing table (A) with data.
You have another table (B) that currently has not data.
You want to append records to table (B) from data in table (A).
The number records to created in table (B) is based on the values in the
fields Max and Min (bad names, by the way) in table (A).
The number of records to be added to table (B) is calculated as:
The integer value of (tableA!Max - tableAMin) / 2
Here is some ambiquity. Is the integer value or a value rounded to
an integer, the result may be different.

If the above assumptions are correct, then the code I posted is what you
need, with the exception of choosing and/or calculating the data to load into
the newly created records.

:

I am trying to learn the best way to create a new table off of an existing
table based on the data found in each row of that table.

I have a general idea of the logic... I have worked with R-base and MYSQL
but need to do it in Access. I also have a beta of Access 2007.

Here is a run down... My existing table has several columns, two have data I
want to use in my query. One is min the other is max.
I need to set a pointer (in Rbase we called it a Cursor) to start at the
first row and check the min number and the max number. Compute the
difference, divide by two and then LOAD into the new table a copy(s) of the
Row I am pointing to based on "x" number calcuated.
So if the min is 10 and the max is 20, I need to load 5 rows (20-10/2=5)
into my new table.. made up of the data in the pointed (Cursor) row of the
first table and add a unique identifer to each row. Then...

Goto or Point to the next row in the main table and run the process again
untill I run through the entire table.

Wht would be the easiest way to do this in Access... VB or SQL? Or am I way
off base with my thinking?

 

[Guest]

Thanks for your help..

I guess I'll have to bone up on VB.

All the best

If the street number and street name are different fields, yes. In fact, it
will be much easier than if you had to separate the number from the name,
change the number and put them back together again.

The streetname has no street number. An actual address will be created in
table(b) later after we have created a street_number entry in table(B) by
using the previously described calc on addressMin and AddressMax.

So shouldn't I be able to append the street name over to table(B) as well as
the other fields?

I failed to mention Rows with even address ranges always have min as an even
number and max as an even number. AND those rows listing the min/max fields
with odd numbers always are odd numbers .. exmpl: min 13 and a max 35. .
Which will result in 22 and of course is divisable by 2.

I fully understand what it is you are doing. The problem with the SQL query
approach is that it will not create multiple records for (B) for each record
in (A).

The code I posted will. Since I did not know at the time what your data
requirements are, I could not specify what you needed to do. Based on the
new information, the fields from A, excluding the street address field, can
be copied from A to B. If the street number and name are in the same field,
you will need code to strip out the number and replace it with the
incremented number.

One error in your most recent post - No odd number is evenly divisible by 2,
but you will not be dividing numbers to get the next street number, you will
be adding 2. The only division will be determining how many new records to
add to table (B). If you have an odd number, it will come up to something
with .5. For example, lets say Max = 35 and Min = 10. The formula will
result in 12.5. The question then is do you want to add 12 records or 13.
To add 12:
lngNewRecs = Int(![Max] - ![Min] /2)
To add 13:
lngNewRecs = Round(![Max] - ![Min] /2)

:

In my last post to Stefan.. I indicated that table (A) has a street name
column, an AddressMin column and an AddressMAX column along with zip code,
carrier route coulmns.

The sets of min and max are either even number sets or odd number sets which
are always divisable by 2. So I don't see a major risk of result math errors.
But I could be wrong.

Now that you know more about Table(A) ... my goal is to populate Table(B)
with all the valid addresses that are represented in table(A) with the
min/max range.

Table(A) is normally used as a lookup table to check valid addresses. I want
to create a detailed table of each valid address.

thanks for your interest in this.


:

Either the other posters or I am misunderstanding your requirements. The way
I understand it is you have an existing table (A) with data.
You have another table (B) that currently has not data.
You want to append records to table (B) from data in table (A).
The number records to created in table (B) is based on the values in the
fields Max and Min (bad names, by the way) in table (A).
The number of records to be added to table (B) is calculated as:
The integer value of (tableA!Max - tableAMin) / 2
Here is some ambiquity. Is the integer value or a value rounded to
an integer, the result may be different.

If the above assumptions are correct, then the code I posted is what you
need, with the exception of choosing and/or calculating the data to load into
the newly created records.

:

I am trying to learn the best way to create a new table off of an existing
table based on the data found in each row of that table.

I have a general idea of the logic... I have worked with R-base and MYSQL
but need to do it in Access. I also have a beta of Access 2007.

Here is a run down... My existing table has several columns, two have data I
want to use in my query. One is min the other is max.
I need to set a pointer (in Rbase we called it a Cursor) to start at the
first row and check the min number and the max number. Compute the
difference, divide by two and then LOAD into the new table a copy(s) of the
Row I am pointing to based on "x" number calcuated.
So if the min is 10 and the max is 20, I need to load 5 rows (20-10/2=5)
into my new table.. made up of the data in the pointed (Cursor) row of the
first table and add a unique identifer to each row. Then...

Goto or Point to the next row in the main table and run the process again
untill I run through the entire table.

Wht would be the easiest way to do this in Access... VB or SQL? Or am I way
off base with my thinking?

 

[Guest]

The code I posted should be pretty close. It is untested air code, but if
you copy it and paste it into a module, you can start debugging it. Post
back if you need help. If you do, please include the error number, error
desctription, and point out the line where the error occured.

Thanks for your help..

I guess I'll have to bone up on VB.

All the best

If the street number and street name are different fields, yes. In fact, it
will be much easier than if you had to separate the number from the name,
change the number and put them back together again.

The streetname has no street number. An actual address will be created in
table(b) later after we have created a street_number entry in table(B) by
using the previously described calc on addressMin and AddressMax.

So shouldn't I be able to append the street name over to table(B) as well as
the other fields?

I failed to mention Rows with even address ranges always have min as an even
number and max as an even number. AND those rows listing the min/max fields
with odd numbers always are odd numbers .. exmpl: min 13 and a max 35. .
Which will result in 22 and of course is divisable by 2.

:

I fully understand what it is you are doing. The problem with the SQL query
approach is that it will not create multiple records for (B) for each record
in (A).

The code I posted will. Since I did not know at the time what your data
requirements are, I could not specify what you needed to do. Based on the
new information, the fields from A, excluding the street address field, can
be copied from A to B. If the street number and name are in the same field,
you will need code to strip out the number and replace it with the
incremented number.

One error in your most recent post - No odd number is evenly divisible by 2,
but you will not be dividing numbers to get the next street number, you will
be adding 2. The only division will be determining how many new records to
add to table (B). If you have an odd number, it will come up to something
with .5. For example, lets say Max = 35 and Min = 10. The formula will
result in 12.5. The question then is do you want to add 12 records or 13.
To add 12:
lngNewRecs = Int(![Max] - ![Min] /2)
To add 13:
lngNewRecs = Round(![Max] - ![Min] /2)

:

In my last post to Stefan.. I indicated that table (A) has a street name
column, an AddressMin column and an AddressMAX column along with zip code,
carrier route coulmns.

The sets of min and max are either even number sets or odd number sets which
are always divisable by 2. So I don't see a major risk of result math errors.
But I could be wrong.

Now that you know more about Table(A) ... my goal is to populate Table(B)
with all the valid addresses that are represented in table(A) with the
min/max range.

Table(A) is normally used as a lookup table to check valid addresses. I want
to create a detailed table of each valid address.

thanks for your interest in this.


:

Either the other posters or I am misunderstanding your requirements. The way
I understand it is you have an existing table (A) with data.
You have another table (B) that currently has not data.
You want to append records to table (B) from data in table (A).
The number records to created in table (B) is based on the values in the
fields Max and Min (bad names, by the way) in table (A).
The number of records to be added to table (B) is calculated as:
The integer value of (tableA!Max - tableAMin) / 2
Here is some ambiquity. Is the integer value or a value rounded to
an integer, the result may be different.

If the above assumptions are correct, then the code I posted is what you
need, with the exception of choosing and/or calculating the data to load into
the newly created records.

:

I am trying to learn the best way to create a new table off of an existing
table based on the data found in each row of that table.

I have a general idea of the logic... I have worked with R-base and MYSQL
but need to do it in Access. I also have a beta of Access 2007.

Here is a run down... My existing table has several columns, two have data I
want to use in my query. One is min the other is max.
I need to set a pointer (in Rbase we called it a Cursor) to start at the
first row and check the min number and the max number. Compute the
difference, divide by two and then LOAD into the new table a copy(s) of the
Row I am pointing to based on "x" number calcuated.
So if the min is 10 and the max is 20, I need to load 5 rows (20-10/2=5)
into my new table.. made up of the data in the pointed (Cursor) row of the
first table and add a unique identifer to each row. Then...

Goto or Point to the next row in the main table and run the process again
untill I run through the entire table.

Wht would be the easiest way to do this in Access... VB or SQL? Or am I way
off base with my thinking?