Thread: experts solution needed
04-19-04, 08:43 #1Registered User
- Join Date
- Apr 2004
Unanswered: experts solution needed
I need help with the following:
I have a linked table "searches" and connection to sage "SALES_LEDGER".
I have a query which pulls out every record which has "enviro" tick box =TRUE from the "searches" table from a certain date which i type in a form.
OK all that is working fine but what I need is something else.
How to create another query/macro or something else which will go to table "searches" after I run the first query and set the value of a check box "exported" to TRUE for all the records that I pulled out with the previous query? so the next time i run the query I mentioned earlier I can omit all the records that have "exported" check box value = TRUE and pull out only records that have not been exported before.
Oh my God, I know it sounds confusing and I don't know how else to explain but if you think you understand please give me an answer.
04-19-04, 10:56 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
you already have a query that finds the records you want to play with.
copy/paste it to a new query
open your new query in design view and menu: Query¦Update Query
...mess with it for a while and you'll soon work it out.
one issue!!!! doing this with queries means you run the risk that your export run includes N records, someone then adds a new record that unfortunately matches your export criteria, and then you run your update run ...hitting N+1 records - one of which has not been exported!
so you have choices like
--locking the whole source table
--adding a third SELECT query after the update and comparing the number of records in there with your first export run. wrap all three queries in a transaction and unwind the transaction if you bump into the N / N+1 issue
--or using a recordset
source recordset is based on the select for your export query
and you step thru the records
..exporting one-by-one (i.e. copy to a second recordset)
..and flagging the record as exported at the same time.
once you have filled your recordset, it will no longer "listen" to the source table so you wont get the N / N+1 issue.
izycurrently using SS 2008R2