Import Excel linking first id with following lines of data

[clydesur]

I'm importing an xls into a table and want to link the town with the data
lines following it. Here is an example of the data imported.

Field1 Field2 Field3
Language Minutes Calls
303002 TALLAHASSEE
SPANISH 36 1
CANTONESE 8 1
HAITIAN CREOLE 4 1
Lang Count: 3 48
303003 COUNTRYSIDE
SPANISH 86 17
Lang Count: 1 86
303004 ST PETERSBURG
SPANISH 884 90
HAITIAN CREOLE 250 18
VIETNAMESE 103 12
GERMAN 34 1
CANTONESE 30 2
FRENCH CANADIAN 29 1
Lang Count: 6 1330

How can i get each line under a city to identify with that city when it is
not in the same table line?

 

[Piet Linden]

I'm importing an xls into a table and want to link the town with the data
lines following it. Here is an example of the data imported.  

Field1  Field2  Field3
Language        Minutes Calls
303002  TALLAHASSEE    
SPANISH 36      1
CANTONESE       8       1
HAITIAN CREOLE  4       1
Lang Count:     3       48
303003  COUNTRYSIDE    
SPANISH 86      17
Lang Count:     1       86
303004  ST PETERSBURG  
SPANISH 884     90
HAITIAN CREOLE  250     18
VIETNAMESE      103     12
GERMAN  34      1
CANTONESE       30      2
FRENCH CANADIAN 29      1
Lang Count:     6       1330

How can i get each line under a city to identify with that city when it is
not in the same table line?

Don't think you can without code. In short, your data is not
normalized because you have summary data in there. (It's redundant
anyway - you can create a query or report to do all that summarizing
for you anyway in Access...

The problem is that Access expects all data for a record to be on the
same line, and in your Excel file, that's not the case. Since the
totals are derived, is there a way you can import just the data
without the summary info? That would be the easiest thing to do.
Otherwise, you would have to write a query against the attached table.

Off the top of my head, you would have to see if Language holds a
numeric value, store it somewhere, and then basically fill down into
all the records where the Language is null... not too hard, but before
we go too much further, how many of these do you have to process? If
there are a lot, it would be worthwhile doing this in code.
Otherwise, if this is a one-off, just fix the file and go on.

OTOH, if you have to process a lot of files like this, you might need
to write the data to a temp table, update it with code, and then
import those results to a permanent table. Maybe (Language, Minutes,
Calls, ZipCode). Then you can summarize that quite easily.