Importing 33 fields into a 58 field table (and changing data order

[tstew]

Hello,

I need to import data (csv in this case) that has 33 fields into a table
that has 58 fields. The data is not in the correct order. So, my idea is to
import the new data to a table and use a query to map to the correct order.
My problem is how to include "spacer" columns in the query to map correctly.
As in: Field A, Field B, blank, Field C, blank, blank, Field E... and so on,
ending up with a query that has 58 columns to match the main table. Is that
clear?

Any ideas?

Thanks,
Mark

 

[Jeff Boyce]

If you use your approach (a query to "map" to your permanent table's
fields), you do not need to accommodate "blank" columns. Your query/map
will put something somewhere. ?Nothing to put? !Don't include it!

On a different topic, an well-normalized relational database table with 58
fields is ...extremely rare! More than 30 fields is pretty rare.

If you'll post a description of the fields & their contents, folks here may
be able to offer suggestion that would result in better performance from
Access (which is optimized to work with well-normalized data).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tstew]

Hi Bill,

I'll include fields below, but first, I don't follow your explanation. I'm
going from 33 fields to 58 in the query. My (very simple) understanding is
that I have to get the smaller number of data fields in the right order and
insert some place holders to get the new records up to 58 fields. Otherwise,
when I paste the new data (from the query) to the old table I won't get the
right data in the right fields. Am I missing something?

I get the data from a vendor, we add about 500 records a week and move or
delete about 350 records a week. I have added several fields near the end
(you can easily spot them (bad naming conventions) for queries I run and
mapping with MS Streets and Trips. The data with smaller number of fields is
a different vendor that I use to fill in missing records from the main
vendor.

The fields in my table (hope this helps):

Address, Text
City, Text
Zip, Text
TG,Text
HOEX, Text
Trustor, Text
Owner, Text
Benefry, Text
B_Phone, Text
Tax_Value, Number
Tx_Yr, Text
Prchs_Date, Date/Time
Amount, Number
TD1_A, Text
TD1, Number
TD1_D, Date/Time
TD2_A, Text
TD2, Number
TD2_D, Date/Time
TD3_A, Text
TD3, Number
TD3_D, Date/Time
TD4_A, Text
TD4, Number
TD4_D, Date/Time
TD5_A, Text
TD5, Number
TD5_D, Date/Time
TD6_A, Text
TD6, Number
TD6_D, Date/Time
Use, Text
YrBlt, Text
Story, Text
Legal, Text
ASSPAR, Text
NOD, Text
Loan, Text
TDID, Text
Remarks, Text
T-S_No, Text
Trustee, Text
T_Phone, Text
Sale_Date, Date/Time
Site, Text
NTS, Text
L_Date, Date/Time
Regid, Text
SqFt, Number
Rooms, Text
Lot, Text
County, Text
CDate, Date/Time
Status, Text
Post, Text
OpenBid, Text
SoldAmt, Text
Lead Type, Text
Map, Text
Research, Text
REO, Text
3rd Party, Text
Owner State, Text
Telephone Number, Text
On DNC, Text
Export to ACT, Text
Notes, Memo
Last Visit, Date/Time
Z Value, Number
Pic Location, Text
Interest Quotent, Text
Our Value, Number
Date our FMV, Date/Time
Crier, Text
3rd Party Buyer, Text
FR Value, Number
Cancel, Text
Opening Bid, Number
Winning Bid, Number
Vacant, Text

 

[Douglas J. Steele]

Rather than import the 33 field table, link to it. Then, create a query that
appends the linked table to the ultimate table. No "place holders" should be
required.

And Jeff's correct: your table definitely does not look normalized. Field
names like TD1_A, TD1, TD1_D, TD2_A, TD2, TD2_D would appear to indicate
that you have repeating groups, which are definitely not recommended.