Software

Ask something
Question Locked

Automating Access 2003 data entry

0 helpful
45 replies

by dale

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Firstly, see the image http://i39.tinypic.com/scz5eu.jpg

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.

45 replies

by shellbot

This sounds like a 5 minute job however, I may not be understanding you correctly
So:
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?

by dale

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 http://i39.tinypic.com/2dl8aoh.jpg 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.

Thanks.

by maevinn

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

by shellbot

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,
'Cal_Ref'
'Cert_No'
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.

by shellbot

** 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:
HAVE YOU BACKED THIS DATABASE UP?

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?

by shellbot

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";

by shellbot

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?

by shellbot

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));

by dale

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.

Cheers,
Dale.

by shellbot

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..

by dale

Well that's good news :)

Yep, have made a copy and working on that.

I've just tried to run the 'Select' query... but got all baffled, so thought I'd move on to the update ones as they seem easier :)

However... the udate query you've provided hasn't got a 'where null' clause. I'll need this, otherwise it would overwrite the existing data on the older records - I assume?
EDIT: Ignore me.... I've just seen your other post with it in :)

Well, the software itself is pretty crap, so I'm not surprised at the database lol.

Cheers.

by dale

Ah, you're going to be fed up of me by the end of today! :)

I assume that you have to run the select quuery first then, otherwise the update query doesn't know what to update?

Only assuming this as I've just tried to run the update query and it's throwing up a syntax error (missing operator).

Really I could just e-mail you the database and you start me off ;) haha.

by shellbot

I'm gonna start from top, so i'm gonna go away for 10 mins and type it up.

"I assume that you have to run the select quuery first then, otherwise the update query doesn't know what to update" - Correct :)

by shellbot

I need you to check 1 thing for me, on your tables, the inst_ID, is this a unique number?
as in, it will only occur once in each table?

Look in the table design, click on the cloumn name and down below in the INDEXED value, does it say "Yes (no duplicates)" ?

by dale

We're dealing with 2 tables at the moment... the table 'Instruments' (this is the table which has the data) says 'Indexed: Yes (No Duplicates)' but the table 'Calibrations' says No.

Yes, 'Inst_ID' is actually unique field in both.

Will this be a problem?
It really is an awfully designed database.

by shellbot

ok, in the diagram of the 2 tables ye posted both tables have a primary key on Inst_ID..
can't be a primary key if it says No.

Weird..

ok.. do you know for sure that it is unique in both, as in there will only be 1 row in each table that has that id and it matches the other table like so:

table1
name=shelley ID=1
name=dale ID=2
name=dale ID=3

Table 2
name=shelley ID=1
name=dale ID=2
name=dale ID=3

where id=3 on table 1 is the same dale with ID=3 on table 2

If we not sure, we'll have to check, but if your certain..then ok

by shellbot

ok,

Queries - New
Choose "Find Duplicates" from list, OK
Choose Calibrations - next
double click Inst_ID - next
don't bother - next
finish

if any duplicate inst_ID exists on that table, it will show up and it will give a count of how many.

For the fun of it, so it on the other table as well.

by dale

My head is officially about to explode haha.

Erm... I'm... pretty sure... :)
The field 'Inst_ID' in the 'Calibrations' table doesn't seem to actually appear on the datasheet view of the table, but it's there in design view... strange...

The bad thing is, the field 'Inst_ID' isn't actually an autonumber, it's just text... but it is unique to each record, as that's how the software inputs it.
There's no primary ID autonumber.

by shellbot

it doesn't have to be an automunber to be unique..

use the duplicate checking i posted to double check and let me know what it turns up

by dale

Right, the table 'Calibrations' does contain duplicates..... lots of them. It's given a new record using the same ID every time it gets calibrated.
The table 'Instruments' contains no duplicates (as expected).

by shellbot

ok..
refresh my memory

calibrations = new data going into instuments
instruments = pre-exiting table where you've added in 8000 rows form calibrations

by shellbot

i just reread thread..
and its

calib = existing table with 8000 new records
inst = new table with blank cust_ref

by dale

Instruments table: I've imported my data into this.
Calibrations table: This is linked to the Instruments table and is what needs populating.

