Sorting Problem in Update Query

[Guest]

Hi,
To my Database, I enter the data via sub form on a main form to the sub
table, in which a user can have several promotions, and by using Update Query
I am applying the data changes on the sub table to the main table on which
the final level promotions and other data are stored. I am applying Update
Query however I see that the last data I had entered in the sub table is
above the other data of the user I entered before(It is sorted via Foreign
Key(P_ID) and then should be sorted by ID(PK,Autonumber) so the last data
should be below the ones entered before), so the new changes are not applied
to the main table. I also have 2 more forms/tables like the one above but I
do not have that problem in these tables although their properties are the
same. I have already checked the Update Query, Table Index Fields and Subform
Properties.
The sub table consists of ID(Primary Key indexed ascending), P_ID(Foreign
Key indexed ascending) and 2 other fields that should be updated according to
the P_ID's
I hope I could be able to explain what the problem seems to be. Thanks for
your help in advance.

 

[Roger Carlson]

Two things:

1) Sort order should have nothing to do with an Update query. Tables in
Access have no intrinsic order. Sorting is just for display purposes. Your
update query should have a Where clause which indicates the primary key of
the record you want to update.
2) I don't see why you have to write the value from the subtable into the
main table. Why not just leave the value in the subtable and query for it
when you need it?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

I am using an another Database which needs data from these tables so it is
easier and faster ,I guess, to create one table and collect the latest
data.Then I make a link of this table in which all data are stored on the new
database.That's why I do update the data on sub tables to the main
table.Would it not slow down the database if I make a link of sub tables and
create queries in that database? Is there any suggestion for the problem I am
facing below?

Thanks

 

[c120898901]

Two things:

1) Sort order should have nothing to do with an Update query. Tables in
Access have no intrinsic order. Sorting is just for display purposes.
Your
update query should have a Where clause which indicates the primary key of
the record you want to update.
2) I don't see why you have to write the value from the subtable into the
main table. Why not just leave the value in the subtable and query for
it
when you need it?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thank you all