access problem, 5days now, cant figure it out

[fbm2themex]

im trying to move/copy a colomn("bates") from table two, and place/replace/insert it in table 1("bates" colomn, same table name but different values)(or, just insert it to table 1, without replacing anything)...i been trying join, inner, left, outer, etc, nothing works, i get it show in table1, but it repeats the same value over and over(example "1, 1, 1", instead of "1,2,3")...here are the tables

1st table 2nd table
Dlexload (table1) Bate(table2)
Bates bates
Path counter
doc docname
vol
can anyone help me????

 

[Rofeu]

Are you just trying to stick the values in table2.bates to the bottom of table1.bates (with the rest of the fields remaining empty)??

If so, a simple append query should do it.

Just goto Query and Select 'Append Query', the rest pretty much shows itself.

Cheers,
Roel

 

[fbm2themex]

no, im trying to replace the hole values of table1.bates with the values of table2.bates...i dont know how to though...

 

[Welshbird]

Which fields should link between table1 and table2 then? I think that is pretty key to this (oo bad pun!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[fbm2themex]

just Bate, theres no other fields that could be linked...is ther way where i could just delete the field in table1, and replace/add it with the field from table2?? to make things easier...

 

[Welshbird]

So how can it know which values to insert into which record?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[fneily]

Please post in the Access forums such as:
Microsoft: Access Tables and Relationships
Microsoft: Access Queries and JET SQL

You said your join showed you the data being transferred? Impossible. Joins only connect tables, they do not transfer data.
You need to use an Action query. As stated above, either Update, Delete, Append or Make table - or a combination of them.
Maybe if you could show the tables before and what you want after, it'll be clearer as to what you want.

 

[Rofeu]

So, basically, Table1 and 2 have the same amount of records and you need to delete the Bates from 1 and put the Bates from 2 in their place??? Why not just copy 'n' paste....?

Maybe if you explain a bit more as to the reason why you need to do this, it might become clearer.

Cheers,

Roel

 

[fbm2themex]

Because thers about 26,000+ records...and i can only copy about 16,000 tops...

 

[Rofeu]

why is that? Otherwise just export both tables to Excel, do your c 'n' p and import it back into access.

Cheers,

Roel

 

[fbm2themex]

my bad, i have 277,996 records..i try doing the excel thing..it export about 66,000...

 

[randy700]

Please provide some sample data. What you have and what you want to have.


Randy

 

[fbm2themex]

hey randy,

i took a screen shot...hopefully it works out well

[URL unfurl="true"]http://img.photobucket.com/albums/v318/fbm2themex/accessprob.jpg[/url]

 

[fbm2themex]

heres the table info

in table 'Dlexload' i have
Bates
pah
doc
vol

in 'Bates' table, i have
ID
DOCname
Bates
counter

in order for me to take Bates from Bates table, merge that specific column with Dlexload table, what do i have to in order to do so?? because i tried, i do get it to show in the Dlexload table, but it comes out repetitive of the 1st value...it doesnt come out right in another words...

 

[randy700]

ok.. that shows what you have... now, what do you want?


Randy

 

[fbm2themex]

im trying to move/copy the hole bates column from 'Bates' Table, and put it in Dlexload table...

 

[Welshbird]

Should the ID in BATES table link to the BATES column in DLEXLOAD?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[fbm2themex]

I figured out the problem, the datatype was all wrong, for one field it was text, the other was number...thats why my query wasnt working well...

 

[Welshbird]

Glad you found that out.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]