Table structure messed up?

[jesswoo]

Hi,

It has been a while since I made a post about a database I am working on for my job. I was a complete novice and then after playing around a bit I figured out some things. Now I have gotten to the part where I need to do some advanced formatting but I am not sure how to do it and I think that my table structure might be what is preventing me from doing certain things.

I will post a copy of the database on here but ask the question about advanced formatting in the proper forum.

I have tried code and macros to perform different operations but they are not working and I think how my tables are set up is the problem.

Any responses are greatly appreciated

 

[jesswoo]

Ohh Just as some additional info this database is used to create process order forms and to keep track of part information.

Each part is unique to a customer. Each customer can have many parts. Each part only has one color but each color could be used for multiple parts.

The main reason for this is to create orders, display them in a schedule and to have the part information saved for future use.

 

[plog]

I don't see anything major, just a few issues. If you had some sample data in there it would be more helpful. Here's what I see:

1. Field Names: don't use spaces or any non alpha-numeric characters except an underscore. Just makes writing queries and code easier.

2. tblPaint: [Oven Temp] and [Cure Time] should be numeric fields, not text. I would even rename the fields to include the unit you will be using (i.e. OvenTempCelsius, CureMinutes)

3. tlbPArt: Same issue as #2 above. I bet some of those text fields should be numeric. Example data would let me tell you which, or I'm sure you can figure it out yourself.

4. tblPaint: who creates [PaintID]? Is it determined by the supplier of the paint or is it something you create?

5. tblPaint - tblPart: You have [ID] which is an autonumber and [PaintID] which is text in tblPaint. In tblPart you are linking to tblPaint by [PaintID], this is wrong. You should be using [ID].

6. tblCustomer - tblPart: You don't have a relationship set up between these two, but you say it exists. Based on what you said, tblPart should contain a foreign key from tblCustomer. With that said, I would create one--make [CustomerID] in tblCustomer and make it an autonumber. Then create a field in tblPart to hold that value.

 

[jesswoo]

The Part ID cannot be numeric, some parts are just a number and some have letters attached. [LM-4563] is a sample part number. Should I have an auto number ID assigned to the parts also?

For tblpaint The PaintID is the product code given on the box of paint. I added the auto number ID because I thought maybe that was why the relationship was not working.

Should I have autonumber fields for all of the seperate info? I mean Part, customer, Paint?

Also if I do use auto number ID fields when I pull the information in a query to auto fill some text boxes will it pull just that auto ID or can it pull the actual part /paint ID? When I last tried to use auto fill with this it would only copy the auto number assigned to the paint and we have about 300 different types of paint so it would not be easy to remember what number each is..

 

[plog]

You have an autonumber ID assigned to parts--[PartID].

You have autonumber fields for all the seperate tables (Part, customer, Paint).

In a query you link by id fields and then pull in all the other information you want once that relationship has been established by linking them. I have no idea what 'auto fill' means to you.

This would be much easier if you responded to the issues I raised by referencing the number I used.

 

[jesswoo]

You have an autonumber ID assigned to parts--[PartID].

You have autonumber fields for all the seperate tables (Part, customer, Paint).

In a query you link by id fields and then pull in all the other information you want once that relationship has been established by linking them. I have no idea what 'auto fill' means to you.

This would be much easier if you responded to the issues I raised by referencing the number I used.

Sorry. After I read over your Post I took a look at my tables again and created a new test database using ID auto number Fields. I took out all Spaces and changed all fields that I could to numeric. I was able to enable referencial integrity with cascading update and restrict delete. I will upload the test database, it has some sample data in it, the fields I haven't added anything to are fields I am not to worried about because they won't be used in anything but a report.

To your above response, by autofill (I still do not know if this is the right way to phrase it) I mean that I have a tblProcessOrders and when I select a PartNumber I want the PartDescription, PaintCode, and PaintDescription text boxes to fill in. I am pretty sure I will need some code for this, waiting on responses for my other post on the "autofill" subject.

Can you please take a look at what I have now and let me know if I am on the right track with my tables and relationships in this new DB?

Thank you so much for all the help so far!

 

[plog]

I know, making colorful forms and loading clip art into reports in Access is the sexy part of making a database. Everyone loves to gloss over the drudgery of setting up tables properly to get to that, but you really need to focus on your table structure before you decide what font to use on your forms.

So let's forget about forms for a week or so and work on your tables, you still have a lot of issues. In fact, with that new table you added, you have more than your initial posting.

Your relationships should not look like a spider web--once a relationship is established between a table and another, when you bring a third table into the mix you only need to establish a relationship between it and one of those previous tables--not both.

I honestly don't know what you are trying to do with tblProcessOrder, but I do know you shouldn't have a link between it and tblCustomer and tblPaint since it also has a link to tblParts, which itself has a relationship to tblCustomer and tblPaint. You've got redundant relationships.

I think we need to start from scratch. In english, with as little database jargon as possible, what is this database for?

 

[jesswoo]

I know, making colorful forms and loading clip art into reports in Access is the sexy part of making a database. Everyone loves to gloss over the drudgery of setting up tables properly to get to that, but you really need to focus on your table structure before you decide what font to use on your forms.

