How to link data on the import?

[Statesman63]

I accepted a month long job in putting together an Access database for a trucking company. This job revealed to me how much of Access I really do not know, yet thought I knew. It is just above my knowledge level, yet, I feel that if I just understand some concepts, that I will be able to complete the assignment.



I'm having several problems. One problem I have is the title of this thread. I don't know how to make the incoming data know that it is related to its perspective field in other tables.

Also, I am not understanding is how to link the tables. I have a main table with all the data. It is by date, and has all transactions, which Access assigned a number per transaction as the primary key. Then I have an Employee table (the truck drivers), a Customer table (the truck driver's company), an employee productivity table (includes wages, pay rate, Emp names, etc) and a truck table. I tried linking the tables, but not sure if it is correct. I linked the Customer and Employee table via an EmpID foreign key in the Customer table. The employee productivity table is linked to the employee table via an EmpProd foreign key in the employee table. And the Customer table is linked to the truck table via a Cust foreign key in the Truck table. The table with all data is indexed to the Emp table, and under relationships the arrow points from the Emp table to the all data table, but there is no one to many (etc.) relationship because I did not know how to establish one. I tried reversing the arrow, but it wouldn't let me. I don’t even know the difference between an indexed link verses a relationship link.

The data itself is not linked. The Employees in the employee table does not know that it is the same field in the other tables. The all data table has EmpName daily info with only about 20 names, but about 4,000 fields. I listed the 20 names in the EmpName field of the tblEmp, but do not understand how to make both tables realize that it is the same field. It does not seem to realize that it is the same field because it does not rearrange the two field data with the correct data. I have this problem with all my tables.

So when I need to add the rest of the data, I don’t think the data knows where to go.

What am I doing wrong? What am I not conceptualizing?

(I am using Access 2007 at work, and completed a class on 2010.)

[June7]

Can't have 4,000 fields, could have 4,000 records.

Why would you link employees and customers? Is customer assigned to specific employee to manage that customer?

You don't mention a table for shipping events. If this database is to track shipping jobs, then need a table for those records. The table would have fields to save as a foreign key the primary key value for:

Customer (who placed the order for shipping)
Employee (the driver)
Truck (the vehicle order was packed in)

Also fields for:
OrderID
DateOrder
DateShip

This table would link to the other tables which serve as sources for data selection. The links you are attempting don't appear valid to me.

This is a very simplistic description of data entities and how they relate. I expect will be more complicated in actuality.

[Statesman63]

Hi, thanks for responding. It is a company that oversees about 5 truck companies and those companies have the employees. And yes, thank you, I meant 4,000 records, not fields. I am still learning about the company because I am new here, but from what I understand, the overseer company logs the employee miles, wages, truck driven, time card, fuel, dates, etc, and keeps track of the companies' revenue, sur-charge, gross weight of truck, (and other fields that aren't coming to mind right now). There are no products logged, just mileage and fuel.

The main data ("all data table") that I imported from an Excel spread sheet has all the data fields by date, by employee doing the driving, by company, by truck, and other columns (26 in all) where the primary key is just an automated assigned number by Access. I am not quite sure how to link all of those employees who are listed over and over again randomly in the 4,000 records to my employee table so that the employee table can understand the name field there is the same as the name field in the "all data table" that are repeated over and over again. Thanks!

[June7]

If you have a table of employees and the names in that table are same as names in the data table, then can link on those fields. However, link should be done on a unique ID that is not names. Creating a unique ID in employees table would be easy (use autonumber field). Again, if the names are the same in both tables (consistent spelling), then populating the ID into data table should be a simple UPDATE action.

Name parts really should be in separate fields in employee table.