autopopulate a field, etc

[sasocreative]

I am totally new to Access, and have inherited a database from which I need to export some data. There are numerous tables, queries, forms ... I've never quite seen the like.

There is a master table called Objects which contains most of the data I want to export. It has an index number, 1 through 27,000 odd.

There is another table called Keywords. This is a list of 318 keywords. Two fields: an index number (1 though 318), and the correlating keyword. e.g. 1 = CRIME, 2 = POLITICS

There is yet a third table called Object_Keywords. This has a number that is the same as the index number in Objects. It also has the Keyword number (though not the actual keyword itself).

What I want to do is import (?) all of the keywords relevant to a given record in Objects.

I've been able to create drop-down lists through lookups and so forth, but that's not really what I want (It would mean manually assigning keywords to 27,000 records).

Any help very much appreciated.

[sasocreative]

OK, so I seem to have made more progress in the last 30 minutes than I have in the last two days.

I have made a new table through a query that more-or-less does what I want it to do as described above ..

EXCEPT

Because a given record can have more than one keyword, the new table has generated numerous rows based on the number of keywords.

Is there a way to merge those Keywords so they only appear in one field, separated, say, but commas ?

[nhorton79]

From the sounds of it the Objects_keywords table is what link the Objects and Keywords table in a many to many relationship.

You shouldn't duplicate information in a database I.e. Storing the actual text value of the keyword against each Object. The number stored links to the keywords to the object.

If you want a report of all the objects matched with their corresponding keywords you could write a query to include the object_id and a dlookup which compares the keyword_id in the object_keyword table with the keyword_id in the keyword table and returns the keyword_name.

Hopefully all this makes some sense.

If you want to post the actual table names and field names, I could write a query to extract this info, if that sounds like something you would want.

[sasocreative]

Much appreciated. I should point out that this is only a temporary need: the ultimate goal is to export the data as an XML file for upload to Wordpress (which will in turn become a kiosk installation). Once that's done, the Access database can do what it needs to do, and Wordpress do what it needs to do. I just don't want to have to manually create 27000 posts in Wordpress

[nhorton79]

That's exactly what a query can be used for: to gather data for report or export. It won't change the data just combine related data in an temporary datasheet for viewing