Very complex database

[adryan_g78]

Hi there!
I'm new around (your forum and Access) and I really need some help on working with MS Access. First, I will appologise for my bad english.
Ok, so...
I'm working for a knitting factory, doesn't matter which. I must create a database that will be shared with about 7-8 users. This database has to be able to track the production every moment. First, let me tell you how the structure should be:
1. Will have an internal (unique) order (I thought to make this the primary key in all my tables)
2. For each internal order I have to be able to choose from "n" different customers, or to input new ones.
3. Each customer might have "n" orders.
4. Each order might have up to 20 variants of colour and up to 12 different sizes; each size on each variant have another quantity to be worked.
5. Each variant of colour might have up to 6 different colours (let's say this garment has red collar, blue cuffs and yellow hem).
6. The users must be able to insert daily figures required by production flux, so that I can track the orders to see how much they worked and how much it's necesary to finish the order.
...something like that...

My big problem is that I don't know how to make the relationships between the tables. Queryes are also a problem.
Could anyone give me some advices, please?
Many thanks!

[orange]

Please read
http://forums.aspfree.com/attachment...achmentid=4712

to get an overview of RDBMS and Normalization. It will help with understanding relationships.

Another link, and the first few topics are highly recommend

Normal forms, normalization and Entity Relationship diagramming
http://www.rogersaccesslibrary.com/forum/topic238.html

[adryan_g78]

Thanks for your answer orange. Indeed a very usefull documentation.
So, if I got that right, the solution for tracking a garment with, let's say, 4 variants, is to assign a PK for a specific order (garment) and introduce this in a second table (variants) as a FK, right?
Please, keep helping me, as I really need to finish that database.

[orange]

I realize you have a desire to finish your database, but it is very important to get the underlying structures designed to meet your requirements. The proper structure will facilitate almost everything you will do with your database.

You should take all of the facts/requirements and use these to test your model. Reconcile every difference and understand why the difference exists... then adjust the model as required.

Spend time making test data, and again test the model and make sure it handles your requirements. If you skip the model, testing and reconciliation, you do so at your peril. A poorly designed data base will be an exercise in frustration. It is one of those things you just can't do after you've got it working. Tweaks are fine and always occur, but major structure changes will best be handled by starting again.

I recommend you read, and use this as a guide
http://www.rogersaccesslibrary.com/T...20Nutshell.doc

Good luck with your project.

[adryan_g78]

Thank you orange!
I'll try again and again untill I'll get it work. I'll leave this thread open, maybe I'll have some other questions on the future.
Thanks again!

[orange]

No problem, glad to offer some assistance.

Make a data model, make some test data -> test the model -> refine as necessary.

[adryan_g78]

Hello, my friends!
I've managed to make a database that suits my needs, after few lost nights, but now I have another problem.
In a form that I've created, I want to insert a picture. It's easy, but the hard part (for me) is that I want a different image for each record (as I have different products to enter). So, I want to be able to choose, from a dialog box eventually, a picture for every record I make.
Oh, and later, when I'll create reports for this database, to show me that image in certain report based on a field (let's say a dropdown box) for which I will choose the value.
Do you think you could help me? Thanks in advance!

[orange]

This may help
http://support.microsoft.com/kb/285820

Try google

Ms access images in reports etc.


There is a lot of info on Access Forms etc here
http://www.jegsworks.com/lessons/dat...reyoustart.htm

[adryan_g78]

Thanks, orange, it was very helpfull.
Now, could you please help me with something else?
I have a form and I want to assign three actions for the same button.
For example, when I create a button, I choose from the actions list to insert a new record when I click it. But in the same time I want to close that form and open a new one.
I need a VBA code for this, isn't it? But I don't know how to do it.
So, when I click the button I want to:
1. Add a new record (this will do it Access automatically)
2. Close this form.
3. Open another form.
Thanks a lot.

[orange]

In general terms, if you have created a form bound to a table and you use the wizard to create a button for adding a record, you would go to the yourButton_Click event procedure, and add some code to (untested, but general approach)

DoCmdClose ' to close the current form and a
DoCmd.OpenForm "yourFormName" 'to Open the desired form

[adryan_g78]

Exactly wath I needed!
Thanks a lot, man!

[orange]

You are welcome.

[adryan_g78]

Hi, orange!
Could you please help me with something else?
When I'm opening the first form, I want it to automatically go to a new record. How do I do that?
Thanks in advance!

[orange]

Try
DoCmd.GoToRecord , , acNewRec

[adryan_g78]

Hello again!
I've finally managed to understand how my database has to be built and all those tutorials about normalization and redundant data and relationships and.... so on
At least I think I did.
Now I would need a piece of advice again. I have 3 tables: CUSTOMERS, ARTICLES AND VARIANTS.
CUSTOMERS has only one field, the PK, called Customer_ID.
ARTICLES has the following fields: Article_ID (PK), Customer_ID (FK), Article description... and so on, it's not important.
VARIANTS has the following fields: ID (PK), Article_ID (FK), Variant name, Size1Qty, Size2Qty.....Size12Qty, TOTAL_VARIANT_QTY.
Let me give you an example of data that I've tried to work with:

ID Article_ID Variant name Size1Qty Size2Qty .... Size12Qty TOTAL_VAR_QTY
1 TRY1 A 5 10 5 50 (let's say)
2 TRY1 B 7 14 21 70 (let's say)
3 TRY1 C 10 10 15 100
4 TRY2 1- BLACK 2 4 6 30
5 TRY2 2 - BLUE 5 10 15 60

Ok, I've made it calculating the totals BY VARIANT using a query, but I want let's say another table which will have fields that calculates automatically the totals BY SIZE and BY ARTICLE, e.g. (from the example above) I will have:
ARTICLE SIZE1TOTAL SIZE2TOTAL........
TRY1 22 34
TRY2 7 14

What do you think? It can be done? I will also want those figures to show up into a report later.