Data Normalization

[JMichaelM]

Have a question. I get a data set which has a field which some have multiple ID's which throws off the mapping to different data set. For Example: SvcT_3003873;SvcT_3000014 What do you suggest be the process for it be handled?

 

[JMichaelM]

Some even have four [SvcT_0231008;SvcT_0231018;SvcT_0231018;SvcT_3004075;SvcT_3004375;SvcT_0066191;SvcT_0231018] but the relational table only has one ID so this can be mapped

 

[jdraw]

Tell us more about what you are dealing with. Describe it just as you would tell an 8 year old or your 80 year old granny. We can't help until we understand you and the issue.

 

[JMichaelM]

1. One table has one field: Some have more than one ID but they are all in one table. [SvcT_0231008;SvcT_0231018;SvcT_0231018;SvcT_300407 5;SvcT_3004375;SvcT_0066191;SvcT_0231018]
2. Second table only has one ID so the ones with more than one ID cannot be linked as a relationship
In other words for the ones with more than one ID, I lose all the data because it does not link.

Do I seperate the data ID's in a query but if I do that you cant link from a new expression field?

 

[jdraw]

???? Your 80 year old granny understands that???

Let's start with what does this represent SvcT_0231018?

In 3 or 4 lines, what are you dealing with? What exactly would be the final view of whatever you are doing?

 

[JMichaelM]

I guess I will have to strip out each ID [SvcT_0231018] if there are more than one from a query as it is a relation in another table

 

[jdraw]

Well that may be, but readers can't help/advise/suggest anything until we understand what you are dealing with and what you are trying to achieve.

As others have said, if you can't describe what you are trying to do, then
- it's possible that you don't understand the issue and need to do more analysis,
- nobody can design or build it.

One field tables are rare.
Multi-valued fields are a poor inclusion into Access by M$oft. Many developers avoid them.
http://stackoverflow.com/questions/13505838/alternative-to-multi-valued-fields-in-ms-access

 

[JMichaelM]

I'm going to try to break the field into seperate fields using an expression like this Expr6: Mid([Service ID],79,12). Then I'll have to make a new table from it and link the fields back to the one ID in the relational table. Thanks

 

[HiTechCoach]

I agree that you should split the data into a properly normalized table design.

It should be easy VBA code to loop trough a recordset and create the desired records in the related/child table.

 

[JMichaelM]

Done. I did it I had to create 20 seperate columns. How do I join them. It is not working joining two different fields to one, It says I need to create a seperate query with the join first in order to create the join. Never done something so complicated in relational database. Good for the brain.

 

[jdraw]

What do the 20 columns represent?
What is the name of the Table?

You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Good luck.