I have an access database that contains all of my employee information. Our site is the only one currently using this database. All of our other sites are fairly primitive at least for now.
My problem is this;
I have a timesheet created in excel from the main office, that I cannot change in any way. I want to import the names of the staff from my current employees query in my database into the excel spreadsheet in the names column.
Is there any way to just import this information, i.e. in mail merge from access to excel, or some other way I haven't thought of yet? Aside from copying and pasting of course, which defeats my main purpose.
Yes, you first create the query in Access. NEXT, in [/I]Excel[/I] you place your cursor in the cell you want the results to come into. NEXT - On the MenuBar in Excel click the Data and then Database Query
NEXT - Dialog box "ChooseDataSource" - Select MS Access Database and click OK.
NEXT - Dialog box "Select Database" - Select the Database of your choice and then click OK.
NEXT - Dialog box "Query Wizard - Choose Columns" - Here you Click the (+) to expand and show the Fields in that table.
NEXT - Select your field from the left side window "Available Tables and Columns". Once you select all the fields you'll need... Click the > arrow to each field to the right box (window) "Columns In Your Query".
NEXT - Dialog box "Filter Data" ..... that's up to you but however your query is setup in Access should be used...so just click...
NEXT - Dialog box "Sort Order"......Sort if you like that's up to you. Then click
NEXT - Click "Return to Microsoft Excel" OR "Save Query" (a good choice so that you can have it again later on) OR just click...
FINISH.... You're done with that part, and now for the last dialog box that pops up... "Returning Data to MS Excel".......you can take it from there....
So just play around with that and see how you like it. Hope this helps you out.
Thanks very much for trying to help, Still have a question.
Originally Posted by DavidCoutts
Just as a side note to BUD's reply you can also put this info directly into a pivot table,
Data -> PivotTable -> Get external Data
then the process is identical to the above
I tried working with a pivot table, and I also tried following Bud's instructions, but my microsoft office must be a different version, I have the 2002 version. Under my menubar, data does not have a selection called "Database Query". Instead, I have "Import external data", pretty much I attempted to follow his instructions as best I could, but I don't have the same options.
Trying the pivot table, I can import all of my data from my query, but how do I get the information in my excel spreadsheet, 1 entry at a time instead of the whole query at once?
I have attached the excel spreadsheet to this so you can see what I am using.
my pivot table is like this:
Last name first name
You're absolutely correct. I am using Office 2000 so guess things are a bit different now. The Bill Gates, everytime a new version comes out, it seems like you have to go learn it all over again as things don't stay quite the same. Wish I could help better but without that newer version I have no idea as to how to help you. I tried but hope someone else on here can better guide you through the process.
Need anything else in 2000 version give me a ring and I will see what I can do.
The problem with the menu bar selection Data not having a Database Query selection in Excel 2000 or 2002 is not due to a change from Microsoft. I checked this out in Excel 2000. Bud's instructions, which were very good and thorough, should have said click the Data and then the Get External Data and then the New Database Query selection.