So let's forget about forms for a week or so and work on your tables, you still have a lot of issues. In fact, with that new table you added, you have more than your initial posting.

Your relationships should not look like a spider web--once a relationship is established between a table and another, when you bring a third table into the mix you only need to establish a relationship between it and one of those previous tables--not both.

I honestly don't know what you are trying to do with tblProcessOrder, but I do know you shouldn't have a link between it and tblCustomer and tblPaint since it also has a link to tblParts, which itself has a relationship to tblCustomer and tblPaint. You've got redundant relationships.

I think we need to start from scratch. In english, with as little database jargon as possible, what is this database for?

Ok, thanks for being so patient.

With this database I need to be able to enter in jobs for our shop and keep track of part information. I get a purchase order, put the info in(the fields in tblProcessOrder), and I had a query that pulled active orders and I created a shop schedule from that so that it can be viewed on the crt screen in our shop and the shop manager could change around the order of the jobs to when they will be run.

The parts themselves never change, they are always the same color, for the same customer, with the same packaging information. We are starting a second shift soon and we will have new people that do not know the parts or what to do with them and I need to be able to keep track of the history for each part so when the order comes in and they see it on the schedule they can go in and look at the specs for that part.

I may be going about this all wrong but I have pretty much based everything off of the part because it is the only thing that does not change.

I will eventually need to be able to keep track of inventory from this as well but I havent even started imagining how to do that.

Thank you again.

 

[plog]

Tell me if any of this is wrong:

Customers can place multiple Orders
1 order is comprised of one company and multiple Parts
Each part has only one type of Paint

You have any other tables you've left out?

 

[jesswoo]

Tell me if any of this is wrong:

Customers can place multiple Orders


1 order is comprised of one company and multiple Parts


Each part has only one type of Paint

You have any other tables you've left out?

Customers can place multiple Orders
Yes, customers can place multiple orders


1 order is comprised of one company and multiple Parts
Yes, some orders may only have one part but most orders have a few


Each part has only one type of Paint
Yes, the paint can be used for multiple parts but the paint for each part never changes.


You have any other tables you've left out?
I need to create a table similar to tblPaint but it is for the shop supplies such as bags, foam and masking materials. it would be set up the same with an ID, ProductCode, Description and location. These things are also specific to each part.

 

[plog]

So parts are comprised of bags, foam and masking materials as well as paint?

 

[jesswoo]

So parts are comprised of bags, foam and masking materials as well as paint?

For the most part. A part is going to either be bagged or stacked with foam, EVERY part will have one or the other. There are different sized bags and each part has a specific bag size but each bag size can be used on different parts.

There are not a whole lot of parts that require masking but I create Procedure booklets for all parts that are masked and they need to be attached to the parts information as well. The masking is something that is specific to each part and no 2 parts are going to have the same masking procedures.
The only thing with masking that I would need to keep track of are the plugs in stock. Just like bags there are different sizes, but they are not specific to anything and I wouldn't include it in a parts information because it could change depending on the results of the previous job.

 

[plog]

At this point I think you need to start relatively over from scratch. Work up a new database and include a table for every entity, but don't worry about linking them yet.

Here's the entities I see so far:

Customers
Parts
Paints
Bags
Foam
Masking

Make a table for each and include attributes that are only directly related to that entity. That means at the end of this you will not know what part goes with what bag or what customer has ordered what part or what paint goes with a part. We will work on the relationships later. Right now, just the entity specific fields.

 

[bigalpha]

I don't know if it's good convention, but I have gotten into the habit of using Autonumber primary keys for each of my tables, even if one of the fields in the table is supposed to be unique. I find that it prevents you from having to rebuild queries and such later on down the road, if any of those supposed unique values turns out to not be unique.

 

[Simon_MT]

I know this late intervention but I rarely use Autonumbers as an identifer the reason being when you look at a screen ID 266 represents OOC which stands for Oil on Canvas while ID 363 represents OOB which stands for Oil on board. You can immediately recognise the OOC and OOB codes whilst 266 and 363 are meaningless.

My point is if you have a part with a identifable reference or is labeled with a Manufacturers Part Reference you can use a more distinguishable reference.

Simon

 

[bigalpha]

I know this late intervention but I rarely use Autonumbers as an identifer the reason being when you look at a screen ID 266 represents OOC which stands for Oil on Canvas while ID 363 represents OOB which stands for Oil on board. You can immediately recognise the OOC and OOB codes whilst 266 and 363 are meaningless.

My point is if you have a part with a identifable reference or is labeled with a Manufacturers Part Reference you can use a more distinguishable reference.

Simon

Isn't this why you use forms to view and edit data?

 

[jesswoo]

At this point I think you need to start relatively over from scratch. Work up a new database and include a table for every entity, but don't worry about linking them yet.

Here's the entities I see so far:

Customers
Parts
Paints
Bags
Foam
Masking

Make a table for each and include attributes that are only directly related to that entity. That means at the end of this you will not know what part goes with what bag or what customer has ordered what part or what paint goes with a part. We will work on the relationships later. Right now, just the entity specific fields.

