Create Un-aggregated Records Table (1 Viewer)

[KevinC]

Hi,

I have a database that contains a table with an administration area and a number associated with the “count” of a certain characteristic of that area.

For use in another piece of software I need to convert the count system to individual records. For example I need to go from:

AREA COUNT
AdminArea1 3
AdminArea2 2
AdminArea3 1
AdminArea4 3
...

to:

AREA COUNT
AdminArea1 3
AdminArea1 3
AdminArea1 3
AdminArea2 2
AdminArea2 2
AdminArea3 1
AdminArea4 3
AdminArea4 3
AdminArea4 3
AdminArea4 3

I actually don’t need to count field in the new table, but it would be useful for my own quick reference.

The problem is that I do not have the original raw un-aggregated data – otherwise this would not be a problem. Also there is no way I can get this.

I realize that this is working in the opposite way to good normalised databases – however as I said the software that I need to export requires this data in the expanded format.

Does anyone know how I could run a query in access to achieve this?

I have large tables so will need to do this programmatically rather than manually.

Many thanks in advance.

Regards,

Kevin

 

[neileg]

If this is a one off, I'd simply run queries to extract the records where the count is 1 and append this once to a new table. Then extract the count = 2 records and append this twice, and so on.

Of course this isn't very pretty if you need to do this regularly and not very quick if your counts get too high, but it may be good enough. I don't do clever, I just do practical!