want to create new records from an existing record 2

[joeclueless2]

Hello,

First off, I am rusty as the tin man.

What I would like to do is add new records to a new table. These new records will be almost identical to existing records in another table. Each existing record that would be duplicated will hold all the values of the original, the challenge is to create one new record for each interval of 2 in an existing range that is represented by a [low] field and a [high] field in the original record. The record id would also be replaced with a new id.

The example only shows the concept, but there are many more fields in the data.

example:

In record: 100 198 Main St

Out records: 100 Main St
102 Main St
104 Main St
106 Main St
108 Main St
110 Main St
112 Main St
114 Main St
etc...
194 Main St
196 Main St
198 Main St

I appreciate any help

Thanks,

Joe

 

[Golom]

Before we start on a solution, can you clarify a bit?

In [blue]100 198 Main St[/blue], are "100", "198", "Main St" in one field, two fields or three fields on the incoming record?

Alternatively, are "100 Main St" and "198 Main St" in different records?

You state that "... there are many more fields ...". Does that mean that there are many more fields to which you want this additive process to be applied at the same time or just that there are many more fields that you want to add to the output table.

Finally you want to "... add new records to a new table ..." means, I gather, that you want a MAKE TABLE query and not an APPEND query. Is that correct?

 

[joeclueless2]

Golom,

My apologies for the lacking clarity in the original post, and I thank you for the response.

Here is a sample of a dozen or so records that contains the relevant field headings:

--------------------------------------------------------------------------------
| OBJECTID_1 |BEG_HSE|END_HSE|PREFIX| NAME | SUFFIX |SUFFIX_DIR| ZIP |
--------------------------------------------------------------------------------
| 1632 | 100 | 498 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 3623 | 100 | 899 | E | ADAMS | BL | | 90011 |
--------------------------------------------------------------------------------
| 31510 | 400 | 899 | E | 29TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 31509 | 400 | 899 | E | 28TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 31508 | 400 | 899 | E | 27TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 31512 | 601 | 699 | E | 30TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 31514 | 2500 | 2699 | | MAPLE | AV | | 90011 |
--------------------------------------------------------------------------------
| 27366 | 2500 | 2999 | | TRINITY | ST | | 90011 |
--------------------------------------------------------------------------------
| 26088 | 2500 | 2999 | | STANFORD | AV | | 90011 |
--------------------------------------------------------------------------------
| 24615 | 2500 | 2999 | S | SAN PEDRO | ST | | 90011 |
--------------------------------------------------------------------------------
| 18650 | 2500 | 2598 | S | MAIN | ST | | 90007 |
--------------------------------------------------------------------------------
| 14063 | 2501 | 2999 | | GRIFFITH | AV | | 90011 |
--------------------------------------------------------------------------------

The output for the first record would be something like:

--------------------------------------------------------------------------------
| OBJECTID_1 | ID | HSE |PREFIX| NAME | SUFFIX |SUFFIX_DIR| ZIP |
--------------------------------------------------------------------------------
| 1632 | 1 | 100 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 2 | 102 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 3 | 104 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 4 | 106 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 5 | 108 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 6 | 110 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 7 | 112 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 8 | 114 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
| 1632 | 9 | 116 | E | 25TH | ST | | 90011 |
--------------------------------------------------------------------------------
Etc... Up to 498


And the "many more fields" that I mention are additional fields that would remain unchanged and be carried over to the output table.

I hope this clarifies the objective

-Joe

 

[Golom]

OK. First build a table called Integers with one field called Num (Integer) and populate it the digits from 0 to 9.

Then

SELECT M.OBJECTID_1, (BEG_HSE + (I1.Num*100+I2.Num*10+I3.Num)) AS HSE, 
       [blue]... Other Fields ...[/blue] INTO NewTable

From myTable As M, Integers As I1, Integers As I2, Integers As I3

WHERE BEG_HSE + (I1.Num*100+I2.Num*10+I3.Num) <= M.End_Hse
  AND (I1.Num*100+I2.Num*10+I3.Num) Mod 2 = 0

That will allow you to fill in up to 1,000 numbers. Note that this is a cross or cartesian join so you will be creating 1000 * the number of records in your input table although some of them will be eliminated by the where clause. Because of this the query may take a while if your input table is large.

I have assumed that you want it to increment by 2 in all cases so that if the first number is even then you will get even numbers and if it is odd then you will get odd numbers.

 

[Golom]

I just noticed that some of your records start with an even number but end with an odd number (e.g 2500 - 2699). The above will miss the last value because of the "increment by 2" constraint. Change it to

