loop help 1

[cdw0308]

I have a table with 2 fields

cable_num
cond_num


cable_num is a text field

cond_num is a numeric field

ex.

cable_num cond_num

1AP0001 4
1BA0001 2


I need the above info in a new table and formatted like below:

1AP0001_1 4
1AP0001_2 4
1AP0001_3 4
1AP0001_4 4
1BA0001_1 2
1BA0001_2 2

I am very novice at this and I believe i will need some sort of loop plus an INSERT and UPDATE phrase.

Any code examples would help me greatly in getting this project started the right way.

Thanks

 

[lameid]

What is the maximum cond_Num? If it is relatively small, you can do this entirely manually...

You could make a table that lists numbers up to the maximum, say Number_list with a field of intList.

Then you could use something like the following query...

Select Table1.cable_num & "_" & Number_List.intList, Table1.cond_num
From Table1, Number_List
Where Number_List.intList <= Table1.cond_num

That is the list anyway.

You could write VBA code to populate a long number list or populate a table with the values you want. To make the number list I would probably just start numbering down in Excel to 4 or so and copy the results down so that it keeps incrementing and import that. That is the easiest way I can think of.

 

[cdw0308]

The Maximum number of for cond_num is 48
I have thouands of cable_nums that this has to be done to.
I would like to find a way to do it without manually typing it in excel and exporting every record.

I made a table called Number_List with a field called intList.
I input the numbers 1 - 48 in this table.
Then i created a query as you showed above in that code and it just made every cable_num have increment to 48. It did not look at the cond_num field and stop with the max number for that particular cable_num.

Here is what i have for my code so far.

SELECT max_conductors2.cable_number & "_" & Number_List.intList AS Expr1, max_conductors2.Spec_Condno
FROM max_conductors2, Number_List
WHERE (((Number_List.intList)<=[max_conductors2].[Spec_CondNo]));

Any further help would be greatly appreciated.

 

[lameid]

Are both the fields in the where clause numeric (Number_List.intList and max_conductors2].[Spec_CondNo])?

The only thing I can figure is it is one is text and throwing everyhting off.

 

[cdw0308]

That was it.
I had them both as Text fields instead of numberic.

Thanks for all the help.
It worked great.