Automating Access 2003 data entry

By dale ·
Firstly, see the image

What I was tasked to do (Why an IT Technician had been delegated a tedious data entry job, I do not know!) is to merge a ton of information from an Excel spreadsheet into an existing database. This is now done, but I need to add to that data.

Looking at the image, the large rectangle shows what I need, basically:

'Cal_Ref' - I need to enter the number '1' in that field for the rest of the records
'Cert_No' - I need to enter the text 'nocert' in that field for the rest of the records
'Cust_Ref' - Now this I need to copy from the field (on the right) and paste it in there - If you notice, where It's being copied from and where it's being pasted to are different tables.

Is there any Macro or VB code that could help me with this?
There are about 8400 records.... so you can see why I'm trying to automate this.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Just clarify

by Shellbot In reply to Automating Access 2003 da ...

This sounds like a 5 minute job however, I may not be understanding you correctly
1. ALL 'Cal_Ref' will need the value of "1"
2. All 'Cert_No' will need the value of "nocert"

Now, with Cust_Ref:
You want to move the cust_ref from one table to another table?
problem with this one is that Cust_ref would normally be the record id that you would use to make a link between the two tables. Moving an ID from one to another is a bit dodgy. Is there another column that is existing on both tables (that has data) that we can use to link the tables?

Lets step back, did you import this data into Access?

Collapse -


by dale In reply to Just clarify

Hi Shellbot, thanks for the reply!

1. ALL 'Cal_Ref' will need the value of "1" - Correct
2. All 'Cert_No' will need the value of "nocert" - Correct

'Cust_Ref' is not a primary key field. I'm not trying to move the field, just copy data from 'Cust_Ref' in one table in to 'Cust_Ref' in another table.

See image for the table relationships.

Yes, this data was imported, after jigging it about a lot, I've managed to get it where it needs to be, apart from what I need to achieve now.
Unfortunately this data isn't on it's own; It's part of a database consisting of another 80,000 odd records. I don't need to thouch them, thank God, just the 8,000 odd that I've imported.

In reference to your post about the queries, are you sure that's going to be possible as there are other records that already have data in that field? The records I am dealing with have blank fields.


Collapse -

Add a where clause

by Maevinn In reply to Reply

The second post includes the update statements, add a where Cust_Ref is null to each query.

Collapse -


by Shellbot In reply to Reply

I see now..your right..don't wanna overwrite anything.

Shame ya didn't ask before you merged it into the existing table. My approach would have been to get the data into a table, do all the jigging and then merge it into the existing data..soooo much easier.

So ya,
will the 'Cust_Ref' field be blank for these 8000 records? If so, will the pre-exisintg data in the table have blank 'Cust_Ref' fields??

As the other poster sugested, a where clause will work, but have to make sure we get the right clause.

Give me 5 mins and I'll post another query for ya ok.

Collapse -


by Shellbot In reply to Reply

** do not do this untill you have done the other two queries, because we want to leave this field empty so we can use it for the where clause ok!!**

Ok, so Inst_ID is the same on both tables? Don't mean to be condesending, just don't want to steer you wrong!!

IF your sure that they are, then this will do the trick:

UPDATE tblDestination INNER JOIN tblSource ON tblDestination.Inst_ID = tblSource.Inst_ID SET tblDestination.cust_ref = [tblSource].[cust_ref];

(change table names and just put it all on 1 line so it doesn't freak out at incorrect formatting or soemthing.)

Now, as I should ahve asked you in my first post:

If not, do so immediatly!!!!! And each time you make a change back it up, so you can always roll back to specific point if need be..

I'm off for the day now, might check in with ya later if i on at home, otherwise I see what your up to tomorrow ya?

Collapse -

2 queries for you

by Shellbot In reply to Automating Access 2003 da ...

go to queries - new - design view - close without choosing a table. click SQL (top left). Copy in the first query below, change "tblDesination" to the name of the table you want to update, and run the query.
this will change every value in that column the value of 1. Do the same for the next query.

UPDATE tblDestination SET tblDestination.cal_ref = 1;

UPDATE tblDestination SET tblDestination.cert_no = "nocert";

Collapse -

Find empty ref

by Shellbot In reply to 2 queries for you

run this query please (changing your table names).

SELECT tblDestination.cust_ref, tblDestination.*
FROM tblDestination
WHERE (((tblDestination.cust_ref) Is Null));

Now this will bring back the rows where cust_ref is empty. Is there only records which are the ones you've newly added, or is there older ones in there as well?

Collapse -

With where clauses

by Shellbot In reply to Find empty ref

If you are happy that the only blank cust_ref's are your new rows, these will do the trick:
(change table names)

UPDATE tblDestination SET tblDestination.cal_ref = 1
WHERE (((tblDestination.cust_ref) Is Null));

UPDATE tblDestination SET tblDestination.cert_no = "nocert"
WHERE (((tblDestination.cust_ref) Is Null));

Collapse -


by dale In reply to Automating Access 2003 da ...

Hi Shellbot. I haven't ran anything yet, just trying to get my head around it all! I really am not good with databases.

In reply to some of your questions:
The 'Cust_Ref' field is blank for the new records in the table 'calibrations', but it populated in the table 'instruments' (which is where we need to copy the data from).

Instrument ID is the same in both tables, yes.

Yes, I have backed up each time! First rule of thumb :)

Ok, I've spoken to our Web Developer, he says that this database is in a right state (quality vendors for you!) and that there is no 'auto number' reference. Will what we're trying to do with the SQL queries still work?

I really appreciate your time and help, just need to get it all straight in my head before I proceed.


Collapse -

no worry

by Shellbot In reply to Reply

doesn't matter that there is no auto_refernce..that makes no difference at all.

Instrument ID is the key here. Thats what links the 2 tables (as your relation diagram shows).

Obviously your not experienced in the ways of the database, hence why I'm trying to step it out for you :)

Now, here's what ya do.
Make another copy of that database and drop it onto your local drive or whatever.
Then do this work and see what happens. As with backing up, never do something your not sure about on a live system..always on a test bed first. So make that copy, then:

1. Run that SELECT query I gave you to check to make sure that ONLY your NEW rows have empty 'Cust_Ref'.
2. If happy that none of the old rows have an empty 'Cust_Ref', then run the 2 UPDATE queries to populate "1" and "nocert".
3. Run the UPDATE query to move the 'Cust_Ref' fromthe old table to the new table.

I understand your nervousness, go through it slowly so you understand what your doing.

And hey, because your on a copy, if it all goes to h*ll no matter right!

Note: 99% of the databases I've ever seen are in a "right state".. :) Its shocking how badly most are set up and maintained. They are sold to people with "ah sure it looks after itself.." and 6 months down the road the client is up sh!t creek with servers crashing and applications not working because the DB's are in falling over..

Related Discussions

Related Forums