automatically adding new fields

[Guest]

I'm new so the answer might be staring me in the face.

My users will be importing a new table weekly to my databse.
That table doesn't have all the fields that my form needs to see.

It's just going to have three fields firstName, lastName and SSN for each
record...but I need to also allow my users to add (via a form) data to each
record like the birthdate, and the color of the eyes.

How do I allow my users to automatically add more fields to each record
included in the imported 3-field table?

(Do I use a 'makeTable' query based upon the 3-field table? If so how do I
get the additional fields into the new table? All the records from the
original 3-field table dissappear when I add any other fields to the
'maketable' query.)

 

[Guest]

yep, it was staring me in the face.
In the 'makeTable' query I have the 3-field table and a 'resource' table I
will keep in my database (which contains all the other fields that need to be
added for each record).

I joined the ID fields in these tables and set the relationship of that join
to 'include all records from [3-field table] and only those from [resource
table] where joined fields are equal.

 

[John Vinson]

I'm new so the answer might be staring me in the face.

My users will be importing a new table weekly to my databse.
That table doesn't have all the fields that my form needs to see.

It's just going to have three fields firstName, lastName and SSN for each
record...but I need to also allow my users to add (via a form) data to each
record like the birthdate, and the color of the eyes.

How do I allow my users to automatically add more fields to each record
included in the imported 3-field table?

(Do I use a 'makeTable' query based upon the 3-field table? If so how do I
get the additional fields into the new table? All the records from the
original 3-field table dissappear when I add any other fields to the
'maketable' query.)

Third option:

Have a table in your database with all of the fields that you need.

Rather than importing the external file as a new table, import it
*INTO* this stored table. It will fill in the three fields, leaving
the additional fields null.

John W. Vinson[MVP]

 

[John Vinson]

yep, it was staring me in the face.
In the 'makeTable' query I have the 3-field table and a 'resource' table I
will keep in my database (which contains all the other fields that need to be
added for each record).

I joined the ID fields in these tables and set the relationship of that join
to 'include all records from [3-field table] and only those from [resource
table] where joined fields are equal.

See my answer to your earlier message. This will work but in a sense
it's a more complex solution than is needed.

John W. Vinson[MVP]

 

[Guest]

Good option. Thanks much, John

 

[Guest]

....but
When I import into a table it just adds records to the table I am importing
into. I need to overwrite the records currently in the table so I don't end
up with duplicates. Am I back to the first option:
-replacing a table and then requiring the user to run a 'makeTable' query
that adds new fields because it is joined with a table that includes those
new fields?

If there's a cleaner way, please let me know.
Thanks 'gain

 

[Guest]

....OK: I found out I can set the properties of the table I am importing
into...to prevent duplicates. That works.