MultiValue Fields for use with Order Table- Can it be done this way (1 Viewer)

[rm.harper]

I have an order table. Each Job has 1-3 types of material ordered.

Can i used a multivalue look up box or would i have to create 2 additional fields:

Material1 Material2 Material3

Each material could be ordered from a different supplier also so the same rule would have to spply to my supplier field dependent on the correct answer

....and leave blank if less that 3 materials are used?

Cheers
R M Harper

 

[willknapp]

Multivalue fields can get tricky when it comes to reporting, so I tend to steer clear of them. If I were tasked with building this system, and I understand you correctly, I would consider the following:

I'd want to truly normalize this database, so I'd probably want to handle this with a number of different tables

Orders Table
Suppliers Table
Materials Table
OrderDetails Table (One Order to Many OrderDetails) made up of Primary Keys from the Orders, Suppliers, and Materials Table.

When an order is created, you can then add the details about the Material and Supplier. Theoretically, you can add as many of these detail records as you want, though according to your description, it would max at 3 (which you could control programatically).

This would be my suggestion to maintain the data integrity.

 

[jdraw]

I agree with Will. I would not use the multivalued field. I'd build the appropriate table.

Here are some videos on database design including Normalization. The video quality is alittle less than desired, but the message should help most readers.


http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

 

[rm.harper]

Multivalue fields can get tricky when it comes to reporting, so I tend to steer clear of them. If I were tasked with building this system, and I understand you correctly, I would consider the following:

I'd want to truly normalize this database, so I'd probably want to handle this with a number of different tables

Orders Table
Suppliers Table
Materials Table
OrderDetails Table (One Order to Many OrderDetails) made up of Primary Keys from the Orders, Suppliers, and Materials Table.

When an order is created, you can then add the details about the Material and Supplier. Theoretically, you can add as many of these detail records as you want, though according to your description, it would max at 3 (which you could control programatically).

This would be my suggestion to maintain the data integrity.

Thanks for the great advice. I did some deeper research and managed to get hold of an order management template, this is designed exactly how you describe. Ive already attempted to build the base system about 3 times, and scrapped all attempts as i did not like how i had arranged my information.

If i do a database map would you take a look at it for me? If you are busy with other things please dont feel the need too, just if you have a few minuted spare would be a great help, save me programming again to then have to scrap. If i map my database first and have someone give it a once over i can finally get this thing up and running and if additional functions are needed they can be added at a later date. At the minute i just want the fundamentals to work so i can begin testing this as i start to use it.

Thanks again

R M Harper

 

[spikepl]

Do not attach yourself to one particular contributor like a leech

Post your issue and data here, and whoever feels like it will help out. In this instance, attach a screenshot of the relations window (expand all tables and de-mess it prior to posting)

 

[rm.harper]

Do not attach yourself to one particular contributor like a leech

Please don't refer to me as a leech, its just plain rudeness on your part. A user was present so the question was asked.

If i was wrong to do that then THIS reply would of sufficed:

Post your issue and data here, and whoever feels like it will help out. In this instance, attach a screenshot of the relations window (expand all tables and de-mess it prior to posting)

No need to get insulting towards new members

and to just correct your point:

If i map my database first and have someone give it a once over i can finally get this thing up and running and if additional functions are needed they can be added at a later date.

If someone would be so kind to just look through this document it would be appreciated, This is a typed up version of what i want my database to do, relationships can be seen using the colour coding on field names.
Thanks in advance

 

[spikepl]

To set the record straight - it is not customary nor generally accepted nor necessary practice to direct help requests at specific people, because that would overwhelm them in no time, and other quielifed helpers abound. That you feel sensitive about being figuratively speaking compared to a leech I am unable to do anything about.

You were asked to provide a screenshot of the relations window, not a story. The relations window is so much easier to comprehend than a story, and you would not want to impose undue burdens on your helpers, would you?

If you do not know what the relations windows is, then try scanning the ribbon for suitable text in the Database Tools section.