True about the normalization. What I would like to do is break it up into a couple of tables, an entry table and entry details. But I am working with...

Ok, thanks. That seems to work. But it seems complicated. Opinions on this please....

If I am able to edit how the records in this table get entered, should I change...

So then you are saying that what I am trying to do is just not possible?

Ok, yes, that's true. But what if I wanted to do something like this:

select * from #temp t where t.color in ('red','blue')

and still get record number 4 because red is...

No, I have not... but NICE! I like it.

Thanks!

Well that seems pretty simple. Thanks.

I can't really set the value when creating the record. There are actually 23 entries on the form, not just 5. I kept the number...

Can you use this to help?

CREATE TABLE [HourlyAdmitsandDischarges](

[dateid] [int] NULL,

[recordtype] [varchar](25) NULL,

[h0] [int] NULL,

[h1] [int] NULL,

[h2] [int] NULL,

[h3] [int] NULL,

[h4] [int] NULL,

[h5] [int] NULL,

[h6] [int] NULL,

[h7] [int] NULL,

[h8] [int] NULL,

[h9]...

Thanks for the reply. Unfortunately the data in my table looks different. It is a wider table with all hours in the day in each record. A sample looks like...

Ok, I see what you are saying...

instead of something like this:

drawdate num1 num2 num3 num4 num5 num6

02/05/2015 55 ...

Interesting...

I have never unpivoted a table, but I think I understand what it is your are saying. I will read up on unpivot to see how to make it...

Good to know. Thanks!

Thanks Jack, I appreciate the help. Unfortunately, the two tables are on different servers and the admins of one of them is insisting that I do not use linked tabled...

I think I got it. It looks like I really don't need lastname and firstname in the second CTE. Only need the address to join to and the groupnumber.

thanks...

Still reading through it to understand it all. Results look good, now I just want to be able to do it myself next time.

BOOM just like that. I tried using a CTE and a row_number, just didn't think of having to do it twice.

Thank you!!