I want a solution to the problem of creating relationships between tables (1 Viewer)

[Roody_Asa'ad]

Hello all
I'm new here in this forum and I need your help regarding creating program repositories
I created the tables and when creating the relationships between them I encounter big problems
Required:
1- I want to create relationships between tables
2- I want to create a form for the material card
3- I want to create a sales invoice
As for the sales invoice, I want to choose the name of the store. All the shelves that contain this store appear in the Shelves field. When choosing the Shelf field, all the materials that belong to this shelf appear in the other field.
4- I want to create a purchase invoice
5- I want to create reports

 

[GaP42]

Welcome to Access World.
Relationships are established between tables using the identifying attributes of rows in the tables - usually Primary Keys (PK) in one table to the equivalent item in another table (the Foreign Key) so that the records have a relationship that can be used to control referential integrity. There are formal methods to the design of tables - referred to as normalisation. Your table design as far as it has gone will not support all the requirements you have outlined above. You have not defined/designed the tables which will hold Material or Invoice.
Irrespective of that, taking the Table "Store Card" and "Shelves Card" if I assume this is about the shelves in a store, then the PK of Store should appear as a FK in the Shelves Card table - not the name of the store. So instead of "store" in the Shelves Card table you would have "Kod_Store"

Review your tables and include the appropriate FKs in each table that relates an item in one to those in another. However - you also seem to have scattered "Store" across all your tables. Not likely to be needed - eg Items - if you locate Items on a shelf (in the Item Card table) and a shelf record relates to a store then the Kod_Store would not be required in the Item Card table.

 

[bob fitz]

I would advise against the use of Lookup fields in tables. See: http://access.mvps.org/access/lookupfields.htm

 

[bob fitz]

Search YouTube for videos on: "access table relationships".

There are lots of them. Here's one I found:

 

[mike60smart]

Hello all
I'm new here in this forum and I need your help regarding creating program repositories
I created the tables and when creating the relationships between them I encounter big problems
Required:
1- I want to create relationships between tables
2- I want to create a form for the material card
3- I want to create a sales invoice
As for the sales invoice, I want to choose the name of the store. All the shelves that contain this store appear in the Shelves field. When choosing the Shelf field, all the materials that belong to this shelf appear in the other field.
4- I want to create a purchase invoice
5- I want to create reports

Hi Roody
Welcome to the Forum.

In order for us to help we need a more detailed description of the process you need for entering data.

 

[mike60smart]

Hi Roody
Welcome to the Forum.

In order for us to help we need a more detailed description of the process you need for entering data.

Hi Roody
Your database contains just a number of tables.
What you need to do is express your business model in a short sentence.

An example would be:-

A Customer on a given Date can Order 1 or more Items.

This is then broken down into related tables.

tblCustomers
-CustomerID - PK _ Autonumber
-Customername

tblCustomerOrders
-CustomerOrderID - PK - Autonumber
-CustomerID - Number - LongInteger (Linked to PK in tblCustomers)

tblOrderItems
-OrderItemID - PK - Autonumber
=CustomrOrderID - Number - LongInteger (Linked to PK in tblCustomerOrders)
-Item
-Qty
-Price

See if you can use this to relate your current tables.
First delete your current Primary Keys.
You will then need to add Primary Keys to Every table.

 

[jdraw]

Relationships between tables in a relational database are a representation of "business rules" within your business. The link has examples.

In plain English-- What is your business? What are the major things and activities"

 

[XPS35]

1- I want to create relationships between tables
Go ahead. Make sure data types are the same in related tables.

2- I want to create a form for the material card
I don't see a table named liked that. If you mean items card, again: go ahead.

3- I want to create a sales invoice
I don't see a sales tables.

4- I want to create a purchase invoice
I don't see a purchase table.

5- I want to create reports
Just do it!

Bottom line: what are your problems?

 

[Pat Hartman]

I created the tables and when creating the relationships between them I encounter big problems
Required:

It would be helpful if you told us what problems you found. I found problems so I can start.

"card" is probably some artifact from the company's manual system and may or may not be the equivalent of "table" or "entity". Looking at the tables, I would expect Store to be the primary ID of Store_Card but it is not. Store is the PK of Export_Card. I don't know what "export" means in this context so I'm not sure what to tell you. I would expect it to be something like a sale but that makes no sense with Store as the PK to the Export table. I have the same problem with Import_Card. It appears to be a purchase of inventory. Again this table has a PK of Store which makes no sense. Borrowing_Card sounds like another inventory transaction like lending a book but it also doesn't have any useful linking fields. I'll take a stab at how I think the tables are related and create a relationship for you

I added a table named tblItemShelf because it is possible that one item type might be on multiple shelves and I moved quantity to that table so that it is a function of that item on a specific shelf. You have to add all the rows for an item in tblItemShelf to find the total quantity of an item.

I realized that as I got to import/export/borrowing that I don't know enough about your application to continue.