I really don't know how to explain it.
Here's another image that might help http://i43.tinypic.com/mvj7o8.jpg

To recap:
I need to enter the number '1' in the 'Cal_Ref' field of the 'Calibrations' table, the text "nocert" in the 'Cert_No' field. I then need to copy the data from 'Cust_Ref' field in the 'Instruments' table in to the 'Cust_Ref' field of the 'Calibrations' table.

Thanks.

by dale

Instruments is the one that has the new 8000 records.
Calibrations contains the blank fields.

by shellbot

Now, chill out and take one of those there clean copies of the database you've got and go for it.
Its a copy..so we can do-over if this isn't want ye want.

Ready!!!

so using your test DB:

Step 1:
To get to SQL view on queries:
Click on Queries in Object List
Click NEW on menu, choose Design View, click close on show table window
Top left corner, click SQL, delete the "SELECT;" that is there.

Step 2:
Copy and paste in this query.

SELECT calibrations.*
FROM calibrations
WHERE (((calibrations.cust_ref) Is Null));

have a look-see in there..if your happy that only the 8000-ish new rows have an empty cust_ref, then we're in business.
Take note of the number of rows returned.
Proceed to step next step :)

Step 3:
repeat step 1

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

a box will alert you to say " x records will be updated" does that match your number of rows form the Select query?
If yes, click ok.

repeat step 1

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

a box will alert you to say " x records will be updated" does that match your number of rows form the Select query?
If yes, click ok.

Step 4:
If all has gone well so far, make a copy of the database in case this goes wrong.

repeat step 1

UPDATE calibrations INNER JOIN instruments ON calibrations.Inst_ID = instruments.Inst_ID
SET calibrations.cust_ref = instruments.cust_ref;

You do not need a where clause on this, thats what the inner join is kind of for. it links the records on the Inst_ID.
So basically, it is saying update it where these two fields match each other.

Now, if you get syntax errors or something I'll blame you, because I have created tables and ran the queires to test
that they work and they work just fine for me. :)
If all of this works, you better give me a thumbs up for this..if not I will hunt you down. :)

by dale

Ok, now don't shout at me :P but I've just realised that the records wont be in the calibrations table, as they've not been created yet... they've only been created in the Instruments table.
This is the part where we need to create the number 1 in the 'Cal_Ref' - this is another primary key (yes, there's 2!) in the Calibrations table.

I'm actually doubting myself now...
Thinking on... is this actually going to be possible?

I really do appreciate your help!
Hey, if I can get this working, I'll send you a box of chocolates and bottle of wine... ;)

by shellbot

ah see..I was thinking right then in the place.. ok

now stop confusing me and lets get this show on the road.

so using your test DB:

Step 1:
To get to SQL view on queries:
Click on Queries in Object List
Click NEW on menu, choose Design View, click close on show table window
Top left corner, click SQL, delete the "SELECT;" that is there.

Step 2:
Copy and paste in this query.
SELECT Instruments.*
FROM Instruments
WHERE (((Instruments.cust_ref) Is Null));

have a look-see in there..if your happy that only the 8000 new rows have an empty cust_ref, then we're in business.
Take note of the number of rows returned.
Proceed to step next step :)

Step 3:
repeat step 1

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

a box will alert you to say " x records will be updated" does that match your number of rows form the Select query?
If yes, click ok.

repeat step 1

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

a box will alert you to say " x records will be updated" does that match your number of rows form the Select query?
If yes, click ok.

Now...I'm going for tea break..back in 15-20.. do the above and tell me how it goes!

by dale

