Trying to get row data across columns

[Rodifer]

Hi,

I'm not sure what I'm trying to do counts as a full transpose. Basically,
I've got some data which simplifies to the following:

ID# Topic Serial

There can be more than one record for each ID in this table, each with one
attached serial number. The table I want will have one record per ID, but
will have all serial numbers for a given ID number across the table, thus:

ID# Topic Serial1 Serial2 Serial3 Serial 4

I have established that there may be as many as four SN#, as few as 2 per
ID#. How would I go about creating this table?

 

[John W. Vinson]

Hi,

I'm not sure what I'm trying to do counts as a full transpose. Basically,
I've got some data which simplifies to the following:

ID# Topic Serial

There can be more than one record for each ID in this table, each with one
attached serial number. The table I want will have one record per ID, but
will have all serial numbers for a given ID number across the table, thus:

ID# Topic Serial1 Serial2 Serial3 Serial 4

I have established that there may be as many as four SN#, as few as 2 per
ID#. How would I go about creating this table?

Why would you WANT to?

Sorry, but that's a serious question. Such a table would be a gross violation
of normal form. In fact as described it *could not exist* since every record
of a Table must have the same set of fields; you can't have a table with four
fields in one record and six fields in the next record!

A Crosstab query can get data into this "shape" but I don't know why you would
want to put that non-normlized data into a stored Table.

 

[Rodifer]

Maybe it's partly that I'm not a highly experienced Access user (getting
there), and don't know a better way yet to do what I do. I am working on
upgrading a lot of our data cleaning processes from manual review (messy,
time-consuming, and hard on the eyes) to a set of ad-hoc databases for the
initial cleanup, followed by regular reporting to identify data changes that
may signal problems. My algorithms range from the very simple to - well -
this particular challenge.

I'm doing this as an intermediate step to identify violations of a rule.
There will actually be two columns of data (project number and date)
represented by the Serial number area - I just didn't want to get too
detailed. In the end, the task is to identify those items for which
particular project number and date combinations exist that are not allowed.
Once I have the data aligned this way, it becomes very simple to pull it into
Excel, and manipulate from there. Then the initial cleaning is done, and
monthly follow up is easy and quick.

I'll look into crosstab queries and post again if I can't figure out how to
do it. I just thought I'd answer your question since I've seen it on
database forums before, and thought it might elucidate why one might
occassionally want to denormalize the data.

Thanks for the help.

 

[John W. Vinson]

I'm doing this as an intermediate step to identify violations of a rule.
There will actually be two columns of data (project number and date)
represented by the Serial number area - I just didn't want to get too
detailed. In the end, the task is to identify those items for which
particular project number and date combinations exist that are not allowed.
Once I have the data aligned this way, it becomes very simple to pull it into
Excel, and manipulate from there. Then the initial cleaning is done, and
monthly follow up is easy and quick.

I'll look into crosstab queries and post again if I can't figure out how to
do it. I just thought I'd answer your question since I've seen it on
database forums before, and thought it might elucidate why one might
occassionally want to denormalize the data.

It is indeed valid to - sometimes - denormalize, either because Access can't
perform a desired operation (or can't do it fast enough). But it should always
be a temporary expedient.

My concern is not so much with constructing a denormalized VIEW of the data
usnig a Query (and yes, you can export that query into Excel) but with storing
that denormalized VIEW into a Table. Sure, maybe you do need to do so; but I'd
say *don't* do so unless there is some very good reason that you can't use the
query.

It does sound like a Self Join query can be made to work here, especially if
there are only two columns (or a small fixed number of columns) which need to
be brought into line. If you have an open-ended variable number of columns to
manage, it's still possible but a lot harder!

 

[Rodifer]

Thanks. I'd been trying a self-join, but was having a really hard time
persuading it to let go cooperate with a basic self-join, largely because I
could have up to four sets of data (four self-joins) going across, and I was
not able to eliminate the problem with data multiplication.

I ended up hauling splitting the thing into four tables, one for each repeat
(Original reference, first duplicate of SN, second duplicate of SN, and
third) and joining those. From there, it was easy to get my data in shape,
apply a logical check to see if the rules were violated, and produce my
output. The project is basically done except I have to make it pretty and
document it for future reference.

Terrific success - took an assignment that should take about 12 hours a
month, spent about eight hours retooling the process, and netted a process
that will do the same work in a half-hour a month.