0

I have a series of tables in an Access 2007 database. I am trying to find a way of outputting a flat-file to an excel spreadsheet that combines all of the tables so that each row of the flatfile represents a unique combination of the table rows from each table.

For example, these tables:

enter image description here

Would combine to make this output table:

enter image description here

The challenges I'm facing are:

  • The 'input' tables can vary in number of rows and columns, as well as quantity
  • The total number of rows in the final output table can get quite large (200,000+ rows)
  • I know Excel and VBA (in Excel) well but almost nothing about Access

Is there a way to do this in Access? Is there some native functionality in Access that I'm completely overlooking? Any pointers (even if it's "you need to read into X and Y") would be greatly appreciated!

Thanks,

Adam

AdamDynamic
  • 761
  • 6
  • 15
  • 29
  • 3
    Create a new query. Select your 3 tables as the data sources. If desired, set up joins between tables by dragging a line between a field in one table to a field in another. Without joins you will get a Cartesian Product ... every from 1st table paired with every row of 2nd table, and then each of those combination paired with every row of 3rd table. Select the fields you want included in the result set. When the query returns what you need, save it and give it a name. Then you can export that named query to Excel. – HansUp Apr 12 '13 at 15:23
  • As hansUp said, create a query and join the table. You can then save to a file or cut and paste into excell. If the table is large, you could hit excell's row / column limit though. The one query I would have is converting the data to excell the best way to go ??? – Bruce Martin Apr 12 '13 at 22:29
  • Thanks for the replies guys! I managed to put together a very simple query in Access that seems to have done the job. In terms of whether Excel is the best approach, the answer is 'probably not'; it is however a tool that everyone else in my team is comfortable with, whereas no-one knows much about Access/Databases (the solution is a temporary one anyway so it doesn't have to be 'perfect'). Thanks again for the help! Adam – AdamDynamic Apr 15 '13 at 08:14

1 Answers1

0

As noted above:

Create a new query. Select your 3 tables as the data sources. If desired, set up joins between tables by dragging a line between a field in one table to a field in another. Without joins you will get a Cartesian Product ... every from 1st table paired with every row of 2nd table, and then each of those combination paired with every row of 3rd table. Select the fields you want included in the result set. When the query returns what you need, save it and give it a name. Then you can export that named query to Excel.

If the table is large, you could hit Excel's row / column limit though.

Community
  • 1
  • 1
Paul Sweatte
  • 24,148
  • 7
  • 127
  • 265