Using queries to create new tables to normalize database

[lirantha]

I've been trying to teach myself Access, so I'm just barely aware of the conventions; please pardon any newbie errors. if presenting the problem in a different format would be better, please let me know!



So, I've currently got bibliographical information in one big, not-normalized table (tblPublicationEntries) with 515 records. Here are my fields:

EntryID (primary key)
HD (foreign key)
PubID (currently null; needs to be a second foreign key; see below)
Corpus
CorpusVolume
AuthorLast
AuthorFirst
PubYear
CatalogNumber
PageNumberStart
PageNumberEnd
FigNumber

The problem with this set up is that, really, Corpus, Corpus Volume, AuthorLast, AuthorFirst, and PubYear taken together should be split off into a separate table. I figured out how to do that part by making a new table with a SELECT DISTINCT query (=tblPublication). I then added a new Autonumber field to be the primary key of that table, leaving me with this:

PubID (primary key)
Corpus
CorpusVolume
AuthorLast
AuthorFirst
PubYear

This results in 158 records, a number of which include null values for various fields. What I'm trying to do now is get my nice new tblPublication.PubID (primary key) numbers to go into the tblPublicationEntries.PubID (foreign key) field in my original table, so that I can set up a one to many relationship between tblPublication and tblPublicationEntries.

I tried an UPDATE query, but couldn't figure out how to get the WHERE clauses to work since a relationship doesn't currently exist between tblPublicationEntries and tblPublication. The "Enter Parameter Value" dialog box keeps popping up, and what I was trying to do was avoid having to enter all of the information by hand in the first place. Any suggestions?

[CJ_London]

what makes your record unique is the collective of Corpus, Corpus Volume, AuthorLast, AuthorFirst, and PubYear. This has been 'summarised' into a single field PubID

So create an update query with your tblPublicationEntries and tblPublication linking each on the of these five fields and update the pubID in tblPublicationEntries with the pubID in tblPublication

[lirantha]

*nods* That's what I was trying to do. But I'm getting something wrong in the SQL syntax. (In addition to not knowing Access, I've never worked with SQL before this week.) This is what I had:

UPDATE tblPublicationEntries
SET tblPublicationEntries.PubID = tblPublication.PubID
WHERE tblPublicationEntries.Corpus=tblPublication.Corpus
AND tblPublicationEntries.CorpusVolume=tblPublication. CorpusVolume
AND tblPublicationEntries.AuthorLast=tblPublication.Au thorLast
AND tblPublicationEntries.AuthorFirst=tblPublication.A uthorFirst
AND tblPublicationEntries.PubYear=tblPublication.PubYe ar
;

But that's the query that kept asking me to enter parameter values.

I reworked it just now in the query builder after creating inner joins (is that what you meant by linking each of the five fields?) by dragging the matching fields on top of one another. But running the query alters only 34 records rather than all 515 - perhaps because so many of my records have null values in one or more of the joined fields? How would I make it also update records that include null values in one or more of the linked fields, if that's the case?

EDIT: Okay, so pardon the n00bness; did some reading and found that replacing the inner join with a left join should be altering all 515. But when I run the following query, it warns me that I'm about to change 515 records, but I still don't get any new values in the tblPublicationEntries.PubID column except in rows without any null values in the joined fields. What am I missing now?

UPDATE tblPublicationEntries LEFT JOIN tblPublication ON (tblPublicationEntries.CorpusVolume = tblPublication.CorpusVolume) AND (tblPublicationEntries.PubYear = tblPublication.PubYear) AND (tblPublicationEntries.AuthorFirst = tblPublication.AuthorFirst) AND (tblPublicationEntries.AuthorLast = tblPublication.AuthorLast) AND (tblPublicationEntries.Corpus = tblPublication.Corpus) SET tblPublicationEntries.PubID = [tblPublication].[PubID];

[CJ_London]

you'll have to negate the nulls by using the nz function

WHERE nz(tblPublicationEntries.Corpus,"")=nz(tblPublicat ion.Corpus,"") etc

LEFT Join is immaterial in this situation, it should be an inner join - all a left join will do if there is not a matching entry is supply nulls

compare the position of the SET and WHERE elements in your original query manually written with the one generated by the query builder, that was causing your error - otherwise the syntax was fine in principle

[lirantha]

YES! Thank you so much.