Editing memos through queries (1 Viewer)

[gunslingor]

Problem:
I have one table with 10,000 records. The table lists a hostname (i.e. computer name) and all the associated "windows services" running on that machine along with fields related to description of service and setting of service. So the relevant fields in the table looks like this:
HOSTNAME SERVICE NAME UNOFFICIAL DESCRP OFFICIAL DESCRIP
host1 service1 memo memo
host1 service100 memo memo
host1 service56 memo memo
host2 service56 memo memo
host2 service93 memo memo
host2 service10 memo memo
host3 service1 memo memo
host3 service245 memo memo
host3 service382 memo memo

The "official description" has been inputed into the DB automatically. At this point, I need to enter my "unofficial descriptions". I have created a query that tells me I have about 400 unique "service names" which require the unique "unofficial descriptions". The problem is that I need to apply these "unofficial descriptions" to all occurances of that service name. I cannot seem to do this in a quiery, probably because the "DISTINCT" keyword only pulls distinct records and leaves duplicates behind. I need DISTINCT to pull the distinct records, then when I enter the "unofficial description" it needs to enter this at each place the service is listed. Any suggestions?

 

[Atomic Shrimp]

DISTINCT queries are not updatable - because they group multiple similar records into one output row, where appropriate. There isn't any way to update one row in a summary-type query and have the updates cascade out to all the constituent rows (at least, not without writing code to perform the process).

If you have a number of different unofficial descriptions, each of which applies to multiple rows in your table, you'd be better off storing them in their own table, then just putting a foreign key field in the big table - so each row there just points to its correct unofficial description.

(this carries the additional advantage that corrections to the unofficial descriptions only need doing once, in one place).

Also, there are a whole bunch of scenarios in which Access will truncate the display of a memo to the first 255 characters - I think this also goes hand in hand with being non-updatable in these cases. There's a list here:
http://allenbrowne.com/ser-63.html

 

[gunslingor]

I was afraid of that. I had thought of your solution, to use a separate table and reference by key, but this would still require me to go through the entire table and assign number keys to each of the 10,000 records which would not really accomplish anything since I could just as easy put the descriptions in via cntrl C or V.

What I could do is delete the unofficial description field from this table, then creat a table with the unofficial description and service name (service name being primary key). Then just pull this data for forms and reports. I didn't want to do this because it will make the DB design inconsistent. Most of the data gets pulled into the DB via a text parsing modul I created, which is greatly simplified with less tables and key references in tables.

Any other suggestions on how to cascade field inputs by identifying another field?