Update Query problem

[sdel_nevo]

Hi Guys

I have been asked to insert new data into a table in a database

The table is calledtblTestSheet This table has three fields in it.

TestSheetID


PartNumberID
ProcessNumber


The ProcessNumber field is a new addition to this table and as such I have been asked to populate the required process number value for each TestSheetID based on PartNumberID

The ProcessNumber increments by 10 for each TestSheetID based on PartNumberID

for example

we have

TestSheetID	PartNumberID	ProcessNumber
467	127	**10
468	127	**20
469	127	**30
470	127	**40

***are the values that need to be entered*****


The problem I have is that this table contains thousands of entries and to manually enter this data will take ages


is it possible this could be done by an update query?

some PartNumberID only have 1 TestSheetID, other PartNumberID's have many TestSheetID's

in stuck as to how I could do this

any ideas would be fantastic

many thanks

Steve

[orange]

Interesting. You have provided very little info about your overall situation. A brief description of the "purpose" or "business" involved would help with context. If we knew a little about "what" this was about, you might get more focused responses.
For example, what is a Part? Where does it fit in your setup? What is a testsheet?

If you have table of Parts, and a table of Testsheets, and you want to get each combination of testSheet * Part and assign a new unique identifier (ProcessNumber), you could use a Cartesian join and a For loop with 10 as the increment.

But you'll have to clarify your requirement before jumping into a physical solution.
Good luck with your project.

[JoeM]

Here is what I would do, as it should be fairly quick and easy.
Export your list to Excel. Sort your data and perform the calculation to populate the new field.
Import the Excel file into a temporary table.
Link this temporary table to your original table, based on the primary key field(s).
Do an Update Query to update the field in your original table from the value in the same field in the temporary table.

[sdel_nevo]

Hi Orange

Many thanks for the reply

The table tblTestsheet holds information that builds a form called frmTestSheet based on the partnumberid

the frmtestsheet is used to record test results for our quality department during production of various parts

the managers here now need to change the entire system as we are moving over to Navision as the ERP system


its for this reason that I now have to enter this information, which is nice LOL

I like the sound of the Cartesian join but I'm not sure I understand how to do that

we have a table tblPartNumber, its from this table that thefrmTestSheet saves the PartNumberID into the tblTestSheet



The processNumber is a requirement of Navision, so that the development costs can be reduced for the Navision project I have been asked to enter this data before it's imported into the new Navision system rather than having to enter the data once its been imported

hope this makes sense


many thanks

Steve

[sdel_nevo]

Hi JoeM

I did think about going the excel route, but I would still have the issue of updating the required values manually (or via a Macro)
and I could not get my head around how to do that to be honest,

I think that the best approach would be to go for the Cartesian join and a For loop as per oranges reply

will do some research on going that way

many thanks for the reply

Steve

[orange]

Research Cartesian Join Cartesian Product.
Clearly define your requirement and test.
In essence if you have 2 tables let's say tbl1 and tbl2 where tbl1 has data in fld1 a,b,c,d and tbl2 has fld2 with data 1,2,3
then the query

Select fld1, fld2 from tbl1,tbl2 (Note there is no JOIN so you get a Cartesian product)

Gives these results.
a1
a2
a3
b1
b2
b3
c1
c2
c3
d1
d2
d3

You could create a new table with these values.
You could then run a small routine to take these values, and add a new field to the table
and value that field with 10, 20, 30 ..... such that you now have possibility to make/concatenate/display values such as
a110, a220 etc.


Do some research and lots of testing to ensure it does what you need.

[sdel_nevo]

Hi Orange

That's Great, Many thanks for the heads up

Steve

[JoeM]

I did think about going the excel route, but I would still have the issue of updating the required values manually (or via a Macro)

Do you mean in Excel or Access?

In Excel, it is a simple formula you can copy down for all rows.
So, if you first value is in cell C2, you would just enter 10 in cell C2 in Excel.
Then, in cell C3, just enter the formula:
=C2+10
and copy that formula all way down for all your rows.

Then, import (or simply link) this Excel file into Access into a new temporary table in Access, and do you Update Query between this table and your original table to populate your original records with these new values.

No macro or manual updating required.

[sdel_nevo]

Hi JoeM

OMG lol, that's brill
sometimes I guess you cant see the woods for the trees

many many thanks

@ orange

many thanks for you help, I will however research your Cartesian product as its something I know nothing about

thank you both very much for your help

Steve

[JoeM]

You are welcome.

Note that I am not sure if you start the Process Number over at 10 when the PartNumberID changes. If so, then you would just use a formula like this in C2 and copy down:

=IF(C2=C1,C1+10,10)

(of course, you need to be sure that your records are sorted so all the PartNumberID fields are grouped together).

[sdel_nevo]

Hi JoeM

Class, many thanks again

Steve

[orange]

Steve,

For reference, and possibly future use
see this link www.accessforums.net/showthread.php?t=48558

for an example of cartesian product and usage

[sdel_nevo]

Hi orange

thats great,will take a look and try to get my head around it

many thanks again

Steve