Not working how I need it to :(

The query
SELECT Instruments.*
FROM Instruments
WHERE (((Instruments.cust_ref) Is Null));
brings up 84,287 records.... but they're all old records. The new ones I've added aren't there :S

The Inst_ID field is the main primary field that the database uses. It's not an autonumber, just text. The original records are in the format 'IDxxxxx' (x being numberical and however many there are) the new ones that I've added are 'newxxx'. Surely this wouldn't make a difference?

Not going to be happy if the s**t design of the database is going to prevent me from doing what sounds like a pretty simple thing (as you've managed to explain it all :) )

by maevinn

SELECT Instruments.*
FROM Instruments
WHERE (((Instruments.cust_ref)= New*));

(hurry, before shellbot gets back!)

:D

by dale

SELECT Instruments.*
FROM Instruments
WHERE (((Instruments.cust_ref)= New*));

Brings up an error stating there is an extra ) in the expression.
Also, wouldn't work anyway as it's the Inst_ID field that has the newxx data in, not cust_ref

Giving me false hope :( haha.

by shellbot

ok for that query it don't matter a fig about the ID .

Take a deep breath, we just trying to find the empty "cust_ref".
Now, The records that came up the 84287 rows, was cust_ref empty?

by shellbot

use this one then

SELECT Instruments.*
FROM Instruments
WHERE (((Instruments.Inst_ID) Like 'new*'));

by shellbot

i'm goin to the pub!

we'll sort ya out next week ok. I still think this is simple, we've just got a bit of confusion on what is where.

is this sensitive data? if not, i might get you to actaully just copy those 2 tables, zip em and email to me next week.

have a good weekend..

by dale

"Next step
ok. if your happy with the select, then step 2:
UPDATE Instruments SET Instruments.cal_ref = 1
WHERE (((Instruments.Inst_ID) Like 'new*'));

then step 3:
UPDATE Instruments SET Instruments.cert_no = "nocert"
WHERE (((Instruments.Inst_ID) Like 'new*'));"

The fields 'Cal_Ref' and 'Cert_No' only appear in the Calibrations table though, not the Instruments table. But this only appears when you add a new record to the Instruments table anyway, which is why I did ask if it really was possible to do.

Thanks for the reply.

by dale

Morning, hope you had a good weekend!

Ok, that suggestion does work. The query gives me all of my new records that I added to the Instruments table :)

So, working on from that, where do we need to go next? Baring in mind that to create the record in the Calibrations table, we need to put that number '1' in the 'Cal_Ref' field. and then need the 'nocert' entering in to the 'Cert_No' field and then copy the 'Cust_Ref' data from the Instruments table to the Calibrations table.

Sorry for causing any confusion....

Thanks!

by shellbot

ok. if your happy with the select, then step 2:
UPDATE Instruments SET Instruments.cal_ref = 1
WHERE (((Instruments.Inst_ID) Like 'new*'));

then step 3:
UPDATE Instruments SET Instruments.cert_no = "nocert"
WHERE (((Instruments.Inst_ID) Like 'new*'));

by shellbot

Seems we've had success!!
Some initall confusion on what was actually needed to go where..but once we sorted that out..piece of cake right Dale?
(Ended up taking the issue offline..)

Queries as follow:

UPDATE instruments SET instruments.cal_ref = 1
WHERE (((Instruments.Inst_ID) Like 'new*'));

UPDATE instruments SET instruments.cert_no = "nocert"
WHERE (((Instruments.Inst_ID) Like 'new*'));

INSERT INTO calibrations ( Inst_ID, cust_ref, cal_ref, cert_no )
SELECT [instruments].[Inst_ID], [instruments].[Cust_ref], [instruments].[cal_ref], [instruments].[cert_no]
FROM instruments
WHERE ((( instruments.Inst_ID) Like 'new*'));

Running a quick check to see if the number from source table matches the destination table as some records were previsouly added manually, so was throwing a key violation error. Thats ok though, its doing exactly what it should be by doing that.

SELECT calibrations.Inst_ID, instruments.Inst_ID
FROM calibrations INNER JOIN instruments ON calibrations.Inst_ID = instruments.Inst_ID
WHERE (([instruments].[Inst_ID] Like 'new*'));

by maevinn

Heh--I was out on Friday, caught up this morning, and was in the process of suggesting using the sub-query in where clause when I decided one more set of hands wasn't going to help. Glad it got worked out!

by shellbot

Just a bit of confusion on what data was where and what exactly needed to be done. The lightbulb went off this morning and we sorted it pretty quickly after that!

I should have posted that we were working offline ..my bad.

But at least we've got it sorted now!

by dale

Well in the end... all went well on the test database. Going to try it on the live one tomorrow :)

Many thanks to Shellbot here for her hard work... and patience ;-)

by dale

Everything seemed to have worked correctly on the live system.

Thanks for all your help :)

This discussion is closed to new replies.