SELECT M.OBJECTID_1, (BEG_HSE + (I1.Num*100+I2.Num*10+I3.Num)) AS HSE, 
       ... Other Fields ... INTO NewTable

From myTable As M, Integers As I1, Integers As I2, Integers As I3

WHERE BEG_HSE + (I1.Num*100+I2.Num*10+I3.Num) <= M.End_Hse
  AND ((I1.Num*100+I2.Num*10+I3.Num) Mod 2 = 0 
       OR (BEG_HSE + (I1.Num*100+I2.Num*10+I3.Num) = M.End_Hse)

 

[joeclueless2]

Golum,

That works wonders! Thank You!

I see that you inferred quite a bit from the sample, with regards to even and odd values, and the interval of 2 units.

I have three record types: O = ODD, E=EVEN, and B=BOTH.

This is in a field named "SIDE"

The ODD and EVEN types are great. I would like to process records that have a "SIDE" value of BOTH as if they were two records, an ODD and an EVEN one.

Does that make sense?

-Joe

 

[Golom]

Does that make sense?

Not really.

First of all SIDE doesn't appear in your sample. Is it a new field that you want to create?

A Value can be odd (i.e. SIDE = "O") or even (i.e. SIDE = "E") but I'm not up with you on SIDE = "B" given that the value on a given record must be one or the other but it is never both odd and even at the same time.

Post an example of how you visualize this output.

 

[PHV]

SELECT M.OBJECTID_1, (BEG_HSE + (I1.Num*100+I2.Num*10+I3.Num)) AS HSE,
... Other Fields ... INTO NewTable

From myTable As M, Integers As I1, Integers As I2, Integers As I3

WHERE BEG_HSE + (I1.Num*100+I2.Num*10+I3.Num) <= M.End_Hse
AND (I1.Num*100+I2.Num*10+I3.Num) Mod IIf(M.SIDE='B',1,2) = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[joeclueless2]

Golom,

Thanks again for the response! At the time of the original post, I was thinking the side issue would be too confusing.

So there would be the standard output much like the first solution you had provided for original records with "SIDE" field values of "O" or "E", as well as this type of output for records with "SIDE" field values of "B".

Here is an example of one record with a "SIDE" value of "B" and the desired output...

Record:
----------------------------------------------------------------------------------------
| OBJECTID_1 | SIDE | BEG_HSE | END_HSE |PREFIX | NAME |SUFFIX | SUFFIX_DIR |ZIP |
----------------------------------------------------------------------------------------
| 3623 | B | 100 | 199 | E | ADAMS | BL | | 90011 |
----------------------------------------------------------------------------------------

Output for the one record, starting with the EVEN output:

--------------------------------------------------------------------------------
| OBJECTID_1 | SIDE | ID | HSE |PREFIX| NAME | SUFFIX |SUFFIX_DIR| ZIP |
--------------------------------------------------------------------------------
| 3623 | E | 1 | 100 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | E | 2 | 102 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | E | 3 | 104 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | E | 4 | 106 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | E | 5 | 108 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
And so forth....................to:
--------------------------------------------------------------------------------
| 3623 | E | 50 | 198 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
And then continuing.........with the ODD output:
--------------------------------------------------------------------------------
| 3623 | O | 1 | 101 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | O | 2 | 103 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | O | 3 | 105 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | O | 4 | 107 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
| 3623 | O | 5 | 109 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------
And so forth....................to:
--------------------------------------------------------------------------------
| 3623 | O | 50 | 199 | E | ADAMS| BL | | 90011 |
--------------------------------------------------------------------------------


Thanks for being patient

-Joe

 

[joeclueless2]

Golom,

After re-reading your posts I realized that you were on to the concept of having types "O", "E", and "B". Type "B" being as you describe in this quote.:

I just noticed that some of your records start with an even number but end with an odd number (e.g 2500 - 2699).

Thus, this makes the statement:

A Value can be odd (i.e. SIDE = "O") or even (i.e. SIDE = "E") but I'm not up with you on SIDE = "B" given that the value on a given record must be one or the other but it is never both odd and even at the same time.

Untrue.

At first I wanted to generate all potential addresses within the specified range, now I am looking to handle the records differently depending on the "SIDE" value.

Is this more clear?

-Joe

 

[Golom]

Just so we can see what's going on, create a query called "I" like this

Select I1.Num*100+I2.Num*10+I3.Num As N
From Integers As I1, Integers As I2, Integers As I3

and then

SELECT M.OBJECTID_1, 
       IIF((BEG_HSE+I.N) MOD 2 = 0,"E","O") As [NSIDE},
       (BEG_HSE+I.N) AS HSE, 
       ... Other Fields ... INTO NewTable

FROM myTable As M, I

WHERE BEG_HSE+I.N <= M.End_Hse
  AND (BEG_HSE+I.N Mod IIf(M.SIDE='B',1,2) = 0 
       OR (BEG_HSE+I.N) = M.End_Hse)

With credit to PHV for his [blue]IIf(M.SIDE='B',1,2)[/blue] thinking.

I changed the name of the output field from "SIDE" to "NSIDE" to avoid any conflicts with the field "SIDE" in the input table.

 

[joeclueless2]

Thanks Golom,

I tried what you asked and just for the record, there is a brace in the place where a bracket is intended closing out the "NSIDE" field.

After creating the "I" query and then creating another query for part 2 (and then), the results only included one record for each record in the source table. The data generated by the new query were basically the same as the original records, except the [HSE] value is equal to the [END_HSE] value and the [NSIDE] values are "O" in records that contained "B" in the [SIDE] value.

Where you say:

Just so we can see what's going on

I have added an image of what is happening.


Thanks!

-Joe

 

[Golom]

Strange. I got the same result but this does seem to work

SELECT M.OBJECTID_1, M.SIDE,

       IIF((BEG_HSE+I.N) MOD 2 = 0,"E","O") As [NSIDE],

       (BEG_HSE+I.N) AS HSE

FROM myTable As M, I

WHERE BEG_HSE+I.N <= M.End_Hse
  AND ((BEG_HSE+I.N) Mod IIF(M.SIDE="B",1,2) = 0
        OR BEG_HSE+I.N =  M.End_Hse)
 

ORDER BY 4

I Started with
[pre]
OBJECTID_1 SIDE BEG_HSE END_HSE
3623 B 100 105
3624 E 2 8
[/pre]
and got
[pre]
OBJECTID_1 SIDE NSIDE HSE
3624 E E 2
3624 E E 4
3624 E E 6
3624 E E 8
3623 B E 100
3623 B O 101
3623 B E 102
3623 B O 103
3623 B E 104
3623 B O 105
[/pre]

 

[Golom]

Just FYI ...

The problem was

AND (BEG_HSE+I.N Mod IIf(M.SIDE='B',1,2) = 0 

which should have been

AND ([b][red]([/red][/b]BEG_HSE+I.N[b][red])[/red][/b] Mod IIf(M.SIDE='B',1,2) = 0

The Mod operator has precedence over + so the sequence of operations was effectively

AND (BEG_HSE+[b][red]([/red][/b]I.N Mod IIf(M.SIDE='B',1,2)[b][red])[/red][/b] = 0

Sorry about leading you astray.

 

[joeclueless2]

Golom,

Are you kidding?!? This is very helpful stuff!

Thanks for your efforts. I am going to tinker with what you have helped me with for now. I may come back with questions if you're up for it. I am now wondering if this thread is miss-categorized since it is really a query issue. I have no idea how to move the thread to the proper forum.

At any rate, I thank you Golom and PHV for the help on this!

-Joe

 

[joeclueless2]

Hello,

I have the need to move beyond the limits of the query "I". I have modified it as such and would like to be affirmed that this works (even though I tried it and it seems ok).

SELECT I0.Num*10000+I1.Num*1000+I2.Num*100+I3.Num*10+I4.Num AS N
FROM Integers AS I0,Integers AS I1, Integers AS I2, Integers AS I3, Integers AS I4;

Please let me know if anything seems too far off base. I just imitated the pattern, but the use of zero (i.e. I0.Num) has me a bit unsure if that is ok...

Thanks for all the help again!

-Joe

 

[joeclueless2]

Oops,

I think I found an issue with handling [SIDE] = "O" records.

These types are

SIDE NSIDE HSE
O E 686
O E 688
O E 690
O E 692
O E 694
O E 696
O E 698
O O 699

I am trying to figure out why? It is interesting that the last record's [NSIDE] value is processed as an ODD an all others are as Even...

The queries are:

Query "I":

SELECT I1.Num*100000+I2.Num*10000+I3.Num*1000+I4.Num*100+I5.Num*10+I6.Num AS N
FROM Integers AS I1, Integers AS I2, Integers AS I3, Integers AS I4, Integers AS I5, Integers AS I6;

Other Query:

SELECT M.OBJECTID_1, M.SEQID, M.POLYID, M.TEMP_ID, M.EKEY_5, M.MKEY_5, M.HKEY_5, M.SIDE, IIf((BEG_HSE+I.N) Mod 2=0,"E","O") AS NSIDE, (BEG_HSE+I.N) AS HSE, M.PREFIX, M.NAME, M.SUFFIX, M.SUFFIX_DIR, M.CITY_NAME, M.ZIP, M.SCH_YR, M.LAUSD, M.EDITOR, M.EDIT_DATE, M.EDIT_NOTE1, M.EDIT_NOTE2, M.EDIT_NOTE3, M.EDIT_NOTE4, M.EDIT_NOTE5, M.ORIG_PID INTO SampleOutput4
FROM Test_525 AS M, I
WHERE (((([BEG_HSE]+[I].[N]) Mod IIf([M].[SIDE]="B",1,2))=0) AND (([BEG_HSE]+[I].[N])<=[M].[End_Hse])) OR ((([BEG_HSE]+[I].[N])=[M].[End_Hse] And ([BEG_HSE]+[I].[N])<=[M].[End_Hse]))
ORDER BY 4, M.NAME, ([BEG_HSE]+[I].[N]);

Could Golom take another look to see where this is processing the [SIDE] = "O" records improperly?

Thanks for any help!

-Joe

 

[Golom]

You obviously started with a record like this
[pre]
OBJECTID_1 SIDE BEG_HSE END_HSE
3623 O 686 699[/pre]


You are starting with an even number (686) and therefore, because of the constraint about adding two, the numbers generated will also be even. I realize that there is an implied conflict between the specification of "O" (i.e. Odd) as the desired sequence and the even-number starting point. You will see the same effect if you specify SIDE = "E" and start with an odd number.

The choices I see are

1. If you are specifying "O" for SIDE then you must start with an ODD number (and similar comments about "E").

2. Prescribe some algorithm about where to start when SIDE is "O" (or "E"). For example
[blue]If SIDE = "O" and BEG_HSE is even then start at BEG_HSE + 1[/blue]
Similarly
[blue]If SIDE = "E" and BEG_HSE is odd then start at BEG_HSE + 1[/blue]

If you want to go with option 1. then the code remains as-is. If you pick the second option then, although it is possible to do it in SQL, the result is incredibly messy and obscure. Instead create a public function like this and save it in a VBA module.

Public Function SP(BEG_HSE As Long, _
                   SIDE As String) As Long
    If SIDE = "O" And BEG_HSE Mod 2 = 0 Then
        SP = BEG_HSE + 1
    ElseIf SIDE = "E" And BEG_HSE Mod 2 = 1 Then
        SP = BEG_HSE + 1
    Else
        SP = BEG_HSE
    End If
End Function

And then the SQL becomes

SELECT M.OBJECTID_1, M.SIDE, 

IIF((SP(BEG_HSE,SIDE)+I.N) MOD 2 = 0,"E","O") AS NSIDE, 

(SP(BEG_HSE,SIDE)+I.N) AS HSE

FROM myTable AS M,  I

WHERE SP(BEG_HSE,SIDE)+I.N <= M.End_Hse
   AND ( (SP(BEG_HSE,SIDE)+I.N+IIF(SIDE="O",1,0)) Mod IIF(M.SIDE="B",1,2) = 0
             OR SP(BEG_HSE,SIDE)+I.N =  M.End_Hse)

ORDER BY 1,4

Starting with
[pre]
OBJECTID_1 SIDE BEG_HSE END_HSE
3625 O 2 8
3626 E 11 15
3627 B 40 44[/pre]

This produces
[pre]
OBJECTID_1 SIDE NSIDE HSE
3625 O O 3
3625 O O 5
3625 O O 7
3625 O E 8
3626 E E 12
3626 E E 14
3626 E O 15
3627 B E 40
3627 B O 41
3627 B E 42
3627 B O 43
3627 B E 44[/pre]

Note that

- "O" series beginning with an even number or "E" series beginning with an odd number do not show the first number in the series.

- a SIDE = "E specification that ends with an odd number will still show that odd number (and similar comments about SIDE = "O"). If you don't want that then eliminate the

[blue]OR SP(BEG_HSE,SIDE)+I.N = M.End_Hse[/blue]

clause.

 

[joeclueless2]

Golom,

I'm getting a late start today. I appreciate your response and shall try to implement the public function in a VBA module today.

On a related note, I mistakenly published my last post without specifying that the original record was like:

SIDE BEG_HSE END_HSE
O 601 699

and I had posted only the last handful of output records to illustrate the difference between the [SIDE] and [NSIDE] values of the majority of the output records and that the last record was correct.

I am not sure if that helps shed light on anything, but you should probably know that the input record was "Odd".

Thanks again,

-Joe

 

[Golom]

Tried 601-699 with the code I posted ... WORKS!