General Rules for 'Appendable' Select Queries

    Hi all, and thanks again in advance.


    Can anyone direct me to (or just tell me) the general rules for creating simple queries in Access that allow appending records? (I'd like to know where this is in Access' own Help file, because I know it must be there, but I'm hanged if I know how to ask for it.)


    For example, I have a simple two-table query, joined on one column. The column is part of a Key in each table (but not the whole Key in the second table). Table_1 is "Proposals" and Table_2 is "Proposal_Revs". The Key to the first is PropNum, and the Key to the second is PropNum & RevNum.


    I've joined other tables before and happily discovered that I could use the resulting queries to add records (had not known before that that was possible in Access, aside from an 'Append Query', which is NOT what I want here) ... but I can't do that with this query. I'm wondering what the rule / rules may be which govern that, but I can't find it in Access Help.


    Thanks,
    Chris

    Re: General Rules for 'Appendable' Select Queries


    Chris,


    I think what you are asking is' What are the rules that Access uses to determine when you can edit the datasheet that is based on a query such that the underlying tables are updated?'


    From experience and something I read earlier today- but not sure where- is that is depends on the number of indexes and relationships. If Access can work out how to do it it will let you- if it can't it wont!


    Not a great answer, but a few tests should help.


    Take a simple query that selects all orders for each client in a table. If you now try to edit data you should succeed since the query design is simple and the tables are only linked by one field. However, if you have data coming from 5 tables, with left and right joins Access will normally (but not always) fail to allow the edits.


    I fear I have not answered your question, but suspect that there is no clear answer.


    I have not tried to add records to the query datasheet, this could present more problems since the linked fields would need some form of validation, I guess.


    Sorry for not having a clearer answer, I will check back later to see if anyone has got a better answer!


    A.

    Re: General Rules for 'Appendable' Select Queries


    I used to run into that problem often. A work around is to make a table from your select query and use the table in the append. A macro can run the steps sequentially for you.


    Hope that helps,

    :guitar: - Scott

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login