| Author |
 Topic  |
|
|
cardella
Starting Member
5 Posts |
 Posted - 05 March 2003 : 13:45:30
|
Having a problem when importing a file (.txt or .xls), seems that even though I set the column to be indexed and duplicates ok, I am unable to see the option in that column when doing a query. (I just get is null, is not null) I can import other files with no issues when doing the exact same type of import. I have even re-generated the file as a text file and still get the same results. I thought the issue was with the actual file, but I have tried saving it as a .txt and other formats with no luck. I even sent the file to a college and we tied importing it into his system and get the exact same results. I am guessing that if there are too many unique records (1268) this may be the cause. Although I have another file that has (761) unique records and it works fine. I change some of the data and limited it to 100 records and it seemed to work fine? If this is the problem, can this max # be changed? I found a way around it by copying a coulmn that was searchable , then pasting the data into it after the fact. This works until I compact & repair the Db, then its back to the is null or is not null options. I am not an expert at access, but I follow the same procedures as when I import other tables, but still get this weird result. I have verified that the properties are valid in the table, and am at a loss for what is happening. The data in the column is text, and coded as text. Any help would be greatly appreciated.
Thanks,
Sal
|
Edited by - ruirib on 05 March 2003 14:04:34 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 March 2003 : 04:05:46
|
Well I'm pretty confident that it's not too many unique records, as database record field values will tend to be unique, and Access is *way* more than capable of handling 1300 records. I imagine for anyone to help, they'll need more clues - maybe a *LINK* to a sample of the data (please don't post it all here), and a step-by-step guide for how to reproduce the error (best of all is to use numbered bullet points, eg
1)open access 2000 sp1 in windows xp pro sp1
2)File|Get data from external sources|import from...|...
3)On the wizard tick this box, that box the other box, and hit next...
4)...
you get the idea.)
Also provide a link to a copy of the mdb you're trying to import into - this may be relevent
By doing this, it will give people a chance to reproduce the problem, if they can, and share their findings. People here genuinely want to help, and this will help them to help you :-) |
 |
|
|
cardella
Starting Member
5 Posts |
Posted - 06 March 2003 : 13:11:09
|
[Thanks for the pointer, I am new to forums, so I will attempt to attach the files. I removed any confidenttial data, and reverified that the file still does not work properly. ]Originally posted by pdrg
Well I'm pretty confident that it's not too many unique records, as database record field values will tend to be unique, and Access is *way* more than capable of handling 1300 records. I imagine for anyone to help, they'll need more clues - maybe a *LINK* to a sample of the data (please don't post it all here), and a step-by-step guide for how to reproduce the error (best of all is to use numbered bullet points, eg
1)open access 2000 sp1 in windows xp pro sp1
2)File|Get data from external sources|import from...|...
3)On the wizard tick this box, that box the other box, and hit next...
4)...
you get the idea.)
Also provide a link to a copy of the mdb you're trying to import into - this may be relevent
By doing this, it will give people a chance to reproduce the problem, if they can, and share their findings. People here genuinely want to help, and this will help them to help you :-)
[/quote] |
|
|
|
cardella
Starting Member
5 Posts |
Posted - 06 March 2003 : 14:11:08
|
I could not attach the file, and I looked it to the help and found that attaching files is not allowed.. so I recreated a simple XL spread sheet with only 1 column (defined as number) and just inserted numbers 1 through 1001 (1, 2, ,3, 4, 5, etc) on separtae rows thus creating a 1 X 1001 or more matrix. I then saved this as a XL file.
1)Now I create a new db ... db1.mdb
2)select "NEW TABLE" button from within the db1.mdb
3)select the "IMPORT TABLE" hit "OK"
4)select the XL file I created earlier (1 X 1001 matrix)
5)select first row contains column heading
6)select in a new table
7)verify field name is correct
8)select Indexed... Yes (Duplicates OK)
9)Data type... is grey out as a double (not changeable it knows the column of numbers are numbers for the XL file format)
10) click... Next button
11)choose... Let access add primary key
12)click... Next
13)Import to table... Enter a table name
14)click... Finish button
15)Access come back with a successful inmport message
16)click...OK
17)open the table created instep 13
18)click the filter by form icon on the menu bar
19 and this is where I get Is Null or Is Not Null option, not the actual indexed #s 1-1001.
If you recreate the XL file but only have 1 through 1000, and follow the steps above, it works perfectly fine.
Seems to be a limitation as a function of # of rows not even total records (Rows X Columns)
quote:
Originally posted by cardella
[Thanks for the pointer, I am new to forums, so I will attempt to attach the files. I removed any confidenttial data, and reverified that the file still does not work properly. ]Originally posted by pdrg
Well I'm pretty confident that it's not too many unique records, as database record field values will tend to be unique, and Access is *way* more than capable of handling 1300 records. I imagine for anyone to help, they'll need more clues - maybe a *LINK* to a sample of the data (please don't post it all here), and a step-by-step guide for how to reproduce the error (best of all is to use numbered bullet points, eg
1)open access 2000 sp1 in windows xp pro sp1
2)File|Get data from external sources|import from...|...
3)On the wizard tick this box, that box the other box, and hit next...
4)...
you get the idea.)
Also provide a link to a copy of the mdb you're trying to import into - this may be relevent
By doing this, it will give people a chance to reproduce the problem, if they can, and share their findings. People here genuinely want to help, and this will help them to help you :-)
[/quote] |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 March 2003 : 04:05:32
|
Hiya - that was an *excellent* step-by-step guide, and it enabled me to reproduce the behaviour immediately - everyone else read and learn!
OK so I've now got the same problem as you. I've reproduced it on winXPProsp1 running Office2003Beta2Build4920. Does anyone out there have a config it isn't reproducable on? Office 97, or something? Anyone still got Access 2 on an old box?!
I'll have an investigate, and if I can't find what the problem is I'll see if I can get someone on the 'office' team to look into it.
But you're right - this seems odd behaviour! |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 March 2003 : 04:12:11
|
Hmmm - this is unrelated to the import - deleting and adding records (1000/1001) to a table also prompts this.
I suspect it may be a design limitation - still looking... :-) |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
|
|
cardella
Starting Member
5 Posts |
Posted - 07 March 2003 : 08:57:17
|
This does help get to the route problem. I performed the following steps to correct the issue....
1)open up the table in design view
2)Select the field in question and click the LOOKUP tab
3)Change display control to... LIST BOX instead of TEXT BOX
4)Select row source .... "the table with the data in it"
5)select the SQL STATEMENT Query Builder...
6)add the column with the data
7)exit and save the builder
This allows me to see my items from the list box, although now I see any duplicates in my original data. When doing a query from the table or from within a sub query it work ok, but not perfect (the duplicate records for other columns have different classes A, B ,C etc that tie into this specifc column to give a unique row of data based on other inputs) Unfortunately, this does not resolve my ability to choose these records from within a form (which calls the query). I will need to work on this next, since the data is requested from a form to begin with. Thanks for the help to resolve this issue partly! |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 10 March 2003 : 09:09:17
|
No problem - shame I couldn't find you a full solution! I'm really hectic right now, so maybe someone else can help - try the msaccess group in this forum?
Good luck!
Paddy :-) |
|
|
| |
Topic |
|
|
|
Topic Locked
