New User-how do I filter a data sheet by another?

[Guest]

Hi,

I rarely use Access, but have a large spreadsheet where I have first and
last names and a second data sheet with last names. I need to filter the
first datasheet by the last name on the second data sheet and return all
fields (but only "matches" to the last names on data sheet 2) on the large
spreadsheet.
Spreadsheet 1:
Bill Smith purchases id description
Sandy Elves purchases id description
Frankie John purchases id description
Fred Mertz purchases id description


Data Sheet 2:

Smith
John

I would expect to return 1 and 3 of first datasheet. There could be
multiple correct line items in first data sheet.

Thanks very much.

Susan B

 

[John Vinson]

Hi,

I rarely use Access, but have a large spreadsheet

nitpick: Excel uses spreadsheets. Access uses tables. THEY ARE
DIFFERENT.

where I have first and
last names and a second data sheet with last names. I need to filter the
first datasheet by the last name on the second data sheet and return all
fields (but only "matches" to the last names on data sheet 2) on the large
spreadsheet.
Spreadsheet 1:
Bill Smith purchases id description
Sandy Elves purchases id description
Frankie John purchases id description
Fred Mertz purchases id description


Data Sheet 2:

Smith
John

Are the first andlast names in the same field, or (better) in two
different fields in Spreadsheet 1? If different fields, create a Query
by adding both tables to the query grid; join the LastName field in
Datasheet2 to the LastName field in Datasheet1.

If the last name is embedded inside a full-name field in Datasheet2,
you'll need a more complex query. Add both tables to the query window
but DO NOT join them. Instead, put a criterion on the Criteria line
under the full name field of Spreadsheet1:

LIKE "*" & [Datasheet2].[LastName]

using your actual table and fieldnames of course.

John W. Vinson[MVP]

 

[Guest]

Perfect John! And I will try to escape the spreadsheet mindset!

sb

Hi,

I rarely use Access, but have a large spreadsheet

nitpick: Excel uses spreadsheets. Access uses tables. THEY ARE
DIFFERENT.

where I have first and
last names and a second data sheet with last names. I need to filter the
first datasheet by the last name on the second data sheet and return all
fields (but only "matches" to the last names on data sheet 2) on the large
spreadsheet.
Spreadsheet 1:
Bill Smith purchases id description
Sandy Elves purchases id description
Frankie John purchases id description
Fred Mertz purchases id description


Data Sheet 2:

Smith
John

Are the first andlast names in the same field, or (better) in two
different fields in Spreadsheet 1? If different fields, create a Query
by adding both tables to the query grid; join the LastName field in
Datasheet2 to the LastName field in Datasheet1.

If the last name is embedded inside a full-name field in Datasheet2,
you'll need a more complex query. Add both tables to the query window
but DO NOT join them. Instead, put a criterion on the Criteria line
under the full name field of Spreadsheet1:

LIKE "*" & [Datasheet2].[LastName]

using your actual table and fieldnames of course.

John W. Vinson[MVP]