Solved Normalization of "Wide" data to "Long"

[Halked]

Good day,

I been struggling on a section of my integration for this company I am helping. They asked me to create a section for keeping a list of all the appliances that each unit has. My idea was to create just a simple sub form attached to my main building details form. However, my biggest problem is that I have over 300 units to integrate. That would be around 4 records per home, with each record having up to 3-4 descriptive details such as serial number, model, brand. I.E. that would be well over 1200 unique records that I do not want to manually brute force and type.

In the picture I provided, you may notice that mock data in the excel sheet shows all their data into a single string of information for each column. I have an idea of how I would want to do this. Please see below:

Idea:

1. Use excel to delimit the data by a space.
2. Take those new columns and add them as a row.
3. Assign in excel the foreign key to each row manually. (HouseID) Child key.
4. Import that excel sheet into access.

I feel like I am missing something here though and I am open to suggestions on what else can be done for this project. I am planning on transferring this data over the weekend, either by brute force or something elegant.

Thank you/Miigwetch
- Sean

 

[RonPaii]

If you already have the data in Excel, link your Access FE to the sheet(s), then use queries or code and record sets to import that data into normalized Access tables.

 

[Mark_]

So you have "UnitID" and "Unit" as parent. Will you be responsible for tracking tenant information also? If so, I'd highly recommend tenant information being a child record as tenants come and go frequently and often change between units more than you'd expect.

For each appliance, do you need to also track install date and maintenance records? If so, that should be a child record for each appliance, then you use a junction table to show what appliance is in what unit as of when. Helps if they pull a "Fridge" from one unit for repair, then install in a different unit once back. Junction table will hold UnitID and ApplianceID, along with Install date and removal date.

Very relevant item I'm not seeing is data of manufacture for each appliance and the warranty info. "Fridge" manufactured in 1992 but installed in 2022 isn't going to be the same as one purchased in 2022 and installed immediately after purchase.

I'd also have description for each appliance. Free form text (at least initially) where you can include feature that are relevant for lease requirements. Any feature promised in the lease must be matched when replacing an appliance.

 

[Halked]

If you already have the data in Excel, link your Access FE to the sheet(s), then use queries or code and record sets to import that data into normalized Access tables.

Appreciate the suggestion. I will give this a shot.

 

[LarryE]

1. Each unit has multiple individual appliances installed
2. Each type (range, refrigerator, dish washer etc.) may have multiple brands
3. Each brand has multiple models
4. Each model has multiple individual serial numbers sold and installed

You will need:

1. A unit table
2. An appliance types table
3. A brand name table
4. A model table
5. A serial number table that also holds install date and other information related to that appliance

The serial number table also has a foreign key from the Unit table

 

[MadPiet]

Appreciate the suggestion. I will give this a shot.

