Interesting Update

[Guest]

Hi,

I have a table with column headings like so with sample data

InsID, Type2, ID2, Type3, ID3, Type4, ID4, Type5, ID5 as far as ID16
1234
4567


And I have another table with the following
INSID, OIN, Role, Type
1245 12 Team RISP
1245 17 Team RISP
4567 10 Team RISP
4567 7 Team RISP
4567 12 Team HMI
4567 17 Team RISP


What I want is the first table to look like

InsID, Type2, ID2, Type3, ID3, Type4, ID4, Type5, ID5
1234 RISP 12 RISP 17
4567 RISP 10 RISP 7 HMI 12 RISP 17

An INSID can have up to 16 differenet OIN numbers, I hope this makes
some sort of sense but if any further clarrification is needed please
giev us a shout!!!

Thanks Phil

 

[Tom Ellison]

Dear Phil:

Your first example is of a denormalized set of data. This is a bad thing.
Avoid like the plague sort of result.

The second example is of a normalized set of data. This is a good thing.

So, you want to change from the good thing to the bad?

OK, but you must first take the good rows and determine some order in which
to number them so they can go across.

And does the information in the OIN column just go away?

In your output, the 4567 row, the order is 10, 7, 12, 17. How did you
determine this to be the order when you went horizontal. If there is a
method to this, please explain. Your rows ARE ordered by INSID, but within
each group by that column, I don't see any ordering that you're using.
That's the specific difficulty to which I'm referring.

I expect you may say that that's the order of the rows, as shown in your
sample data. Common misconception. Rows in a table are in an order only if
you specify a sorting method. Otherwise, it's called a "bag" which refers
to the fact that, if they are listed in an order, it's the result of pulling
the jelly beans out of the bag, red / green / yellow / green / etc. This is
not guaranteed to be repeatable, and has no significance.

Now, it is very difficult to get a query to perform arbitrary assignments.
In fact, the only way I know to do that is with a random number generator.
There is definitely no repeatable ordering to rows, except by sorting by a
unique set of key columns.

I'll be glad to try to help to the extent possible. We need to Rank the
rows within each INSID group, then use a query built on subqueries of that
Rank in order to assign them to the (horrible, shudder) denormalized
columns.

Don't get me wrong. There can be a good reason to do this. Certainly, it
is often necessary and desirable to do this for reporting purposes. But,
please, please, listen. Don't do this for internal database purposes. Keep
the normalized data for that.

Tom Ellison

 

[Guest]

Hi Tom,

Thanks for spending the time looking at this, it's much appreciated, the
order for record 4567 of 10 7 12 17, this does not have to be in this order,
these are just people codes that I want associtated with recordid of 4567.
Usualy I work with T-SQL rather than access SQL and sometimes I dont get my
process right. If you can offer any further information it would be much
appreciated. If there is anything else that I have not explained please give
me a shout.

Thanks Phil

 

[Tom Ellison]

Dear Phil:

First, an impression. If you're used to working with T-SQL, stick with
that. It's superior. Yeah, I'd know I have to duck when I say that. But
Jet isn't on a par with MSDE or full SQL Server. No way.

OK, next question. Is the data in the INSID and OIN columns, taken
together, unique? Can it be duplicate? If it can be duplicate, would it be
unique if we add Role and/or Type?

Are you familiar with ranking within a group. Something like:

SELECT INSID, OIN, Role, Type
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.INSID = T.INSID
AND T1.OIN < T.OIN) AS Rank
FROM YourTable T

Such a ranking would be used to identify into which set of columns (your
columns sets of Type#/ID# numbered 1-5) each row of data would be placed.

If I were putting this into T-SQL, I'd write:

SELECT INSID, OIN,
(SELECT Type
FROM YourTable T1
WHERE T1.INSID = T.INSID
HAVING (SELECT COUNT(*)
FROM YourTable T2
WHERE T2.INSID = T1.INSID
AND T2.OIN < T1.OIN) = 0) AS Type1
FROM YourTable T

This uses a 2 level nested correlated subquery (whew!) to extract the Type
of the row with the lowest (rank = 0) row within the INSID group.
Similarly, you can extract the ID1 value from the OIN column. Then,
changing the Rank from 0 to 1, you can extract the next Type/ID values for
"2", then "3", "4", and "5".

However, if INSID / OIN are not, together, unique (and preferably guaranteed
to be so by constraint, i.e. unique index) then the subquery will find two,
or more, rows, which generates an error. If you could provide uniqueness by
adding additional columns, as I suggested earlier, then this can be
overcome. Otherwise, you're asking the query to arbitrarily specify which
row to place in a rank ahead of another which has tied with it. No can do.
Queries are picky about being asked to arbitrarily choose between to equal
options.

The above, I believe, is within the capacity of Jet to run, as well. I
particularly made the reference from the inner subquery to be to the
intermediate subquery when I wrote:

WHERE T2.INSID = T1.INSID
AND T2.OIN < T1.OIN) = 0) AS Type1

If you use this:

WHERE T2.INSID = T.INSID
AND T2.OIN < T1.OIN) = 0) AS Type1

then you have made a reference to the outer query from the innermost, a 2
level jump so to speak, and I have never seen Jet accept this one. T-SQL
has absolutely no problem with this, by comparison, so I consider it to be
"good SQL" but it exceeds Jet limitations. Interesting little trick, eh?
Try it both ways with Jet and see!

More about you how could easily continue to use T-SQL to do your work, even
if the data is in a Jet database, if you request me to advise you on this.
Just let me know and I'll add that discussion.

Do you feel we're getting anywhere?

Tom Ellison