Questions about key fields in appended tables 1

[KerryL]

If I'm appending 10 tables into one master table for the purpose of compiled reporting, how important is it to worry about key fields and autonumbering?

Should I set up the Master table field properties exactly as they are in the individual DB's? Or should I append the autonumbered key fields from the individual tables into a Master table field with a "number" data type?

Or isn't it that big of a deal if I'm just going to use the compiled Master table for reporting purposes and do no updating of records in it?

Also, since I'm compiling the data in 3 linked tables from 10 separate databases, I assume I should be sure the linking is identical in the Master DB as it is in the original DBs?

What's the accepted school of thought on importing autonumbered fields and key fields from multiple tables into one Master table?

Thanks for any advice you can give me,
KerryL

 

[BNPMike]

It depends on what role the autonumbered fields play. Logically if you are aggregating several tables for reporting, you don't need autonumbering so - yes - use ordinary numbers for this data. Autonumbered fields are not limited to joing to other autonumbered fields.

My feeling is if the numbers are important then it is safer to import them into a non-autonumber field so they don't get changed although probably a duplicate autonumber would probably just be rejected.

 

[willir]

...but what happens if the number from one table being appended into the master table is the same number from another table also being appended into the master table?

Say you are merging three customer tables from three different databases into one master customer table.
[tt]
AutoNumber DataBase Customer
1 DB1 Smith and Sons
2 DB1 Gone Fishing
1 DB2 SF World
1 DB3 Weed Puller
[/tt]

If I understand you correctly, you are merging data from diffrent tables. In the above, there are three records with the primary key of "1" -- two records will be rejected if the unique index / primary key is correctly assigned or you will have three records with the same identifier.

The "bottom line" here is that each record should have it's own unique primary key. If you can accomplish this with your current data great. If not creating a new field as your primary key will work. You can still reference the "important" number within the record, and be confident that each record is unique.

 

[KerryL]

Thanks for your feedback, guys...

I agree with what BNPMike said because the master table that results from merging data from multiple DBs will be used only for reporting.

Willir, you are correct about the possibility of identical key fields being merged into one column. This is handled by the fact that each remote DB has a site code which is used in their key fields. For example, Lansing=22 & Detroit=35, so the key fields for Lansing tables begin at 220001 and the key fields for Detroit begin at 350001. When the tables are merged, data in the autonumber key fields end up in a regular number column (without dupes) and can still be used for linking to records in 2 other merged tables.

So what I did is merged the autonumber key fields into regular number fields but created a new autonumber key field in the master table so each record in the master table still has a unique identifier. As I said above, links are maintained by the unique autonumbers created in the separate databases and merged into reg number fields in the master tables.

Does that make sense?

Thanks again for your feedback, I appreciate it. Sometimes just talking through these situations help to clarify them, and your input was valuable.

KBL

 

[willir]

Good job. And good thinking on using the site code.

As you become more familiar with the data, you should be able to judge if you still need the new autonumber key or not.

My only concern was that merging data may generate duplicate keys, or result in failed imports. You seem to have addressed this issue.