Microsoft Access A to Z: Preparing and importing data

No video

Microsoft Access A to Z: Preparing and importing data

Рет қаралды 2,157

Lisa Friedrichsen

Күн бұрын

Пікірлер: 21

@thesusanest 10 күн бұрын

You are a great instructional designer and educator, thanks! This video helped me see how I can import my very wide spreadsheets (covering multiple topics) into a staging table, then divide out those fields into their appropriate separate tables. 1 Mass import, an append queries for each permanent table, then clear the staging table via a delete query. Next up, VBA code to automation the process. 😊

@lfriedrichsen 10 күн бұрын

@@thesusanest you are so kind to make this comment. Ty! See all of my youtubes organized at learncs.w3spaces.com

@matthewb5235 9 ай бұрын

Your lessons are the best. Your delivery is so fluid and focused, with so many key details. It is a real lost that you have retired (?)

@lfriedrichsen 9 ай бұрын

Thank you for the kind words! Yes, I'm retired but still pinch hitting in the teaching category as needed and creating KZfaqs as time allows. Let me know if you have a need for something that I haven't addressed. All of my KZfaqs are organized at learncs.w3spaces.com. Thanks again and take care.

@atef74edu 2 жыл бұрын

I was thinking about using the function Trim in Excel to clean up the data, but you did take my understanding to cleaning the data to a higher level and much broader scope.👍

@lfriedrichsen 6 ай бұрын

Thank you for the comment. I should have thanked you a long time ago. See all of my KZfaqs organized at learncs.w3spaces.com

@erinkavanagh3553 Жыл бұрын

Thank you😃

@lfriedrichsen Жыл бұрын

Ty! See all my youtubes at learncs.w3spaces.com

@jimfiorentino7741 3 жыл бұрын

Love your instructional style

@lfriedrichsen 5 ай бұрын

You are welcome! See all of my KZfaqs organized at learncs.w3spaces.com

@nickybenz8592 Жыл бұрын

I was thinking of importing multiple spreadsheets of data into one table. My thinking approach is make sure all that spreadsheets are consistent before importing 0:02 . Or do you recommend say there is 20 excel files with shipment data, then consolidating them into one excel sheet then import that one excel spreadsheet. If there are more shipments under that customer just important that data into access when the time comes.

@lfriedrichsen Жыл бұрын

I would probably go with option 2. You can do a lot of data scrubbing in Excel to make sure that the data imports successfully to Access.

@lfriedrichsen Жыл бұрын

One Excel file allows you to sort each field/column a-z and z-a which helps you find bad data.

@lfriedrichsen Жыл бұрын

Once you import the data successfully in Access you can use the find duplicates query wizard and sorts to find, fix, or delete duplicate data. Regardless, it sounds like a big job that will involve many hours of manual work and attention to detail.

@nickybenz8592 Жыл бұрын

@@lfriedrichsen appreciate I will try it tomorrow! I am going to check with your other videos :)

@atef74edu 2 жыл бұрын

Now, I thought about something we can do to check the validation of the date specially when we have thousands of rows in the excel sheet which we can insert a column next to the date and use the function Value(E2) and run it to the end of the list and when there a logical error in the date we will get the result #Value!

@lfriedrichsen 6 ай бұрын

Cool! Thank you for the comment. See all of my KZfaqs organized at learncs.w3spaces.com

@dwihpjavas 3 жыл бұрын

nice Explanation, but Im wondering how to importing datas transaction on the parent table? while the transaction is child table already have records. for example to import the jobs of each employee to the employee table, wich at the table the relation key employees are the IDkey Employee hvnt to be same ID.

@lfriedrichsen 3 жыл бұрын

I think you are asking about how to import new records into a child table when the foreign key field in the child table doesn't have a matching value in the primary key field of the parent table. If that is correct you have to remove the "enforce referential integrity" option on the relationship, import the records, use a query to find all of the orphan records in the child table, manually fix or enter the matching records in the parent table to make sure there is a matching ID in each table, at which point you can enforce referential integrity on the relationship again. See this KZfaq on how to find/fix orphan records! kzfaq.info/get/bejne/grp0iqyT0ZyodXU.html

@lfriedrichsen 3 жыл бұрын

I should do a KZfaq playlist on this specific issue given it is a common one. Perhaps this summer! Here it is! How to find orphan records using join properties!! kzfaq.info/get/bejne/grp0iqyT0ZyodXU.html

@dwihpjavas 3 жыл бұрын

@@lfriedrichsen really nice, Im already ring a bell notification , cant hold to waiting