How do you extract a list of entries from a large MS Access db?

[Guest]

I have a MS Excel sheet with 22,000+ rows of data. The first column contains
the identity of each row (a gene number). I would like to extract the rows of
data pertaining to a specific set of genes that I am studying. In other
words, I would like to enter a list of, say, 50 genes (based on the gene
number in column 1) that would then extract those 50 rows into a new sheet,
database, report, whatever is possible. I was informed that Access could do
this, or something similar, with my Excel sheet. So, I have both programs. Is
any of this possible? If the answer involves macros/VBA, I might need a more
detailed response as I have no prior experience with those. Thanks in advance!

 

[Guest]

Hello KW,

You can certainly use Access to help return selected records. Start by
importing your data into a new table in Access. Open Access, click on File >
Get External Data > Import... In the first screen, select Microsoft Excel
(*.xls) in the files of type dropdown. Proceed through the various screens of
the import wizard. If all goes well, you should have your 22,000+ records
imported into a new table in an Access database. You may have trouble
importing some of the data, expecially if the datatype is not consistent. If
so, post back and we can tackle that issue if it needs to be addressed. It is
also possible to link to your spreadsheet, but I have personally experienced
corruption when doing so.

You can then refer to the following KB article for help on creating select
queries:

HOW TO: Create a Select Query in Microsoft Access 2000
http://support.microsoft.com/kb/304361/EN-US/

Note: Although written for Access 2000, this KB article should apply equally
well to all versions of Access.


Tom
______________________________________

:

I have a MS Excel sheet with 22,000+ rows of data. The first column contains
the identity of each row (a gene number). I would like to extract the rows of
data pertaining to a specific set of genes that I am studying. In other
words, I would like to enter a list of, say, 50 genes (based on the gene
number in column 1) that would then extract those 50 rows into a new sheet,
database, report, whatever is possible. I was informed that Access could do
this, or something similar, with my Excel sheet. So, I have both programs. Is
any of this possible? If the answer involves macros/VBA, I might need a more
detailed response as I have no prior experience with those. Thanks in advance!

 

[Guest]

Thank you so much! Worked like a charm!

 

[Guest]

You're quite welcome. Glad that I could help.

Please consider signing back on and marking my first reply as an answer.

Tom
__________________________________

:

Thank you so much! Worked like a charm!
__________________________________

:

Hello KW,

You can certainly use Access to help return selected records. Start by
importing your data into a new table in Access. Open Access, click on File >
Get External Data > Import... In the first screen, select Microsoft Excel
(*.xls) in the files of type dropdown. Proceed through the various screens of
the import wizard. If all goes well, you should have your 22,000+ records
imported into a new table in an Access database. You may have trouble
importing some of the data, expecially if the datatype is not consistent. If
so, post back and we can tackle that issue if it needs to be addressed. It is
also possible to link to your spreadsheet, but I have personally experienced
corruption when doing so.

You can then refer to the following KB article for help on creating select
queries:

HOW TO: Create a Select Query in Microsoft Access 2000
http://support.microsoft.com/kb/304361/EN-US/

Note: Although written for Access 2000, this KB article should apply equally
well to all versions of Access.


Tom
______________________________________

:

I have a MS Excel sheet with 22,000+ rows of data. The first column contains
the identity of each row (a gene number). I would like to extract the rows of
data pertaining to a specific set of genes that I am studying. In other
words, I would like to enter a list of, say, 50 genes (based on the gene
number in column 1) that would then extract those 50 rows into a new sheet,
database, report, whatever is possible. I was informed that Access could do
this, or something similar, with my Excel sheet. So, I have both programs. Is
any of this possible? If the answer involves macros/VBA, I might need a more
detailed response as I have no prior experience with those. Thanks in advance!