Filter .... sort of ...

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I have a 1600 record spreadsheet used for scheduling
students. There's lots of different combinations of
classes, rooms, etc. Let's say I know which records are
the ones which fall into each category. There's hundreds
of combinations. I used Access queries to come up with
all these results.

Anyway, let's say that I know I need to update records:
4, 24, 37, 43, 100, 112, 130, 156, 173, 180 in the same
manner, and I want to use Excel. Can I put these in a
list and then 'Show these records'. This would be
like 'multiple filtering'. To be able to build a list,
then show only those records ... like a query on Access,
but on Excel? I don't have Access installed at work.

Thanks,

Robert
 
You could try Data -- Form, if you don't have too many fields, entering the
record # as Criteria each time.

Or Advanced Filter. If your data is in e.g. A:H, copy the Record # label to
J1 and enter the record #s to show beneath. Then use this in the Criteria
Range.

HTH,
Andy
 
Robert said:
To be able to build a list,
then show only those records ... like a query on Access,
but on Excel? I don't have Access installed at work.
Click to expand...

A things you may not have realized:

1) You can query a (closed) Excel workbook as a Jet data source using
ADO. You use the MS OLEDB provider for Jet but specify Excel in the
extended properties e.g.

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db.xls;" & _
"ExtendedProperties='Excel 8.0'"

Data must be laid out in database style i.e. rows of columns. Column
headers make things easier (especially one row one column tables). The
Excel equivalent of a table is either a worksheet (suffix with $):

SELECT MyCol FROM [Sheet1$]

a defined Name (no $):

SELECT MyKeyCol FROM [MyRange]

or even a range defined at the worksheet level

SELECT MyKeyCol FROM [Sheet1$MyRange]
- or -
SELECT MyDataCol FROM [Sheet2$D4:D99]

The square brackets are required for an existing 'table'.

Jet proprietary SQL is used so as an MS Access user you should be
familiar with it. However, certain functionality is not supported
e.g. DELETE FROM, ALTER TABLE. Inserted rows are appended to the
bottom of a range. And you can't UPDATE a cell which contains a
formula.

2) You don't need the MS Access application to create and/or query a
Jet database (.mdb file). You can do all this on the fly using only
Excel and ADOX (to create the .mdb file) and ADO (use DDL statements
to create the schema e.g. CREATE TABLE etc and SQL for queries).

You do need MDAC, free MS download and shipped with Excel, and Jet,
free MS download and shipped with early versions of MDAC, so it's
highly likely you already have the necessary components shipped with
Excel.

See the following links (in these articles, read 'Jet' for the word
'Access'
because the MS Access application is not actually used):

Creating an Access(sic) Database:
http://msdn.microsoft.com/library/d...s/odeopg/html/deovrcreatingaccessdatabase.asp

Running a Temporary Query:
http://msdn.microsoft.com/library/d...s/odeopg/html/deovrcreatingaccessdatabase.asp

--
 
Back
Top