Web Development·11,947 discussions

Question on Access 2000/XP Query

Locked

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?

Topic

Reply To: Question on Access 2000/XP Query

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.

Reply To: Question on Access 2000/XP Query

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.

Reply To: Question on Access 2000/XP Query

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

Poster rated this answer.

Reply To: Question on Access 2000/XP Query

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.

Reply To: Question on Access 2000/XP Query

In reply to Question on Access 2000/XP Query

This question was closed by the author

[Author]

Replies