Non-normalized data

[Pasvorto]

I have a report that gets employee data from a table (Table A). The report works fine. Now, the user wants to include data from Table B. The problem with Table B is that the data is not normalized. Each record in Table B allows for 6 employee numbers.

So, the user wants employee information about work performed for a date on a given production line. That is working. The addition is for those times when the employee did no accrue 8 hours on the production line. The remainder of their time should be in Table B (where they are doing something else). Out side of the employee number and the date, there is little connection between the data in the 2 tables.

I seem to be given a choice of using aliases or creating subreports linking the employee number and date. So, I have Table_A.EmployeeNumber and need to link to one of the fields: Table_B.Employye1, Table_B.Employee2, Table_B.Employee3, Table_B.Employee4, Table_B.Employee5, or Table_B.Employee6. If I find a connection, I can report their time from Table_B.

Any suggestions?

[IkkeEnGij]

is something preventing you from creating a 'normalized table' (Table C ?), and filling it with the data in the 'unnormalized table' ?
creating 6 records in the 'normalized table' for every record in the 'unnormalized table'
and creating an enforced referential entegrity between Table A and Table C

[Pasvorto]

That just seemed a bit more complicated. I have used aliases before, but I'm not a fan of them. I am leaning toward just inserting a sub-report that accesses data from that table.

[ColinE66]

I think that IkkeEnGij's suggestion will be your best approach, going forward. If your users are starting to see the need for analyzing information in Table B then you should address it now rather than when the number of such requests has grown.

But, if you insist on maintaining the status quo, perhaps you could create a view in the database that normalises the data.

Something along the lines of this air-code

CREATE VIEW TableC AS
SELECT Employee1 as EmployeeID, Column1, Column2, etc
UNION
SELECT Employee2 as EmployeeID, Column1, Column2, etc
UNION

etc., etc....

[Pasvorto]

Now there's an interesting approach. I never considered using a view. I am only supporting this company until the end of February. They are converting to a new system so there really is no long term involved.

[techgnome]

I wouldn't bother with a table that normalizes the data... I think I might be more inclined to create views that normalize the data... Tables would normally be the answer, but in this case, unless the app was also changed to use the new normalized tables, they would have to be constantly updated, which would very easily become a nightmare.

-tg

[Tech99]

Pivoting works fine, when you know data values.

fex. FldName field might contain values in pivot.

SELECT [FldName2], ISNULL([10],0) as '10', ISNULL([15],0) as '15', ISNULL([20],0) as '20',
ISNULL([22],0) as '22', ISNULL([23],0) as'23', ISNULL([25],0) as '25',
ISNULL([30],0) as '30', ISNULL([40],0) as'40', ISNULL([50],0) as '50',
ISNULL([60],0) as '60', ISNULL([70],0) as '70', ISNULL([80],0) as '80',
ISNULL([81],0) as '81', ISNULL([82],0) as '82', ISNULL([83],0) as '83',
ISNULL([84],0) as '84', ISNULL([85],0) as '85', ISNULL([86],0) as '86',
ISNULL([90],0) as '90', ISNULL([91],0) as '91', ISNULL([92],0) as '92',
ISNULL([93],0) as '93', ISNULL([94],0) as '94'
FROM
( SELECT [Fldname2], [Fldname] FROM tblName ) AS pvtsource
PIVOT
( MIN([Fldname]) FOR [Fldname] IN([10], [15], [20], [22], [23], [25], [30], [40], [50], [60], [70], [80], [81], [82], [83], [84], [85], [86], [90], [91], [92], [93], [94])
) AS p
where FldName2 in ('what ever')
order by FldName2