Do you have some mock data you can share? (Doesn't have to be real... just representative.) Maybe you can do some of the grunt work in Excel and PowerQuery and the rest use Queries in Access. hard to tell at the moment though.

 

[GPGeorge]

Good day,

I been struggling on a section of my integration for this company I am helping. They asked me to create a section for keeping a list of all the appliances that each unit has. My idea was to create just a simple sub form attached to my main building details form. However, my biggest problem is that I have over 300 units to integrate. That would be around 4 records per home, with each record having up to 3-4 descriptive details such as serial number, model, brand. I.E. that would be well over 1200 unique records that I do not want to manually brute force and type.

In the picture I provided, you may notice that mock data in the excel sheet shows all their data into a single string of information for each column. I have an idea of how I would want to do this. Please see below:

Idea:

1. Use excel to delimit the data by a space.
2. Take those new columns and add them as a row.
3. Assign in excel the foreign key to each row manually. (HouseID) Child key.
4. Import that excel sheet into access.

I feel like I am missing something here though and I am open to suggestions on what else can be done for this project. I am planning on transferring this data over the weekend, either by brute force or something elegant.

Thank you/Miigwetch
- Sean

How much have you studied the process of Databasae Normalization? You have an Excel spreadsheet, which is usually not the best starting point because it is "wide" as you say. Overcoming the tendency to think in "spreadsheet" can be difficult. So you have to make a serious effort to approach the problem from the point of view of relational tables.

So, with the stage set, I'd strongly recommend watching some good videos on normalization, and studying, not just reading, some discussions on how we go about normalizing spreadsheet data into relational tables as part of your preparation.

Here are some of my current favorites.

The Normal Forms: First Normal Form (1NF) – Roger's Access Blog

www.rogersaccessblog.com

Problems With Repeated Columns – Roger's Access Blog

www.rogersaccessblog.com

Once you have that foundation under you, the advice you get will make more sense as you migrate the data from columns in a spreadsheet into fields in tables.

 

[Halked]

Do you have some mock data you can share? (Doesn't have to be real... just representative.) Maybe you can do some of the grunt work in Excel and PowerQuery and the rest use Queries in Access. hard to tell at the moment though.

Yeah, the mock data is in the second attached picture on this main thread. Are you not able to see the attachment picture? New to this forum so I am not sure if it went through.

 

[Halked]

How much have you studied the process of Databasae Normalization? You have an Excel spreadsheet, which is usually not the best starting point because it is "wide" as you say. Overcoming the tendency to think in "spreadsheet" can be difficult. So you have to make a serious effort to approach the problem from the point of view of relational tables.

So, with the stage set, I'd strongly recommend watching some good videos on normalization, and studying, not just reading, some discussions on how we go about normalizing spreadsheet data into relational tables as part of your preparation.

Here are some of my current favorites.

Once you have that foundation under you, the advice you get will make more sense as you migrate the data from columns in a spreadsheet into fields in tables.

Good day George!

Love the reply and will be sure to take a look at some of these resources.

Anyways, no I do not use excel spreadsheets for any data that I manage. I exclusively use access. It just so happens to be the format provided to me by the organization I work for. In the pictures that I attached, I list my new tables and how I would go about normalizing some of the mock data.

Again, my main point was just sort of seeing what I am missing here with the appliances. Many have already offered great advice on this and will continue to explore options regarding integrating this excel spreadsheet to an already established database.

 

[Halked]

Do you have some mock data you can share? (Doesn't have to be real... just representative.) Maybe you can do some of the grunt work in Excel and PowerQuery and the rest use Queries in Access. hard to tell at the moment though.

I will leave this here just incase the picture doesn't show it. Example of how the data looks.

Fridge Field (Mock Data)
AMANA - YNED4655EW MC1208654 - 5/2023
Frigidaire - FFTR1835VW XD10394855 - 7/2021
GE - GTS18GSNBRSS GE9928347
Whirlpool - WRT112CZJW WP7728394 - 6/2020
LG - LTCS20020W LG7782341 - 4/2019
Moffat - MTMX050EF3WW LC881234

 

[Gasman]

I will leave this here just incase the picture doesn't show it. Example of how the data looks.

Fridge Field (Mock Data)
AMANA - YNED4655EW MC1208654 - 5/2023
Frigidaire - FFTR1835VW XD10394855 - 7/2021
GE - GTS18GSNBRSS GE9928347
Whirlpool - WRT112CZJW WP7728394 - 6/2020
LG - LTCS20020W LG7782341 - 4/2019
Moffat - MTMX050EF3WW LC881234

Can't you see the pictures?

 

[Halked]

Can't you see the pictures?

Well, I can't tell what you mean by this. No, if people in this thread posted pictures, I can't see them. However, I think I will mark this thread as solved. I think I am done with this.

 

[Ken Sheridan]

The attached file illustrates how non-normalized data from Excel (or any similarly structured source such as a delimited text file) can be decomposed into a pre-defined set of normalized tables in Access. When inserting the appliance data into a table, your current string expressions can be parsed into their constituent elements by means of the Split function. You can see how it works in the immediate window:

strFridge = "AMANA - YNED4655EW MC1208654 - 5/2023"
? Split(strFridge," - ")(0)
AMANA
? Split(strFridge," - ")(1)
YNED4655EW MC1208654
? Split(strFridge," - ")(2)
5/2023

 

[Halked]

The attached file illustrates how non-normalized data from Excel (or any similarly structured source such as a delimited text file) can be decomposed into a pre-defined set of normalized tables in Access. When inserting the appliance data into a table, your current string expressions can be parsed into their constituent elements by means of the Split function. You can see how it works in the immediate window:

strFridge = "AMANA - YNED4655EW MC1208654 - 5/2023"
? Split(strFridge," - ")(0)
AMANA
? Split(strFridge," - ")(1)
YNED4655EW MC1208654
? Split(strFridge," - ")(2)
5/2023

Very insightful comment Ken.

Thank you very much.