So ignorant of Access that I'm not even sure what I'm asking

[lokimotive]

So, despite using Access at my job to build a large table of records for the past year, I am completely unfamiliar with how the thing works, or even, to a large extent, what it does. I've been searching the Internet for a few hours until I realized that I don't even know what I should be asking. I defer to you for some advice.

Here's my situation: We've built a large table with about 700 rows and 30 columns. Each row represents a different record for an audio recording, each column represents various characteristics for each audio recording (title, length, author, publication date, etc.).



Right now, I need to insert two new columns containing some new data from another source: static URLs for each record in a library catalog, and their bib number. I have a tab delimited file formatted as such: URL[tab]Bib number[tab]Title[tab]publication date.

Theoretically, the Title and publication date should match their respective cells in the Access database, and I would like to match up the bib number and URLs to their respective titles. The titles are not in the same order in the tab delimited text file as they are in database.

So is there anyway I can import this text file into access and match up the titles? How could I possibly do it?

[TheShabz]

is there a common unique identifier between your access table and your file?

[lokimotive]

Yes, the third and fourth tab delimited portion, the title and publication date, should be the same both in the text file and in the database.

[ajetrumpet]

you could write a short function to do it, but you may want to use the interface instead. maybe try a DDL query to add the fields first and import your text file to a new table, then append the recs using a dlookup. three steps, but that's not bad.

for a ddl query, try:

alter table YOURTABLE

add column YOURFIRSTFIELD text (255), YOURSECONDFIELD text (255)

for the final query, try:

update YOURTABLE set [new field 1] = 

dlookup("new field 1 from import", "oldtable", "[commonfield] = '" & [commonfield] & "'"), 

[new field 2] = dlookup("new field 2 from import", "oldtable", "[commonfield] = '" & [commonfield] & "'")

[TheShabz]

ok. so you will go ahead and import the tab file into access. it should be a fairly simple procedure, Access' wizard is pretty good on imports. when you do. open up a query in design view and throw both tables in there. find the matching identifiers and join them together by clicking and dragging from one to the other. then double click the * in the first table to send it to the bottom of the window (* means all records and fields). Then double click on all the fields of the imported table except for the 2 you joined on (we dont want duplicate data). change it to a "make-table" query and set a table name. hit run and all the ones in your main table that have a match in the imported table will be sent to the new table. then you want to go in and do some duplicate checking to make sure there arent any issues.

thats basically the gist of it. for more detailed info, get started and google or ask. hope you find your way through.

[lokimotive]

Thanks a lot! I tried TheShabz' method, but I got an error telling me that it can't join on a Memo, OLE or hyperlink object. The title, the most unique, unfortunately needs to be a memo because the majority of them are over 255 characters.

If I tried ajetrumpet's method, would I run into the same problem?

[TheShabz]

if you tried the other method, you would get your memo field truncated to 255 characters. If there was an ID field, your task would be much easier.

[lokimotive]

I now realize that. Thankfully, there is something that can function that way, it's just not in the text file I have now.

Anyway, thanks for the tips.