Ok here is what I got. I thought about adding quantity fields in the paint, bag, foam, and plug tables but honestly I do not know how that should be done...

Also, should I have created an orders table?

 

[plog]

Here's somethings I see:

1. tblBags, tblPlug, tblFoam: Whats the difference, in purpose, between ID and ProductCode in all those tables? Where/how does product code get assigned? Can a Part have more than one Bag, more than one Foam or more than one Plug?

2. tblBags, tblPlug, tblFoam: Can a Part have more than one Bag? More than one Foam? More than one Plug?

3. tblBags, tblPlug, tblFoam, tblPaint: All of these tables have a location field--are all these locations related or from the same table? You didn't include it if they are. Also, all of them are numeric fields except the one in tblPlug, why is that?
4. tblCustomer: ContactPhoneNumber should be text. If you search around you will find an input mask for phone number so that it formats properly when entered and displayed.

5. tblFoam: FoamLength probably should be numeric and be renamed for the units of measurement it represents: FoamLengthInches, etc.

6. tblPart: BagSize probably shouldn't be in this table. Isn't that going to be the same value as tblBags?

 

[jesswoo]

Here's somethings I see:

1. tblBags, tblPlug, tblFoam: Whats the difference, in purpose, between ID and ProductCode in all those tables? Where/how does product code get assigned? Can a Part have more than one Bag, more than one Foam or more than one Plug?

2. tblBags, tblPlug, tblFoam: Can a Part have more than one Bag? More than one Foam? More than one Plug?

3. tblBags, tblPlug, tblFoam, tblPaint: All of these tables have a location field--are all these locations related or from the same table? You didn't include it if they are. Also, all of them are numeric fields except the one in tblPlug, why is that?
4. tblCustomer: ContactPhoneNumber should be text. If you search around you will find an input mask for phone number so that it formats properly when entered and displayed.

5. tblFoam: FoamLength probably should be numeric and be renamed for the units of measurement it represents: FoamLengthInches, etc.

6. tblPart: BagSize probably shouldn't be in this table. Isn't that going to be the same value as tblBags?

1. tblBags, tblPlug, tblFoam: Whats the difference, in purpose, between ID and ProductCode in all those tables? Where/how does product code get assigned? Can a Part have more than one Bag, more than one Foam or more than one Plug?
The ID I just put in there because I thought I needed it, it has no corellation to the actual product code. The product code is item number the manuafcturers of the bags, foam, plugs give them. The same number we use when ordering them.

Orders will consist of at least 20 parts, each part will get a bag. For foam, it comes in a big roll and we tear sheets off the roll and each part will have a shet of foam on top of it between other parts. The amount of plugs used depends on how many holes or threads on the part.

2. tblBags, tblPlug, tblFoam: Can a Part have more than one Bag? More than one Foam? More than one Plug?
Orders will consist of at least 20 parts, each part will get a bag. For foam, it comes in a big roll and we tear sheets off the roll and each part will have a shet of foam on top of it between other parts. The amount of plugs used depends on how many holes or threads on the part.

3. tblBags, tblPlug, tblFoam, tblPaint: All of these tables have a location field--are all these locations related or from the same table? You didn't include it if they are. Also, all of them are numeric fields except the one in tblPlug, why is that?
For locations we have two areas, 300 and 400. Bags, foam and plugs would be in 300 and paint is in 400. The locations are a rack number, ailse number and shelf number so a paint would be something like 400 1-2-1 and a bag, foam, or plug would be 300 1-1-2
I must have forgot to change tblPlugs location to numeric, it is the same.

4. tblCustomer: ContactPhoneNumber should be text. If you search around you will find an input mask for phone number so that it formats properly when entered and displayed.
Input mask! I was trying to remember which property formatted the address and I couldn't remember.

5. tblFoam: FoamLength probably should be numeric and be renamed for the units of measurement it represents: FoamLengthInches, etc.
I will change this

6. tblPart: BagSize probably shouldn't be in this table. Isn't that going to be the same value as tblBags?
Im not sure I understand... I put the bag size in since it is part of the parts information needed when running the job. Should I take the paintCode out too?

 

[jesswoo]

Here's somethings I see:

1. tblBags, tblPlug, tblFoam: Whats the difference, in purpose, between ID and ProductCode in all those tables? Where/how does product code get assigned? Can a Part have more than one Bag, more than one Foam or more than one Plug?

2. tblBags, tblPlug, tblFoam: Can a Part have more than one Bag? More than one Foam? More than one Plug?

3. tblBags, tblPlug, tblFoam, tblPaint: All of these tables have a location field--are all these locations related or from the same table? You didn't include it if they are. Also, all of them are numeric fields except the one in tblPlug, why is that?
4. tblCustomer: ContactPhoneNumber should be text. If you search around you will find an input mask for phone number so that it formats properly when entered and displayed.

5. tblFoam: FoamLength probably should be numeric and be renamed for the units of measurement it represents: FoamLengthInches, etc.

6. tblPart: BagSize probably shouldn't be in this table. Isn't that going to be the same value as tblBags?

I forgot here are the changes I made, I left Bag size in tblparts because I was not sure.