Help Please! Database Design - Relationships

[ceinwyn]

Hi,
I am just a beginner in access (actually I have started this whole thing when people made me crazy by corrupting my neat excel sheets and messing up formulas). I must confess that I was thinking it would be easier but I knew nothing then, now I am totally aware that this stuff need real expertise but I have already started and told my team that I am working on a database not a wise move I know.
Anyway, I have read a lot of tutorials and I am thankful for all of you who share their knowledge so rookies like me can try something
But, eventually I have stuck somewhere.
I have created my tables (I might be very wrong but they look kinda good to me for now), but I just cannot make them work with a query or form. I know this is "Tables" forum, I am writing here because I think the base problem lies with my tables (well, I guess so anyway).
Please find the layout of my tables below.
My problem is this,
I want to enter all the info via forms (except some limited info like status or type), but some info fields like "customer" or "substrate name" just don't get updated when I try to create a query or a form. They seem to be disconnected but I have connected them on the relationships area. I am missing a fundamental point I guess but after all the tutorials and instructions I have read I couldn't catch it yet.
I can make combo boxes to get the existing data like status or type of job but I cannot get Item Name or Substrate from those pre-filles tables, because I do not know all the Items or Substrates they are changing constantly.
Can you please tell me where should I look to solve this? Tables? Relationships? Query or Form design?
Because whenever I try to create a query or form, I cannot find a way to enter the data and populate the related fields with that data.
I know it is quite long entry, because I am frustrated a little.
Any help would be appreciated, and also I have already looked at the Northwind template, (I just couldn't understand it yet)

Thanks a lot,

 

[JohnB]

Ceinwyn,

Are you sure you ticked the "enforce referential integrity" box when you set up the joins?

you also mention pre filled tables, I believe that if the data was already in the tables when you set up, it may not have been enforced.

I must admit I'm not an expert, but that is where I would start.

JB

 

[Kryst51]

One thing I notice, just by looking at your spreadsheet (Maybe you did this with good reason). Maybe it would help to have lookups in your item tables instead of making a separate substrate table for the substrate Name, type and GSM since they are all one to many to the substrate id, and consequently one to many to the item. There aren't any other fields in the substrate table, so it seems a little uneccesary to have that table. I'll look at it in more detail in a little while. Also, COuld you post the database instead of a spreadsheet? I was going to build the database myself, but would take too much time.

 

[Kryst51]

Also, I don't see a "customer" field anywhere on here. Is it referred to by another name on your spreadsheet?

 

[Kryst51]

Are your job type and status describing the same thing? If so, you may want to think about combining into one table, if it's printed, or reprint gives you same basic info, so there could be so they could be each an option on status. Also, as far as a new order is concerned, if it's a reprint then it is obviously not a new order, so maybe think about having someway of having your report (the order) print "REPRINT" at the top IF the status is "reprint" If not then you would know it is a new order. You could also have a check box at the item level where you could specify whether or not it was a sample, instead of identifying that at the status level. And that could print on the order as well.

 

[Kryst51]

Does more than one factory sheet go on an order? Sorry I am posting so much, but I enjoy this part of databases.

I suggested some changes on your spreadsheet, which I attached here.

I won't be insulted if you don't go with them, the only one I think is critical is the one about the substrate. I think that is important and will cause you problems.

 

[ceinwyn]

No, please I am more than happy to hear some recommendations,
I was unable to send the database because I am currently traveling and I have my macbook with me, and the database was in my pc at office, but I have asked my colleague to send it to me, then I will post it asap.

As for your questions, please find my answers below,
1- SubstrateID table : I thought I need this table because there will be repeating names in the substrates like XXX Gloss 300gsm, XXX Gloss 250gsm, XXX Matte 400 gsm and so on. But now I can see your point, I think I did that after I read something about junction tables You are right, I already have a table for each of them (I was thinking that there was many-to-many that I must avoid but I was mistaken )
2- Sorry for the confusion, I am in the production not sales, so I do not work with actual customers, but brands and we call them RBOs.
3- Status & Type. Yes, it is a good question. Let me give you an example. Lets say that I have an Order with 5 Factory Sheets (yes, 1 order can have multiple factory sheets). And this is a new order-production. But after I print these 5 jobs as a package and changed its status to printed, (lets say 2 days later), one of the factory sheets come back (because of a post production scrap, they need some more print). Well, now I have to give it a new OrderID because it is not a part of the package anymore (the other 4 is gone already), and I can not repeat the factory sheet as it is because it can not be in the new order status twice. So I thought I can solve this by entering "reprint" as a type and making it a unique order and giving it a new order amount (because they probably print less than the original amount-so the amount is not connected to the factory sheet, but connected to the OrderID+FactorySheet composite key - hey look at me talking database terms
Sorry for this detailed explanation but I am going to prepare a short spreadsheet today to show a sample data of my everyday job list. I think it would be easier to see data. And I am going to send the database as soon as I get it.

And thank you so much for your help. You gave me a lot to think about, I think I should start to think a little broader as you suggest.

Cheers

 

[ceinwyn]

And one more thing, I have put the "order type" and "job type" at the OrderID table, because this was the way that I was entering jobs, I see it now that I am still thinking the way that I receive the factory sheets and enter them into the job list. Because as I said 1 order can have multiple factory sheets therefore multiple items, I start entering this order as a package first, then go to the details. Do you think I should reverse this and start by items and then create a package aka order?
hmm, I have to get some breakfast before I can think I guess
looking forward to hear your comments.
(in India currently - GMT+05:30 - sorry if seem to late to answer)

 

[MaZeWorX]

For data entry on forms you can have a control that is bound to a Control Source. The control sourcee is usually a table or query. this means the data entered is written to that field on your table. If it is a combo box you can select the property Row Source and to the right you will see a small square button with 3 dots appear. CLick the button and build the query selecting the fields you to set the row source of the combo box.

fields added should include
Your ID or PK for the underlying table of the query
Any data to display in your combo box

Combo boxes have several other options you should be aware of. By default the Bound column will be "1" which we assume is the PK to written to the data entry table. By default it will only display 1 column however you can set the number of columns as required under the 'Format' tab of the combo properties look for column count. Now that we have the right number of columns we can hide the PK and only display readable data to the user by setting the column widths. Just below coloumn count look for Column widths, they would be set as follows 0;.5";5" this example shows how to set 3 columns the first we hide ... the bound colomn then we set the next 2 to display and set them at .5 of an inch. Also if the column data is wider than your combo you can change the setting List width from Auto << which restricts the list to the combos width >> to a number which represents measurement. In my case inchs so 3 would be 3 inchs this will allow my combo list when selected to be seen without scrolling because we were restricted by the width of the combo box

Setting the Row Source does not effect the Control source
This can also be applied to unbound Combos aswell

HTH M

 

[ceinwyn]

Hi MaZeWorX,
I have played around with combo boxes for a while, but I think I am not there yet. I am trying to sort my tables out before getting into the forms, because whenever I try to make a form to enter an order with multiple factory sheets I get some errors.
But, as you have said, I have no problem using combo boxes for the pre-filled data like Type or Status. They work fine. But when I want to enter a new data like a new factory sheet with a new item underneath it which is also connected to an RBO, well that's where things get messy.
I feel like I need to understand the basic relationship first in order to create the queries and forms correctly, currently I am kinda surfing blindly through the options :S
Thanks a lot

 

[Kryst51]

I look forward to looking at your database, and understanding more of what you are trying to achieve. I am more of a visual and tactal learner, So will know more of what to suggest when I can look at what you are looking at, and play around with it. When I as building my database for work, which is why i learned how to do this, It took me a while to realize, that what occurs logically in a process physically is not how the data those processes produce should be stored and linked. It is a total shift in thinking. It took me months to get something that worked. So don't get discouraged, it sounds like you've already got a fantastic start.

 

[ceinwyn]

Hi Kryst51,
Sorry for the delay, I had a very tiresome flight yesterday and finally got some time to look at this database.
Well, please find the new version attached below, I have made some changes as you suggested, and it seems much better
I have couple of questions ( some of them might make me look stupid though

1- I have put two Yes/No fields as you suggested for Job Type and Order Type. (I put them on Order level). But they only accept one value, am I going to attach the other value ( New Order / Reprint ) in the form? got lost there

2- I have deleted the substrate table but now I just cannot figure out how to link them to the Item?

By the way, I would like to add something, all of my tables (except the Status table) will be empty in the beginning. I want all of them to be populated by the users as they enter the orders.
This also includes the SubstrateType (I have already put some values there but they are not limited to those values, users need to add as a new type comes)

Hope this makes sense, I also attached another xls file to show a small scenario of my orders, and a rough idea how my orders look like when I receive them.

Thanks a lot for your help and support and also for your encouragements Will keep them in mind...

Cheers

 

[Kryst51]

Hi Ceinwynn, np about the posting. I have been on a vacation of sorts anyway, I probably won't be able to look at this until Monday.... But I will look at it.

 

[ceinwyn]

oh yes saturday... weekend... hmm i think i should take a page out of your book and do something funnier than working on databases

cheers

 

[Kryst51]

Good Morning Ceinwyn, Unfortunately, your version of access is higher than mine and I am not able to look at it. Is there anyway you can convert it to 2003 file format?

 

[ceinwyn]

Oops, sorry for that, please find the 2003 version attached.

 

[Kryst51]

Thanks!!!

 

[Kryst51]

OK, I think I need to understand this factory sheet thing a little better. Is this something that is given to you, and you enter the order from?

How is this factory sheet generated?

 

[ceinwyn]

Yes, I have no control over the factory sheets, all the info in them are already entered by the other departments. When we receive them, my team enters them to an excel spreadsheet to keep track of the jobs. And at the end of the week and month I try to extract reports. As I have said earlier, I have no contact with the customer or the customer service, all I want to do is keeping track of my production line. Therefore, I do not have a chance to make any change on the forms before they arrive on my desk.
The way they generate the factory sheets is a problem in itself (there is no "standard way") Some of them comes as manually entered sheets, some of them comes from a order entry system which has nothing to do with me. But the info I put into the database is always there, at least if it is not there, I go and get it
Oh, and one more thing, normally each factory sheet is separate (they might have only one item), but as a production requirement, some factory sheets are stapled together (primitive I know) and brought to me as a bunch. That is why I created the "order" stuff. This is not an actual entity in the system, nobody will see the "order ID", but it will help me track those factory sheets that travel together. Currently my team is entering each factory sheet number in the same cell separated with a comma, which makes it very confusing but there is no other way. Because we are "ganging" these "stapled" factory sheets together in the printing procedure. Therefore, they cannot enter the factory sheets separately. We need a roof to keep them together and I came up with this "order ID" thing.

hope this is helpful,
thank you

 

[Kryst51]

Can a factory sheet have more than one item on it?