Creating un-aggregated records table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KevinC - 09 Nov 2007 16:02 GMT

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

Reply to this Message

Dale Fye - 09 Nov 2007 16:23 GMT

Kevin,

1. Create another table (tbl_Numbers) with a single field (intNumber), and
10 records, values 0 - 9.

2. Create a query (qry_Numbers) with SQL that looks like below. This will
give you a list of numbers from zero through 999

SELECT Hundreds.intNumber * 100 + Tens.intNumber * 10 + Ones.intNumber AS
intNumber
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

3. Create a new query

SELECT yourTable.Area, _
qry_Numbers.intNumber as SeqNum, _
yourTable.Count
FROM yourTable, qry_Numbers
WHERE qry_Numbers.intNumber >= 1 _
AND qry_Numbers.intNumber <= yourTable.Count
Order BY yourTable.Area, qry_Numbers.intNumber

Once you have this, you can modify it to insert these values into a table,
if that is where you want it.

HTH
Dale

Signature

Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

> Hi,
>
[quoted text clipped - 47 lines]
>
> Kevin

Reply to this Message

Gary Walter - 12 Nov 2007 12:35 GMT

> Kevin,
>
[quoted text clipped - 9 lines]
> intNumber
> FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

<snip>

PMFBI

It may not be obvious that in above query the 3 "tables"
in the FROM clause are just 3 instances of tbl_Numbers
(maybe for some anyways)...

In a slightly different tack, I might change above to a
make table query (not saying better, nor that Dale is wrong):

SELECT Hundreds.intNumber * 100
+ Tens.intNumber * 10
+ Ones.intNumber AS Iota
INTO Iotas
FROM tbl_Numbers AS Hundreds,
tbl_Numbers AS Tens,
tbl_Numbers AS Ones
ORDER BY
Hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber;

giving you the table Iotas with 1 field Iota going from 0 to 999
(that you should set as Primary Key to speed up calcs)

Iotas
Iota (pk)
0
1
2
3
....
999

then, using table Iotas, I would make one
more table "tblCopies"

SELECT
IotaCopy.Iota AS Copies
INTO tblCopies
FROM Iotas, Iotas AS IotaCopy
WHERE
IotaCopy.Iota > Iotas.Iota

giving you n records for each Copies n

tblCopies

Copies
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
6
6
6
6
6
6
7
7
7
7
7
7
7
........

Which I believe would give you a table
you can just add to a query joining Copies
to your Count to give n records for Count = n ...

Now this will be a big table (499500 records!
-- for example, there will be 999 records for
[Copies] = 999) so I might decide whether I
can afford to (or need to) store that many
records...

for example, if you can absolutely determine your "Count"
will never be greater than 100, you could delete all
tblCopies records where [Copies] > 100

DELETE * FROM tblCopies WHERE Copies>100;

or just not create them to begin with

SELECT
IotaCopy.Iota AS Copies
INTO tblCopies
FROM Iotas, Iotas AS IotaCopy
WHERE
IotaCopy.Iota > Iotas.Iota
AND
IotaCopy.Iota <= 100;

Again, not saying this is better
or Dale is wrong, just that there
are some calculations going on here
that can be taken care of beforehand,
so, when the table is used in an intensive
query, those calculations are already done
and will not slow you down.

Apologies again for butting in...

good luck,

gary

Reply to this Message