Creating un-aggregated tables in access

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

Re: Creating un-aggregated tables in access

On Nov 9, 9:04 am, KevinC wrote:
> 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

Kevin:

First, create a new table (I called mine tblExpanded) with fields
called Area (Text) and Count (Number) and save it.

Then, paste this function into a module in your database:
Public Function ExpandData()
Dim dbs As DAO.Database
Dim rstOrig As DAO.Recordset
Dim rstExpanded As DAO.Recordset
Dim X As Integer
Dim MyCount As Integer
Dim MySQL As String
Dim MyArea As String
Set dbs = CurrentDb
MySQL = "Select * from tblCounts"
Set rstOrig = dbs.OpenRecordset(MySQL)
Set rstExpanded = dbs.OpenRecordset("tblExpanded")
rstOrig.MoveFirst
Do Until rstOrig.EOF
MyArea = rstOrig!Area
MyCount = rstOrig!Count
For X = 1 To MyCount
With rstExpanded
.AddNew
!Area = MyArea
!Count = MyCount
.Update
End With
Next X
rstOrig.MoveNext
Loop
End Function

Replace the tblCounts with the name of your original table with the
aggregate data, and tblExpanded with the name of the new table you
created. Run the function from the Immediate Window and you should
get what you're looking for. I don't believe a query will do what
you're looking for, but this worked dandy in my test db! You should
add some error handling, and you could easily modify this function to
allow you to enter the names of the original and expanded table names
to make is usable for other similar situations. You might need a
reference to the DAO Object Library. I used DAO since you didn't
indicate what version of Access you're using.

HTH,
Jana

Re: Creating un-aggregated tables in access

Hi Kevin,

Here is something you can try:

First -- you need to create a numbers table which will contain 1 field
-- call it Number
then add numbers to it like
1
2
3
4
5
6
7
...
up to whatever your highest count value is in your table

then here is the query to unaggregate your table

Select t.Area From yourTbl t
Inner Join Numbers n On n.Number <= t.Count
Order By t.Area


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Creating un-aggregated tables in access

On Nov 9, 10:35 am, Rich P wrote:
> Hi Kevin,
>
> Here is something you can try:
>
> First -- you need to create a numbers table which will contain 1 field
> -- call it Number
> then add numbers to it like
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> ..
> up to whatever your highest count value is in your table
>
> then here is the query to unaggregate your table
>
> Select t.Area From yourTbl t
> Inner Join Numbers n On n.Number <= t.Count
> Order By t.Area
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Rich: Nice solution! I never thought of that....