Help wanted, relations between tables (1 Viewer)

AljayB

Registered User.
Local time
Today, 11:40
Joined
Dec 2, 2015
Messages
19
Hello all,

I am new to access and don't yet understand the relationships between tables, with that I am having really a had time in making my access program to work.
I hope some of you can have pity on an newbie like me and help me.:) because I am getting really frustrated here as I cannot continue.:banghead:

In excel I have a file with one sheet that has many colums which contain different data in one row for one material.
Now the problem is, I don't want the 60+ rows in my access table. Therefore I have broken down the table in many small tables that contain the specific data required. for that one material. The problem I face now is I don't know how to link the many different tables to the main table.
For example when u enter a new material in the main table and then enter new details in the other tables, how do you link the data from all these tables to the new material that we have just entered?

If someone is interested in helping me, I could also provide you with an exemplary excel table and my new access table...

many thanks in advance,

AljayB
 

Attachments

  • example1.PNG
    example1.PNG
    51.8 KB · Views: 89
  • example2.PNG
    example2.PNG
    80.9 KB · Views: 79
  • Test HDPE.zip
    1,005.1 KB · Views: 82
Last edited:

Tieval

Still Clueless
Local time
Today, 18:40
Joined
Jun 26, 2015
Messages
475
Hi,

This looks extremely complicated and needs some thought put to exactly what it is. In my experience I find that sitting down and creating a spreadsheet on a single page of your entire requirement is the easiest way to start.

Although this spreadsheet will have a very large number of columns, it will clarify the process to you. Once you have done this you can see the relationships that are required.

One important issue is that one of your sheets is not as such a spreadsheet, instead you seem to be using the grid to insert a number of tables in a number of different ways.

You need to define a full column and row relationship even if a lot of data is not applicable in a number of the rows, from this you can then split the information into relevant tables, columns and rows for a database.

I moved to access when a single sheet spreadsheet became too big to manage, taking this approach will introduce you gently to databases and then allow you to become more complex.

You will notice that you soon find you have lots of rows, this is not an issue, lots of columns is where problems come and why tables are split up.
 

apr pillai

AWF VIP
Local time
Tomorrow, 00:10
Joined
Jan 20, 2005
Messages
735
Table relationships is a complex subject. There are several type of relationship like one-to-one, on-to-many and others.

A. One-to-one relationship

Example: Employee Record has different segment of information:
Segment-1: Employee Code, Name, Date of Birth, Place of Birth, Nationality.
Segment-2: Address, City, State, Zip
Segment-3: Educational Qualification, Experience-1, Experience-2
Segment-4: Basic Pay, House Rent Allowance, City Allowance, Transportation

Assume that we have created the employee record, consisting all the four segments of information, as a single record. We will not be using all these information every time we do something concerning the employee.

When we want to claim his salary for the current month we need information from Segment-1 and Segment-4 only. If we take the employee record we will be loading Segment-2 & 3 unwanted information also into the computers memory. If your Company have thousands of employees then imagine the computer time and space you need to process the employees salary.

If you split the table into 4 different logical child-tables it will be easier to connect the required segment with Segment-1. When you create child-tables you must include the Employee-Code (Primary Key) information into the child-table also(foreign-key).

B.One-to-many relationship

On a sales counter several salesmen may attend to customers. Each Product sold to customers by a Salesman carries the Salesman-Code besides other product information in the Invoice record. In case of any sales issues the concerned Salesman can be identified and sort out the issue.

Search for Relationships in the MS-Access Help document and get a general idea of relationships and how to use them and when before embarking on complex database design tasks.

http://msaccess-tips.blogspot.com/2015/09/microsoft-access-tutorial-database.html
 
Last edited:

AljayB

Registered User.
Local time
Today, 11:40
Joined
Dec 2, 2015
Messages
19
Thank you both for the reply, I will then try to put them all in one table, because I don't know how to exactly make the relationship work atm..

Attached is the base table that is now in use and which I want to put into access.

could you please take a look if possible please?

many thanks in advance!
 

Attachments

  • 2015-01-02 recythen HDPE F000.zip
    814.6 KB · Views: 83

BlueIshDan

☠
Local time
Today, 15:40
Joined
May 15, 2014
Messages
1,122
Without having the time to look through your previous suggestions. I noticed that you're attaching linking primary-keys to primary-keys. Be sure to create a foreign key within a table when trying to relate to another table.

[ID] is a commonly accepted naming convention for a foreign key in a table that always refers to that tables record.

[{tablename}_id] is a commonly accepted naming convention for a foreign key that refers to another tables [ID] field.

Sorry if this is not enough information. Good luck and good day! =]
 

Tieval

Still Clueless
Local time
Today, 18:40
Joined
Jun 26, 2015
Messages
475
Some food for thought is attached.

I have put a main table with product types related to it.

The idea is to get a unique item which I think is your LFD Nr and then connect it to various common items such as the product type.

You can then use different tables for all these different product types and relate to them numerically and only display the full information when you choose.

You can now make a set of common information for the product type in the second table that can be looked up. So each LFD NR has a number associated to it that references to the other table and only if you wish you can then see all the information about that product type.

You can use this approach for all your quality information and other items in a similar way.

You also have a date field, a month field and a year field, you can just store a date and extract the other two from it when you want.
 

Attachments

  • Database1.zip
    20.4 KB · Views: 85

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Sep 12, 2006
Messages
15,614
Now the problem is, I don't want the 60+ rows in my access table. Therefore I have broken down the table in many small tables that contain the specific data required. for that one material. The problem I face now is I don't know how to link the many different tables to the main table.

I take it you mean columns, not rows.

however, you can't just say arbitrarily that you don't want so many columns. if the data belongs in a single table, then so be it.
 

Users who are viewing this thread

Top Bottom