Hele win non-normalized query

[arbasd]

I have the following 3 tables:

Project Table = contains a unique Project_ID and Project_Name

FieldNames Table = contains a unique Field_ID, Field_Name (EG: CustName, Cust St, ContractAmt, etc) and Field TYPE there are about 65 fields for each project. All the data is stored as text (even dates and numbers) but I can convert those once I get them into a usable table.

FieldData Table = containng the following fields:
BindID_P - Linked to the Project_ID
BindID_F – Linked to the Field_ID
Value – contains the data for the field


I know this is NOT a normalized database but it is what I have pulled in from another SQL database via ODBC.

I need to figure out a way to create a table or query that puts all the data from the FieldData table into a single record for each project like the following:

_________Cust Name__________ Cust St__________ Contract Amt
Project1
Project2
Project3

Etc.

Any help would be greatly appreciated and thank you in advance.

 

[CJ_London]

sorry, you are not explaining yourself very well - at least I don't understand your problem.

Why not just link project table and fielddata on the project ID and fieldnames and field data on fieldID?

perhaps provide some example data and what it is required to look like afterwards?

 

[CJ_London]

on reflection, perhaps I do - join tables as I suggested, then convert to a crosstab query - projectname as (group by) row heading, fieldname as (group by) column heading, value (first or last) as value. If you need to sum it, you may need to do different crosstabs for each datatype and convert the value to the appropriate type before summing (see cDbl, cCurr, etc)

Note that value is a reserved word (as you can see from above) so strongly recommend you change it to something else otherwise SQL could become confused.

 

[arbasd]

CJ I apologize for not being more clear in my description of the challenge and thank you for the suggestion about the field named "value". That is what the was named in the data I pulled in from a MySQL database written by someone else but I will alias it as "entered" or some such name.

The problem I am having is the crosstab is not quite right. The data in the columns may or may not match the record row. I have attached the image of the query and would appreciate any advice.

Thanks again.

The tables are:
Projects (contains the project name and ID)

ExtraFields (describes the data in the extra fields List table (column header)
ExtraFieldsList (that is where the data resides the BINDID links to the Project ID)

 

[arbasd]

CJ - never mind I had something odd going on with the incoming data. Cleaned it up and all is good. Thanks again.

 

[The_Doc_Man]

Actually, what you described is an over-normalized table structure. You might wish to run a query to "flatten" some of those tables. I'm going to suggest that you think about this:

First, go through your list of field names. See what they represent. Some of them will be attributes of a project. Some will be attributes of a customer. Some will be attributes of a contract.

Lay out one table to capture ALL of this information into one big hot mess. I'm going to shoot from the hip so you might have to play with this idea (if you want to go this way).

Start by copying your Project Table to your working table. In other words, that hot mess you laid out starts as a COPY of the project table. But you modify the table definition of the hot mess table to have the fields needed for this process. They will start out empty. Now you can use a recordset operation on the FieldNames table where you want to get the field names and the field IDs.

Dim rsHM AS DAO.Recordset
Dim dbWrk as DAO.Database
Dim stUpd as String
...

SET rsHM = CurrentDB.OpenRecordset( "FieldNames", dbOpenDynaset )
SET dbWrk = CurrentDB
rsHM.MoveFirst

HMLoop:

'    {UPDATED}

stQry = "UPDATE HotMess JOIN FieldData ON HotMess.ProjID = FieldData.BindID_P " & _
           "SET [HotMess]![" & rsHM.FieldName & "] = '" & [FieldData]![Value] & "' WHERE [FieldData]![BindID_F] = " & rsHM.FieldID & ";"

dbWrk.Execute stQry, dbFailOnError

IF NOT stHM.EOF THEN
    stHM.MoveNext
    GoTo HMLoop
END IF

stHM.Close

The above code segment uses the recordset to look up field names and field ID numbers one at a time and builds a dynamic UPDATE query to transfer data from the FieldData table to the working table that will be the start of the renormalization process.

This in effect flattens the data set that you have. From here you can do various queries to set up tables to re-normalize this on more traditional lines. For example, let's look at CustName as one of the things you might normalize (if you had repeat customers).

You might start with a Customer Table where you would make a Customer ID number as an autonumber and that customer name as one of the fields. You would do something like this to start:

INSERT INTO CustTable (CustName) SELECT DISTINCT CustName FROM HotMess;

That will give you the unique customer names and will assign a number to each. Then, in HotMess, you can modify it to include a new CustID field. Then

UPDATE HotMess JOIN CustTable ON [HotMess]![CustName] = [CustTable]![CustName] 
SET [HotMess]![CustID] = [CustTable]![CustName];

Then you can remove the CustName field from HotMess and instead establish a relationship between HotMess and CustTable on CustID, probably one-to-many with CustTable being the "one" side of that.

Note that if you find info in HotMess that goes with the customer (other than customer name), you could copy that info to the customer table when you establish the list of customers from that INSERT INTO query.

You can do this for every field that you found in that list that needs to be re-normalized. When you are done (having built the distinct side tables and removed the name fields and replaced them with the ID fields for relationship establishment), you have normalized tables that contain everything you had in the imported table.

The process looks tedious but is certainly possible. I've shown you one way to skin a cat, but there are other approaches.