Find the right record to append from

[gwboolean]

I am attempting to append 2 fields from one table and one record to another table. I have been using DLast("field","table") in an append query to get this done. However, it blows up occasionally and points to the wrong record. I am sure there are better ways to do this, I just don't know what they are.
How does one easily and reliably select the right record to use for an append?

[orange]

Instead of DLast (and DFirst), it is recommended to use DMax() and DMin().
See http://www.techonthenet.com/access/f...omain/dmax.php

You do understand the difference in an Append query and an Update query, right?

http://www.techonthenet.com/sql/insert.php

http://www.techonthenet.com/sql/update.php

[gwboolean]

I do understand the difference betweent append and update. I am wanting to create a new record in one table using some of the data from a selected record in another table (append). I am not wanting to put data into an existing record (update). However, DLast is the only way I have been able to understand to select the last record from a table so that I can append (some of) that record to another table.
I have tried other strategies like a get record command but that just somehow gets beyond me and I am going to look up the DMax and DMin and see if I can figure out how to use them for this.

[orange]

There have been inconsistencies reported with DFirst and Dlast. People say to use DMin and Dmax.
If you have a field in your record such that the "last" record is the one with the MAX value, then DMax should work for you.

Do you know the unique identifier of the record you want to get the values from? How do you identify the record you're looking for?

As for your intent to append a new record to a table using fields from other tables, you may be able to do it with a query.
Can you give a specific example with fields and tables involved?

What is the make up of the record you want to append?

[gwboolean]

OK, I see how DMax and DMin work. That won't do for this field. It is a Primary Key, but it is a 10 character text field that is made to be an intelligent number ID for a number of tables. It would have been nice to just use the auto number, but that wasn't desired for the PK here.
The values that go into the fields are a set of numbers and letters, each pair of which have context. They are not, however, used to do anything in the database at this time but it is the intent, in the future, to be able to use these to define searches within the database.
The database is made up of several modules doing a number of jobs, but the primary activity is a document management system with change control.
I am working through queries (to the best of my abilities) to do almost everything I am trying to do.
For this database I have a table that is used to build an intelligent part/document number. The number in this table, once built, is then appended to any one of several tables that have that number assigned to the record for the particular thing created in that table. Now if this sounds like some Rube Goldberg style of getting your created number where you want it...... well it was the best I could come up with. I am sure that this isn't even close to the best way to get this done.

[gwboolean]

I had heard that DLast and DFirst was unreliable. An approach that comes to me now that might clean this up would be to eliminate the use of the intelligent numbers as keys, use them just for what they are and create a set of auto number keys to work between the tables. That really sounds like a lot of work though. I really would prefer to make this work.

[orange]

Yes, I agree generally. Too often people try to put too much intelligence into a "key". For some reason they feel they are helping (even outsmarting) the system so to speak. The autonumber approach to PK is a very successful approach. This PK only guarantees the uniqueness of each record in the table. It is for the use of the database management system(system use). It does not mean sequential number, just unique number. If you need a number for your (the user) use, then make another field for that purpose.

There is an excellent article attached to the post here
http://forums.aspfree.com/microsoft-...es-208217.html
that makes a key point about one fact-one field. Don't concoct "intelligent keys" and make the system use them. If you want them, do so at your peril, but don't use such things as PK.

You will find that the "best"(most useful/friendly and operationally sound) databases are those built upon well designed tables and relationships. It's difficult to tell that to those just starting out who have a keen interest to get into Forms and buttons etc. A good data model with a good naming convention and some clear logic will let you sleep at night instead of debugging some work around that was quick and dirty and considered "good enough".

Enough soap box.

If you do proceed, get your structures set up before too much coding occurs.

[gwboolean]

You mentioned the accdb versus the MBD route. While I see the two extensions I really have no idea what all the differences are (another thing to look at). The intelligent number fields are unique as well, which is why I went ahead and used them for the key. I never dreamed they would be that much trouble to work with. Anyway I scrapped the whole setup, as you might have guessed it had something to do with problems with queries and working with the key fields I had and DLast. It was starting to work pretty well except that it still would just blow up now and then. But yeah, my keys are now just for the tables. I have figured out that I can have my own fields for my work and the database can have it's own fields for its work. Not an easy concept to buy into, but after this I think I might be starting to learn a little about that.
Thanks for you time.

[orange]

No problem, glad to assist.

mdb --> is a format for Access databases 97, 2000, 2003 (there are differences but this is general)
accdb--> for Access2007 and 2010

There is an option to save an accdb to an earlier version (but may not work if certain 2007/2010 features have been used)

mdb can be read by Acc2007/2010 (so I've been told)
accdb format is not readable from/by 2000/2003

There is more to this I'm sure, but that's the 30000 feet version.