Edit fields in local table linked to PassThrough Query

Question

I have been tasked to create a query that will pull records from Data Warehouse with a given criteria that will be used by data-entry level employees with read only access.

Once they have used the query to identify a record that requires action in a different system, we would like them to enter a note in the DB of what their action was, date, etc.

Since they do not have write ability to the Data Warehouse, my thought was to have a local table that links to the PassThrough via the ID.

I've set up a new query that links and have a crude update query to add the IDs (one problem at a time) but still cannot edit the local table field. Any suggestions? (I'm open to other solutions as well, but note: This is a federal government agency and I have limited access to the data warehouse, best solutions display the warehouse data, and use linked local tables for data entry).

Happy to provide more info if necessary. Thanks!

Answer 1

Yes, you can achieve this with a local table as you have already mentioned. However to add a "Note" to your local table, you either have to have a form, or both queries linked as (master > child)

Your local table's design will look like this:

1. Warehouse_record_id > Int or the unique id from your passthrough query
2. Note_ID > AutoNumber
3. Note > String/Memo
4. Added_date > DateTime
5. Updated_date > DateTime
6. Added_by > string
7. Updated_by > string

now if you want to add notes, you can simply create a From from your passquery and add your local table as subform. ( i guess you know this already)

OR:

open up the passthrough query> goto properties >

• Sub DataSheet name > your local table
• Link master field > passthrough unique id
• Link Child field > local table_passthrough_unique_id

now when you open the passthrough query you get the + sign for each record where you can add notes. Try this and let us know