microsoft access question

[SaintGRW]

does anyone know microsoft access pretty well? i haven't used it in like 3...4 years. i have a spreadsheet with about 50 sheets, each sheet has a catalog number and then like 100 product #'s that were in it. anyone know how to take that and important the sheets or file into access so i can search it all at once for a catalog # or product #?

 

[Mr.IncrediblyBored]

1) Open MS Access.
2) Click on Help -> Microsoft Access Help.
3) Click on the index tab.
4) Type "import."
5) Read.

 

[AU Tiger]

Many ways to approach it. The degree of difficulty depends on the layout of the spreadsheet.

Easiest way would be to make a flat file table from all the spreadsheets. Reformat the spreadsheets so that each line has the Catalog number as well as the product information. Cut and paste or use the File...Import feature in Access to import the rows into an Access table. Create the forms, queries and reports that you need.

More complicated process would be to break the spreadsheets up into Catalog table, Product table, and Product/Catalog cross reference tables.

 

[SaintGRW]

Originally posted by: KnightBreed
1) Open MS Access.
2) Click on Help -> Microsoft Access Help.
3) Click on the index tab.
4) Type "import."
5) Read.

if that's enough to solve the problem i wouldn't have asked.

right now i have the catalog # in column 1 in excel. products sku's in column 2. get like an error when i import 3 speadsheets to the same table i think it's just cause access doesn't have enough rows is that a possibility? if that's the case i could make a separate table for each spreadsheet but is there a way to scan all the tables at once for a # or would i have to scan them 1 at a time

 

[AU Tiger]

Access has unlimited rows.

Check your field types to make sure they are correct, just to make sure you aren't trying to import an alphanumeric value into a number field. Did the import process create a separate table with the import errors?

 

[SaintGRW]

yeah, it did generate a 2nd table. that's what's messing me up

 

[SaintGRW]

next problem, an easy way to filter thru the tables, i have 2 tables 1 has about 2000 things the other has 4000, need a way to filter them for stuff easily. both at the same time would be good. boss said she wanted me to use reports...can you make a report that you can filter for a specific item?