General discussion

  • Creator
    Topic
  • #2272949

    Question on Access 2000/XP Query

    Locked

    by hunterzh ·

    In Access 2000, I build two Tables, for example, one for Items Basic Info: part number, description, and so on; another for Structure: ID, parent part number, child part number, Qty.
    Then I have a Query, select parent part number, its information from Basic Info table; based on this query design a Form.
    Now I find one problem, let?s say, 1 parent item has 3 child items; then in the Query, Select: parent part number, description, other basic info, it shows number of records 4. After I modify a little, Select Distinct: parent part number, description, other basic info, it shows number of record 1 (this should be correct), but then I cannot add new records: the add record button becomes unusable.
    How can I make the records distinct, while in the meantime can add new records?

All Comments

  • Author
    Replies
    • #3299144

      Reply To: Question on Access 2000/XP Query

      by jaqui ·

      In reply to Question on Access 2000/XP Query

      insert records is a different function from reading records.
      ie:
      select from basic where parent=1 && child=4

      insert into basic part number, description, ect
      insert into srtucture parent part number child id qty….

      you cannot run an insert on a result. ( can’t select then take result and insert afer. )

      you can get childid from result and increment it by one before running the insert.

      • #3313659

        Reply To: Question on Access 2000/XP Query

        by jaqui ·

        In reply to Reply To: Question on Access 2000/XP Query

        when you make distinct you are creating a result set.
        in effect creating a table with just the information you were looking at in it.
        ( distinct is a select, to separate the viewed data from the saved data. )

        this is where you can format the data to print it on a letter etc.

        the app has to put that into a temprary table, which is a read only table. when you have only one record in a distinct from the database you are no longer actually working with the database. you are working with a temporary table / result set.

        this is a database thing not just an access thing.

      • #3322748

        Reply To: Question on Access 2000/XP Query

        by hunterzh ·

        In reply to Reply To: Question on Access 2000/XP Query

        Poster rated this answer.

    • #3313676

      Reply To: Question on Access 2000/XP Query

      by hunterzh ·

      In reply to Question on Access 2000/XP Query

      In Access 2000, in Query, sometimes we can add records; while sometimes cannot; the problem which I described, is somewhat strange: just after I write “distinct”, we cannot add new records, before that we can add new records.

    • #3322749

      Reply To: Question on Access 2000/XP Query

      by hunterzh ·

      In reply to Question on Access 2000/XP Query

      This question was closed by the author

Viewing 2 reply threads