Converting lookups to fields

[sillysod]

Hi there,

I am looking to create data warehouse type setup on the following data

we have a list of customers and each customer can have some user definable fields stored against it

they are stored in a separate look up table

so there is a one to many relation between customers and several extra fields, the extra fields are stored as follows

NameofField FieldValue CustomerCode
Field1 Value1 CustomerA
Field2 Value6 CustomerA
Field1 ValueX CustomerB
Field1 ValueY CustomerC

So not all customers have all the field stored against them

I have another table which is where the actual fields are defined, the above is the table where the values are stored for any customers which may have a value

so i also have a table that lists the fields
FieldName StoredAgainst
Field1 Customers
Field2 Customers
Field3 Customers
Field4 Suppliers

I need to create a single row for each customers
which will list the fields currently stored as rows as columns, and just having a blank field for where a value does not exist

CustomerName Field1 Field2 Field3

How could i achive this?

I also have access to crystal reports and crystal sql designer if they are any use

Aswell as vs.net and office xp

 

[alvechurchdata]

I think you'll be writing procedural code in VBA or .Net to extract the contents of that extras table into additional fields of the main table. Yo may well hit Access' limit for the number of fields which might be why the extras table exists in the first place.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[sillysod]

The extra table exists so that the users can define extra fields, that may be unique to their business.

We only have about 20 of these extra fields anyway, plus its SQL server

I was hoping there would be an easier way than a VB procedure but if thats the only way ...


Thanks alot

 

[alvechurchdata]

its SQL server
You might do better to ask in one of the specialist SQL Server forums - was just talking in general terms that there's nothing in relational theory to do what you want.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[sillysod]

at the moment im thinking that a VB procedure wouldnt be that hard to code, just very slow to run.

So might have a crack at it that way

 

[alvechurchdata]

just very slow to run

Speed shouldn't matter too much in a data warehouse, you could update it once a day at midnight. Users of a data warehouse need static data so that the data isn't continually changing under their feet. Just make it clear that the data in the warehouse is "as at 23:59 Tuesday".

Geoff Franklin

http://www.alvechurchdata.co.uk