New table doesn't "match"

[PATRICKPBME]

I created a table with about 100 records. I then needed to insert another field for attachments, but since the table was getting crazy with so many fields, I thought I would create a separate table just for documents/attachments. In the "main" table I have had occasion to delete records and noticed that the ID numbers were deleted along with the record. I think I read somewhere that this was not a concern.



I thought the easiest way to create my new table would be to cut and paste the master into the new and then delete all the fields I did not need. However, all of the ID numbers were put back in the new table and the records in the new table don't match with the ID numbers and records in the master table.

What am I missing here? Thanks in advance.

[ranman256]

if your ID numbers are autonums, then you have to take steps.
The tMain.ID is autonum, then you copy the table for tAttach table.
edit tAttach table design, then change the tAttach.ID from auto to long.
This is a related table and you need to keep the keys to the master table.

how many fields does the tMain table have?

[JoeM]

Is your ID field an AutoNumber field? If so, you really have no control over what they are set to.
So, if you need to match up your two tables, you cannot have the ID field be in an AutoNumber field in both tables, as you will never be able to get them to synch/match up properly.

However, it sounds like you might have a design issue with your database, especially when you talk about having too many fields in your table. Normally, you shouldn't really have a ton of fields in any one table. You usually have multiple table broken up into logical tables. A key indicator that a field probably should not be in a table if if you have a lot of duplicating values or blanks in any one field.

For example, if you had an Invoice table, and you have buyer address in it, and every time a person buys something, the address is written in it. So if John has 10 records in the Invoice table, his address appears 10 times. What you should really have there is a separate table for Addresses.

Here is a good link on database design (pay special attention to the rules of normalization, as they often guide how you want to set up your tables): https://support.office.com/en-us/art...6-d2331f158280

[PATRICKPBME]

if your ID numbers are autonums, then you have to take steps.
The tMain.ID is autonum, then you copy the table for tAttach table.
edit tAttach table design, then change the tAttach.ID from auto to long.
This is a related table and you need to keep the keys to the master table.

how many fields does the tMain table have?

Ok, I got to the part of changing the tAttach.ID. Drop down menu does not give me a "long" option. Gives me text, memo, number, date/time, currency, autonumber, yes/no, OLE object, hyperlink, attachment, hyperlink and lookup wizard. Maybe "number" is what I want?

[JoeM]

Yes, choose "Number". then "Long Integer" is a Field Size option for the Number data type.