X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Need Help With Importing Data, Access 2016    
post Dec 14 2018, 09:43 AM

Posts: 7
Joined: 6-February 15

Hey guys, I'm not sure if Im posting in the right section, but I really hope you guys can help me with this!!! I work at a college & I have an excel file for my contracts that we do to pay the professors for the classes they teach in the summer. The file is a list of the contracts created & I import this file into my access database. A contract can have up to 10 courses on it. So the main identifier in the excel file is the WFID which is the contract ID & 1 row is created for that WFID. However, the CRN is the identifying factor for me, so I need an individual row for each CRN, rather than an individual row for a WFID # with multiple CRNs. I hope this makes sense...

Does anyone know how I could separate the data in excel or have access do it for me? Right now I have to do alot of sorting, copying, pasting & deleting in the excel file in order to get what I want, which takes quite a bit of time since this file typically has over 500 rows of data... So I very much appreciate any help you guys can give!!

Oh & I have attached the excel file for you to get an idea of what I need. It has 2 sheets in it. The 1st sheet is how the data comes originally & the other sheet is of the changes I have to make in order to have it work in my database. I can attach the database if necessary, just let me know if you need it.

Thanks again guys!!
Attached File(s)
Attached File  WFID_Changes.zip ( 51.81K )Number of downloads: 7
Go to the top of the page
post Dec 14 2018, 11:11 AM

Posts: 1,767
Joined: 5-February 06
From: Ohio, USA

What have you tried, besides trying to make the file bigger in a different tab?

Since the file contains headers, I would set up a normalized table structure, using those same headers as field names. Then it's pretty simple... query the linked file, appending to each of the tables the fields in the columns, and if everything is properly keyed, indexed, normalized, then everything will go into each table once for each key.

It's been drilled into me since I started doing this over 15 years ago... normalization, normalization, normalization. Once you understand it, then you MAY come up with scenarios where you need to 'break' it, but until then... practice, practice, practice. If you need help, try Newcomer's Reading List and check out the several pieces on normalization.

"Nuclear" Nick
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
post Dec 14 2018, 04:13 PM

UtterAccess VIP
Posts: 10,866
Joined: 10-February 04
From: South Charleston, WV

In looking at your data I think you're going to need many lookups. I think your final normalized table is going to have mostly foreign keys in it for the many lookup tables.

So the data transfer process from that Excel sheet will not only have you transferring the wfid data but you'll have to replace most of rest of the columns with a foreign key.

The thing is, once you have Access setup with all the lookups and you have developed a form for data entry with combo boxes for the lookups, the manual data entry is quite easy, as you probably know, since in most of the combos you just type in one or two characters and you get the value you want and then you hit tab and go to the next field. For 500 records it might even be easier to re-enter the data on your new form. Or maybe not.

Robert Crouser
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st August 2019 - 03:09 AM