Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting information from Access (Office 97)

    Hello,
    I have an Access database that, needless to say, has quite a bit of information in it. I have produced a report from Access that our executives have always been happy with. Now our folks in the Corporate office want it in an Excel spreadsheet. I don't know how to do this without retyping everything that they need every month.

    Is there a way that I can setup a spreadsheet that automatically gathers the needed information without much input from me? If so, how do I do it?

    Much thanks!
    Louise

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Exporting information from Access (Office 97)

    In access you can save the results of a query as an Excel Spreadsheet. I'd start there and then write a macro to format the results.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting information from Access (Office 97)

    Hi, Thanks for the reply.
    The current database report is not from a query, just the information from the database itself. Do I now need to setup a query in order to export the information over to excel? I don't know how to do macros yet...I'll save that for another time. ;-)
    Thanks
    Louise

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Exporting information from Access (Office 97)

    If the report is the entire database, you can extract it to an Excel file using File, Save As/Export, To an External File or Database, Type of File Excel (version whatever), but if it's a (partial) extract, the easiest way is to set up a query, then export to Excel. Open the database, click on the Queries tab, click on Design, select the tables, fields & criteria, etc., you want and go from there. (I'm not very experienced on the Query Design phase!) When the Query is set, extract to Excel by right-clicking the Query, and the process is pretty much the same from there.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting information from Access (Office 97)

    Thanks for your help.
    I also found that if I preview the report, I can click on the "Word" icon and choose Excel and it will put everything on an Excel spreadsheet. So now I think I'll design another report with only the information that they need on it to use for this purpose.
    Thanks again
    Louise

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Exporting information from Access (Office 97)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Good tip, I'll have to remember that one myself in the future! (I often do Excel extracts based on someone else's Query.) I guess that Access class I took has turned to rust in the brain.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Exporting information from Access (Office 97)

    Louise, you have my empathy on this one, however it is not hard to link Access and Excel.
    On the Data menu, there is a Get External Data, New Database Query option. You use this to define the type of Database, Access etc, and then select the tables/fields that you want. If the Get external Data option is not on your Data menu, then go to Tools Add-ins and install both the ODBC and MS Query Add-ins
    The data is then imported into Excel. Save the file so that next time you open it you can update the data by doing a refresh.
    You may need to edit the data by using the Trim command if sorts appear not to work correctly, however to date I have had very few problems in using this feature to move data from Access to Excel.
    Good Luck <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Paul Coyle
    Approach love and cooking with reckless abandon

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting information from Access (Office 97)

    Thanks! Will give it a try.
    Louise

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting information from Access (Office 97)

    Paul, Have done the data menu/get external data method and it works very well. However, I just noticed that it did not capture all of the records from the database. I just entered more records and did a "refresh all" and the new records did not get added to excel. Do I have to rebuild the excel spreadsheet every time I add a new record? <img src=/S/drop.gif border=0 alt=drop width=23 height=23> Thanks
    Louise

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Exporting information from Access (Office 97)

    It should pull all of the info over. Have you checked the query in Access?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting information from Access (Office 97)

    Yes. I think I fixed it. Most of the information comes from a table except for two columns which come from a query. The data export only returns the records for which there is information in the query. So if the table has 52 rows (records) but the query returns only 46 rows (records) of information for example, then only the 46 will be exported. I had to put dummy information in the remaining records in order for the entire database to be exported.
    I may not be saying this right but I hope you understand anyway.
    Thanks!
    Louise

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting information from Access (Office 97)

    Louise - I don't quite understand when you say that 'most of the information comes from a table, but two columns come from a query.' I assume that you mean a query takes most of its data from a single table, and adds two more fields from another table(s).

    In any event, I would look at the query you are using if you only return records when there is data in those fields joined from other tables. This sounds like an "Inner Join" in Access - where a record is only returned if there is matching data in both tables. If you want to include records where "table A" has a value and there is no corresponding value in "table B" you should set this as an "Outer Join" (either left or right) - look at the 'join properties' on the query grid.

    Inserting 'dummy information' is a pain - you have to remember to do it, which implies that YOU have to be on top of what information should / should not be available (that's the computer's job, not yours) and then you have to remember to delete it from the database, and probably from the Excel file you are generating. I would strongly recommend against it.

  13. #13
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Exporting information from Access (Office 97)

    Louise,
    I see that you have solved the problem, great! <img src=/S/smile.gif border=0 alt=smile width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting information from Access (Office 97)

    Dean, I know I'm probably not explaining myself correctly, so please bear with me. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Most of the information I need exported to Excel comes directly from the main table that I call "Studies". The "two columns" come from a query based on another table called "Payments". The query asks for the last time we received a payment in the form of a date and amount. I noticed that if we have not received any payments yet, those studies did not export to Excel. But if I simply put a meaningless date in the payments database, then it comes over without a problem. I know when I first set it up, I had to "join" the tables. I simply did that by dragging the common field from one table to the other and closed that window. I don't understand what "inner join" or "outer join" means or where the join properties are. Sorry.

    I agree with you that remembering to enter a date or dummy information is a pain, but not knowing how else to do it................... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks!
    Louise

  15. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting information from Access (Office 97)

    Louise

    I think I understand what you are saying. In any database in Inner Join is where a query has to find a match in both tables to return a record. An Outer Join allows records to be returned from one table or the other without a matching record in the other table. Your situation is one of these - you want to return a "Studies" record even when there is no matching Payment record (perhaps even particularly when there is no matching payment record <img src=/S/grin.gif border=0 alt=grin width=15 height=15>). Don't feel bad about not knowing this, by the way - it is part of the Access learning curve

    I have made up a little pretend database that includes similar tables and information, just so I could get a screenshot of what you have to do (I hope it attaches and shows up properly).

    In access, go to the query grid where you created the join between your "Studies" table and your "Payments" table. If you right-click on the join line between them you will get a small dialog giving you the choice of "Join Properties" or "Delete" (don't pick delete). when you select Join Proerties it will bring up the dialog box pictured (this is in A2K - the details will be different depending on what version you are in.

    There will be three choices for the type of join - the top is the most common - an Inner Join where you need a match in both tables. The bottom two choices are the two Outer Joins. The 'left' and 'right' refers to which table the query will take records from without a match in the other one - fortunately, Access plops the table names in the description so you don't have to worry about the left and right distinctions. Note on the query grid the join has an arrow pointing towards the table that must be matched - that's because I took this screen shot after I had set the join up as an Outer Join, and then went back and did it again, to take the purty picture!

    The query will now return a record for all studies, showing blanks (nulls) for payment amount and date for any "Study" where there is no payment record, instead of just ignoring them.

    I hope that helps!
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •