Filtering Datasheets

[Nineseven]

Hi All,

My company recently updated from the Microsoft 2003 suite to the 2007 suite and I have been when it comes to certain things.

Every month I upload a spreadsheet with about 5000 lines of data in it. In this datasheet are several columns, the first being names. This 5000 lines of data are consisted or work done by about 50 people;50 different names. I have another spreadsheet with 3 names in it. In Access 2003 I was able to filter the main data sheet by only having the data returned to me from the three names that are on the other spreadsheet.
For the life of me I do not remember how I did this, everything looks too different in 2007. I believe I would run a query, and in that query (in the names column) set a combobox with the three names that are on the other spreadsheet. All this does is create a drop down box in the datasheet, and these three names are the only selections.
I think I might have never ran a query, and set a special filter under the property sheet in design view. But I am no longer sure.

To sum, From these 5000 lines of data I need to filter the main spreadsheet with the three names that are on another spreadsheet. I need every single line of business, from these three specific names, to be returned from that filter. I don't want the spreadsheet to have a pop up box where I have to write the name down, and I don't want to create a drop down box.



I know many of you are going to tell me to hit the filter button, and uncheck the names I dont need. In the same spreadsheet I have a column titled business code, and from the possible 10's of thousands of business codes, I have another spreadsheet or about 500 codes, that I would too filter out of this spreadsheet. Using the filter button and unchecking the boxes is too time consuming.


Help of any kind would be greatly appreciated

[TReimiller]

I'm a little confused by your post. It sounds like you've uploaded an Excel spreadsheet (let's call it Work Log) into a table in an Access database and you want to track the work of 3 specific employees (let's call them the Target Employees) which you've uploaded into another table in the Access database.

So to generate a report of the work for those Target Employees:
1. Create a Query in Access. I like to create queries in Design Mode because I can select my tables and create the specific kinds of links I want on my data.
2. Select the Target Employees table and the Work Log table
3. Link the Target Employees table to the Work Log table using the Employee Name field (click and drag from the Target Employees table to the Work Log table)
4. Right click on the link line and select Option 2 (Select All Records from Target Employees table and only those records from Work Log table that match)
5. Select the fields you want to display in your report (click and drag each desired field down into a column of the query)
6. Save and name your query

If you're a visual learner, there's a "How To" video on YouTube http://www.youtube.com/watch?v=hk8yw7ZCYFw

Hope this is what you were looking for!

[June7]

Do you still have the 2003 file? Have you tried opening it in 2007? Saving as 2007 accdb file?

[Nineseven]

I'm a little confused by your post. It sounds like you've uploaded an Excel spreadsheet (let's call it Work Log) into a table in an Access database and you want to track the work of 3 specific employees (let's call them the Target Employees) which you've uploaded into another table in the Access database.

So to generate a report of the work for those Target Employees:
1. Create a Query in Access. I like to create queries in Design Mode because I can select my tables and create the specific kinds of links I want on my data.
2. Select the Target Employees table and the Work Log table
3. Link the Target Employees table to the Work Log table using the Employee Name field (click and drag from the Target Employees table to the Work Log table)
4. Right click on the link line and select Option 2 (Select All Records from Target Employees table and only those records from Work Log table that match)
5. Select the fields you want to display in your report (click and drag each desired field down into a column of the query)
6. Save and name your query

If you're a visual learner, there's a "How To" video on YouTube http://www.youtube.com/watch?v=hk8yw7ZCYFw

Hope this is what you were looking for!

I do not know why I didnt just think of this, and did it in such a convoluted manner. Thank you.

Further Question. The same work log has a column called business codes. These codes can be one of tens of thousands of codes. I have another spreadsheet with 500 specific codes. Any Idea how I would filter this out? Or do you recommend I run two queries...one for the names, and then I grab the query I just created and create another query and create a joint against the 500 codes of specific business?

[TReimiller]

I do not know why I didnt just think of this, and did it in such a convoluted manner. Thank you.

Further Question. The same work log has a column called business codes. These codes can be one of tens of thousands of codes. I have another spreadsheet with 500 specific codes. Any Idea how I would filter this out? Or do you recommend I run two queries...one for the names, and then I grab the query I just created and create another query and create a joint against the 500 codes of specific business?

I always go by the K.I.S.S. (Keep It Simple Stupid) rule, so I would create the query that selects the work for those specific employees and then a second query that joins the Specific Employee Work query to the Business Codes table. That will give you a spreadsheet that lists the work those specific employees did on any of the 500 "projects" (business codes).

Good luck!