Creating a new Database, retrofitting it to an Excel table (1 Viewer)

Steel

New member
Local time
Today, 01:44
Joined
Oct 11, 2017
Messages
6
My department has contracted a team to build a "bot" that will automatically assign work for employees in my department. It takes emails from an email inbox, looks at other Excel documents, and runs some queries to appropriately manage workloads and assign work evenly.

On the user end of things, we want to build an Access Database that will allow everyone in our department to track their workloads, be able to transfer work from one person to another, etc.

In the traditional way, I would start by making tables...but in this instance, all of the data is going to be dumped into Access from a single Excel table. Once the data is imported, how could I retrofit it to a relational database? For example, I would normally build a table for "Employees", I would have a PK of EmployeeID, I would have the First and Last names, maybe email address, etc.

The Excel document is only going to be showing the Employee's full name, what's the best way to tie this back to the Employee in the database?
 

plog

Banishment Pending
Local time
Today, 00:44
Joined
May 11, 2011
Messages
11,613
I have no idea why organizations insist on contracting out for 90% solutions; the remaining 10% is always the hardest part. Why can't this team properly build what you want? Or at least make the deliverable table(s) in a database?

With that said, you should always properly structure tables in a database. I would build those as you normally would, then build an import process that can bring in the Excel data and put it where it needs to go. Then you can use task scheduler to run that import process whenever you get new data in that spreadsheet. As for tying the spreadsheet to other data you build a mapping table--it would hold the correct ID value from the Employee table in Access and a field to hold whatever unique value you are using in the spreadsheet.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:44
Joined
Feb 19, 2013
Messages
16,553
how could I retrofit it to a relational database?
from a single excel table you will need to run multiple update/append queries based on a breakdown of the excel data. starting with the 'primary' tables and working down to the 'transactional' tables. Very simplistically if your file looks like this

empName...task....outcomeRequired..outcomeBy

for the first query you might append to an employee table which might look like

employeePK...knownAs...empName...active

empName is the name that comes from the excel file and you would only append empNames that don't currently exist in your table (i.e. using a left join)

for new employees you can manually add knownAs as and when they occur. probably set the active fields to default to true

important thing is you now have a PK which you can match through empName

the next query might be to update task in much the same way - the table might include the two outcome fields

then a third which updates a (many to many) table which assigns tasks to employees. Table might look something like this

empTaskPK..employeeFK...taskFK

your query would link the excel table to the employee and task tables on the relevant names to get the relevant PK's to populate the FK fields
 

Steel

New member
Local time
Today, 01:44
Joined
Oct 11, 2017
Messages
6
OMG...Thank you guys, looks like I have some work cut out for me...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:44
Joined
Feb 19, 2013
Messages
16,553
just to add to my post - on the basis that empName appears in multiple rows in the excel file, your first query would be SELECT DISTINCT or a group by query so you only get one iteration of each value
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:44
Joined
Feb 19, 2002
Messages
42,981
I'm going to go with plog's reaction. The data belongs in a database. It is never a good idea to make Excel the "master" source for your data. You might find that the developers are happier to work with an Access BE than with Excel.
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:44
Joined
Mar 14, 2017
Messages
8,738
I don't see this as "excel versus access" .... It is totally normal and common to receive "input" files containing data that needs to be imported to a database (whether that be flat file, Excel, or another format), but you definitely should create a process that parses out all that data and puts it in the appropriate, typically-designed, normalized tables in your Access database. The choices you make on table design in Access shouldn't have pretty much anything to do with the design of the input file (Excel file), other than the fact that whatever information you are being given will limit what information you can populate.

My biggest concern with your situation is that you say they will be giving you entity information without enough identifying information. Such as, the employee will only be represented by name. I'm sure you already know that is practically a show-stopper for good design. What will you do when a second person joins the process (and contributes to this 'input data'), who has the same name? I guess probably the only possible answers are, either 1) insist that the input data contains a unique key type of identifier or combination thereof, or, 2) Build an Exception Process into your data ingestion code that re-directs records like these to a queue to be manually worked/identified/assigned into tables.
 

Users who are viewing this thread

Top Bottom