table relationships issues (1 Viewer)

Hi, I take this opportunity to greet you all, for it is my first participation in this forum
I am an inexperienced Access user and have recently started designing a database in my company. I'm making a database in which I have 5 tables:


- A table with customer data (key field "customer code")
- A table with order data (key field "order", is an order number) where customer and order data appear (there is no product data here)
- A table with order and product data (2 key fields: "order" and "product code"), where product data for each order appears.
- A table with product data (key field "product code") with all existing products and their associated logistics.
- A table with data of the packaging area and incidents. (key "order" or/and "product code" field), where product data for each order (product code only) and associated incidents appear again.


My intention is to make an application with a form consisting of 3 tabs (tabbed form, following a video that I have seen on youtube): one for general order data, another tab with products and quantities, and another tab with data and incidents arising in the area of Packaging.


To do this, I will make queries with several tables for each form (one query for the order data, another for and insert 1 main form (with general order data), another subform (with product and quantity data) and a third subform (dependent on the other two) with data for the packaging area. For this application, I need to set passwords to form 1 (orders) and 2 (product relationship of the order), and leave free access to form No.3 (packaging data). I'm interested in making a separate table with the packaging area data because I think to give permissions to the users of each form, I have to do the 3 forms separately.


The main difficulty is that I don't quite know how to join the table with data from the packaging area to the other tables. When doing the different combinations and testing queries from multiple tables to make the subforms (for possible incidents, in the query I use the "order" field of order table, field "product code" of table products orders, field "incident" packaged data) , I can not retrieve data, because I get the fields from the table result of the blank query and the form also appears blank.


In my (inexperienced) opinion, the sequence of relationships should be:
- Customer table customer code (relationship 1 to many) with order table order
- Order table order order (relationship 1 to many) with item code of table products-orders
- Product code of ordered product table (ratio 1 to 1 with) with table product code packaging data
- Packaging data table product code (many to 1) with product code of product table.


Notes: I think the product code is repeated too much as it serves as a join in 2 relationships and I have seen that the key field is not usually repeated as a join. I have doubts whether the relationship between product code of product ordering product and product code of packaging data table should be 1 to 1.

Thanks in advance

Hi. Welcome to AWF! It might be easier to give you design guidance if we could actually see your database. Are you able to post a sample copy with test data?

Hi yavuka
Welcome to AWF.
I've deleted multiple duplicate posts which were moderated. This can happen with new members especially if the posts contain code, attachments or (in this case) are quite long.
If it happens again, please be patient and one of the mods will approve it fairly quickly.

If you do post your database as DBG suggests, you will need to zip it first.

EDIT
Just noticed this is crossposted at https://www.accessforums.net/showthread.php?t=78565&p=441201#post441201.
Please read this article about crossposting https://www.excelguru.ca/content.php?184

Sorry, Isladogs, I usually don't post in forums. I read the link and I agree with it, so I closed the other post to discuss here.


I had to merge product table information with packaging data table to make the query work, but I'd prefer have products information and data packaging in different tables.


I have another question about batch fields (now in products table, before in packaging data). Same product for same order can have different batch codes It depends on production day, I have created 7 fields (batch day 1, batch day 2, batch day 3...) in packaging data table. I think it ins't optimal, but I can't figure out how to do a individual batch table to get just one batch field for different days to one record for the same product. Thanks for the help.

I dont have enough permissions to post links, sorry

you need to create another table for those batches (not in product table).
add fields like product code, batch no, qty, date.

You appear to have done more analysis than many "newbie" posters/database builders and have a reasonable vocabulary with tables and relationships, forms and probable user access approach.
However, as always, the devil is in the details.
This Database Planning and Design link has many useful articles that may help you to put the various concepts, requirements, naming conventions, modeling and techniques into a design that you can test and vet before getting too deeply into physical database.

Good luck with your project.

Thank you so much, very interesting information, I'll read it carefully.