By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Combining multiple records into one

P: n/a
I know this is a painful question and I have searched the group for
past responses and none of the responses seem to cover my specific
need.

We have a table that contains fields like
ID, Yr, DateOfTest, TestSource, TestScore, TestSubScore1,
TestSubScore2, etc

An individual can have taken this test once or nth amount of times.
There are no limits as to a max. My boss would like to make one
record out of however many records exist for one ID.

So, he wants ID, Yr, DateOfTest1, TestSource1, TestScore1,
TestSubScore11, TestSubScore21, DateOfTest2, TestSource2, TestScore2,
TestSubScore12, TestSubScore22, etc, etc, etc

I have tried crosstab queries, but I'm not having much luck because
you can only have one column heading.

The other option I thought about was to count how many records per ID,
and create a loop based on the max number of records. That loop would
build the table through VBscript. I am really hoping for an easier
solution.

Any ideas would be greatly appreciated!
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Heather wrote:
I know this is a painful question and I have searched the group for
past responses and none of the responses seem to cover my specific
need.

We have a table that contains fields like
ID, Yr, DateOfTest, TestSource, TestScore, TestSubScore1,
TestSubScore2, etc

An individual can have taken this test once or nth amount of times.
There are no limits as to a max. My boss would like to make one
record out of however many records exist for one ID.

So, he wants ID, Yr, DateOfTest1, TestSource1, TestScore1,
TestSubScore11, TestSubScore21, DateOfTest2, TestSource2, TestScore2,
TestSubScore12, TestSubScore22, etc, etc, etc

I have tried crosstab queries, but I'm not having much luck because
you can only have one column heading.

The other option I thought about was to count how many records per ID,
and create a loop based on the max number of records. That loop would
build the table through VBscript. I am really hoping for an easier
solution.

Any ideas would be greatly appreciated!


Flat files were great during the days of Univac

The UNIVAC I (the name stood for Universal Automatic Computer) was
delivered to the Census Bureau in 1951. It weighed some 16,000 pounds,
used 5,000 vacuum tubes, and could perform about 1,000 calculations per
second. It was the first American commercial computer, as well as the
first computer designed for business use. (Business computers like the
UNIVAC processed data more slowly than the IAS-type machines, but were
designed for fast input and output.) The first few sales were to
govenment agencies, the A.C. Nielsen Company, and the Prudential
Insurance Company. The first UNIVAC for business applications was
installed at the General Electric Appliance Division, to do payroll, in
1954. By 1957 Remington-Rand (which had purchased the Eckert-Mauchly
Computer Corporation in 1950) had sold forty-six machines.
You get the general idea of what I think of your bossman's idea.

I guess you could create a table on the fly. Get the max number of
tests and create the table and create your fields. Then open a
recordset and fill it in. I'd consider exporting it to Excel.

I did some work for a company once where the in-house programmer
convinced the boss to convert a normalized database into a flat file. I
made my case against it but I did not do a good enough selling job on
keeping a database normalized. It cost the boss many thousands of
dollars for the rewrite. I think the boss is now bankrupt and the
in-house programmer has moved on to other companies to
destroy...currently at Compaq.

Have you considered creating a report that is multicolumn instead?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.