On Nov 9, 9:04 am, KevinC <kevincre...@hotmail.comwrote:
Quote:
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
