moving one column of data into a new table during import

[ArielZusya]

I've got a spreadsheet that is imported into access and appended to the end
of a table in access. The spreadsheet contains basic info about a group of
people (first, last, id#, Seat#) and then a reference number for the set.
The reference number is next to each record in the spreadsheet. I'm using the

DoCmd.TransferSpreadsheet

method for importing the spreadsheet and that goes off without a hitch. The
trouble is this leaves me with data in a less than efficient and useful set.
I'd like the reference number to be stored into a seperate table (and
preferably only stored in that table once) and then have each of the imported
records relate to the one reference number. I'm struggling with where to
even begin on this. In case that explination didn't make sense I'll try to
illustrate:

spreadsheet contains:

FirstName LastName IDNum SeatNum RefNum

which means records would look something like this for a given set:

Joe Smith 2123 1 32RFE112
Jane Doe 3224 2 32RFE112
Jack Sparrow 4256 3 32RFE112

Etc.

I'd like to import the FirstName, LastName, IDNum, and SeatNum into a table
in access and then have another table linked to this table with a one : many
relationship which contains the corresponding RefNum.

Is there a way to make this happen using VBA? I suppose I can manipulate
the data using queries but it seems a bit more bloated than it needs to be.
Your help would be greatly apprecaited! Thanks!

 

[John W. Vinson]

Is there a way to make this happen using VBA? I suppose I can manipulate
the data using queries but it seems a bit more bloated than it needs to be.
Your help would be greatly apprecaited! Thanks!

Not at all. Queries are the *first* choice of tools for manipulating data in
tables, not the last!

You can have a table Refs with RefNum as its Primary Key. After importing the
data using your existing query (and keeping the RefNum as a useful foreign
key!), you can run an Append query appending the RefNum into Refs:

INSERT INTO Refs(RefNum)
SELECT DISTINCT RefNum FROM yourtable;


John W. Vinson [MVP]

 

[ArielZusya]

OK... That's very cool. Thanks for that. I do have a few questions. First,
generally, is there any advantage to embedding queries or can I leave it as a
standalone query? Second, is there a way to supress the verification and
result feedback when I perform the query (when it asks me if I'm sure and
then after reports back how many records were insterted)? Third, now that
I've got this data where I want it, is there an easy way to point a form to
open the record associated with the ref number I just inserted? I'd like to
save a description with each ref number. Thanks again for all your help.