comparing 2 databases new to access..help.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

comparing 2 databases new to access..help.

Author

Topic

ebaydoug
Starting Member

15 Posts

Posted - 2003-09-11 : 17:24:01

You know the scoop big project, needs to get done..i have never done a thing but read an access report, now i need to be an expert. Anyway I had a good week, figuring it out myself, but now I am stumped.

Basically I have a large customer list of 650,000 email addresses (tblA1) broken down in to queries of 50,0000.
lets call one query of 500000 tblA1 Query

i need to check the 50,000 email addresses in tblA1 Query against my 350000 list of emails in another table in another database tblglobalRemove091003s

If the address exists in both tblglobalRemove091003s and tblA1 Query

i want to remove it from tblA1 Query and save the query without those addresses.

i am lost between comparing tables, databases and queries. Any suggestions?

thanks in advance.

visit http://www.eBayDoug.com today!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-09-11 : 20:21:17

Hi Doug

I'm assuming you're talking about a .mdb file? If you are then what you're describing can be done as follows:

delete from tblA1
where emailaddress in (select removeaddress from tblglobalRemove091003s);

In esssence you don't really need the query of the 50000 records.

From the database window, create a new query, from the view menu, choose SQL and paste in the code above (make sure the names "emailaddress" and "removeaddress" are replaced by the correct names from the two tables....)

Then, hold your breath, (crossing your fingers probably wouldn't hurt either) and run the query...Probably a really good idea to make a copy of tblA1 (with the data) before you do this, just in case.

Let me know how you get on...You may experience memory difficulties depending on your machine, so I'd make sure you weren't running anything else at the time.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ebaydoug
Starting Member

15 Posts

Posted - 2003-09-12 : 13:31:41

thanks..i am just getting in the office i will give it a shot

visit http://www.eBayDoug.com today!

ebaydoug
Starting Member

15 Posts

Posted - 2003-09-12 : 13:38:40

OMG It worked! Thank you!!!!!! (Well it removed 1400 records..so i son't know what they were but its all good! I will assume it worked! thank you thank you thank you!

visit http://www.eBayDoug.com today!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-12 : 14:03:55

You could have looked first...

select * from tblA1
where emailaddress in (select removeaddress from tblglobalRemove091003s);

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

ebaydoug
Starting Member

15 Posts

Posted - 2003-09-15 : 14:03:24

angels...quick easy question...

i have table with unique primary key and email address.

I want to add 3 fields.

subscribe unsubscribe and bounce

I can do this.. (not that dense)

but when i want to assign each field a unique number...so my software can increment that number appropriately.

I can do this...specify, number, integer, and the value (let's say 10)

when i add the fields and save, i do not see where access pust in the value in the field after I dave the design view. Do i need to perform and operation to get the value (10)in the field?

Once again Thanks in advance.

visit http://www.axzar.com today!

ebaydoug
Starting Member

15 Posts

Posted - 2003-09-15 : 14:10:02

stutter bad spell much doug? dang!

visit http://www.axzar.com today!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-09-15 : 19:41:20

hi doug - glad to hear of your deleterious success!

You've got two options with incremental ID (unique) numbers. You can define the field as "autonumber" and let access handle it all. Alternatively, you can define as a number (Long). If you do the latter, you've got to manage everything yourself.

I couldn't quite tell which of these you were trying to do, so let me know.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ebaydoug
Starting Member

15 Posts

Posted - 2003-09-16 : 13:08:27

ok thanks...i figured it out...thanks for helping.

visit http://www.axzar.com today!

ebaydoug
Starting Member

15 Posts

Posted - 2003-09-17 : 13:07:34

ok now i want to replace the string xxx.com and have it instead say yyy.com in my emailaddress field.

any takers?

visit http://www.axzar.com today!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-09-17 : 21:56:27

This might help


create table tempA (name varchar(50))

insert into tempA (name) values ('xxx.com')
insert into tempA (name) values ('xxx.com.au')

update tempA
set name = 'yyy' + substring(name, charindex('.', name) ,len(name) - charindex('.', name) + 1)
where name like 'xxx%'


select * from tempA

drop table tempA

but again - not really sure what you want. Have a look at String Functions in BOL. Where does 'yyy' come from - another table? Are you setting all of 'xxx.com' to 'yyy.com' or only certain values, etc etc etc

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources