Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Question Unanswered: experts solution needed

    Hi
    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.

    Many Thanks

    Dugi

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •