'You want to export the whole database!?'...

[XelaIrodavlas]

... is what I found myself asking.

The question: Is there a way to export all records across multiple tables onto a single excel spreadsheet, but without a ludicrous amount of duplication every time there's a one to many relationship?

Let me explain, I have an HR database featuring tables for Employees, Roles/Trades, Qualifications, Sites etc, and I've been asked if it's possible to get all of that onto one excel spreadsheet so that we can do mail merges, postcode searches etc (I know you can do these things on access, but we're ignoring that for now!!!).
So I gave it a try, creating a query to bring all the information together into a list I could export, but the problem is each employee has multiple roles, and in that they also have multiple qualifications, and multiple sites, and multiple everything else. Inevitably, Access crashed. (I don't blame it either, if my math is right there would have been literally millions of records, each more or less the same except for one difference to the one before it.)

Is there something obvious I have overlooked? Or was Access just not born to do this? What would be nice would be to find some coding or something, to rearrange each unique field entry into a column (where each row is an employeeID), that way the data would be presented in a matrix. In other words:
__________Employee details________Everything else
EmployeeID.............x...............................x...........

Rather than this:

Employee1 DetailA DetailA DetailA DetailA DetailA DetailA
Employee1 DetailA DetailA DetailA DetailA DetailA DetailB
Employee1 DetailA DetailA DetailA DetailA DetailB DetailB
Employee1 DetailA DetailA DetailA DetailB DetailB DetailB
Employee1 DetailA DetailA DetailB DetailB DetailB DetailB
Employee1 DetailA DetailB DetailB DetailB DetailB DetailB

But I would have no idea how to go about it. So, any ideas???

 

[boblarson]

The question: Is there a way to export all records across multiple tables onto a single excel spreadsheet, but without a ludicrous amount of duplication every time there's a one to many relationship?

As far as I know, not without A LOT of coding.

 

[gemma-the-husky]

just work out how many records you are likely to have, and produce a single query that brings it all together. then export that

easily doable for modest size databases, but too many rows for very large databases



it does solve the problem where you dont necessarily want to build every possible query the users may want. give them all the data, and let them filter the spreadsheet as they require.

 

[Lightwave]

Normalisation was invented specifically to get round the problem of duplication of data.

There is no way round that therefor you will always potentially end up with millions of rows if you had to combine everything into one talble.

If users are desperate for raw information I give them the raw separate tables and explain the relationships to them I will take out any of the foreign keys and replace with something more meaningful to them. If there are very obvious requirements I will combine tables and give them raw output.

I am generally in favour of giving users raw information... cos I often can't keep up with all the reports they want.