Web Development

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

General discussion

Locked

Question on Access 2000/XP Query

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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Jaqui In reply to Question on Access 2000/X ...

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.

Collapse -

by Jaqui In reply to

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.

Collapse -

by Hunterzh In reply to

Poster rated this answer.

Collapse -

by Hunterzh In reply to Question on Access 2000/X ...

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.

Collapse -

by Hunterzh In reply to Question on Access 2000/X ...

This question was closed by the author

Related Discussions

Related Forums