Simple ordering form

[jammerculture]

Job #	3" EMT conduit	3" EMT Strap	3"EMT Connector	3" EMT coupling	1/2" PVC Conduit	1/2" PVCStrap	1/2" PVC connector	1/2" PVC coupling
1	400	8	2	6
2					200	4	2	8
3
4	100	2	5	6	300	1	2	8
Totals	500	10	7	12	500	5	4	16

Here we have a simple ordering form. Each item is a column, each job a row. It can be used to total the common material that is used for each job. It is a simplified version, as the actual spreadsheet would have hundreds of column. I want to move this process into excel [MODERATOR NOTE: meant Access?] in order to cut down on some of the inefficiencies and give me the ability to use the data most effectively. Notice that each item has a size, a material and a description. These sizes and materials are duplicated. Ideally I would like to create a form for job takeoffs. Each attribute would be a job drop list for size, material and description. The data would be entered into a table. A report could be generated which would total the materials needed for all the jobs in a given period and an order would be put in based on this report. Can anyone help me design a database that would allow me to do this in the most efficient way possible. I am relatively new to Access and I want to learn, but I want to do it by working on problems specific to my application instead of learning by creating databases of my record collection like many tutorials do.

[recyan]

Till some one comes along, something to think about:
tblItems

ItemID_PK    ItemName    
    
1    Conduit        
2    Strap        
3    Connector        
4    Coupling 

tblItemVarieties

ItemVarietyID_PK    Size    Material    ItemID_FK

1    3"    EMT    1
2    3"    EMT    2
3    3"    EMT    3
4    3"    EMT    4
5    1/2"    PVC    1
6    1/2"    PVC    2
7    1/2"    PVC    3
8    1/2"    PVC    4 

tblJobs

JobID_PK    JobName   
     
1    A        
2    B        
3    C        
4    D 

tblJobItems

JobItemsID_PK    JobID_FK    ItemVarietyID_FK    ItemVarietyIDQuantity

1    1    1    400
2    1    2    8
3    1    3    2
4    1    4    6
5    2    5    200
6    2    6    4
7    2    7    2
8    2    8    8
9    4    1    100
10    4    2    2
11    4    3    5
12    4    4    6
13    4    5    300
14    4    6    1
15    4    7    2
16    4    8    8 

Thanks

[Rod]

May I suggest you rotate the form through 90 degrees and have the products as rows and the jobs as columns. I assume there are many products and fewer jobs.

I thought about a pivot table for the form but then dismissed the idea - too much functionality for the task in hand.

recyan's data model is a good starting point but I think should be reviewed a little more.

1. The 'tblItems' is perhaps misnamed; these are really item categories or families so I would indicate this in the table name.
2. The 'tblItemVarieties' is in fact the item table. If I was a storeman, these are the things I would be stocking.
3. You may want to consider a table for material. I don't know what extra benefits you may get by this approach apart from a level of checking data consistency.
4. I wouldn't do the same for sizes unless they indicate a family - which I suspect in your case they do.

Here's my first cut db (click on it to open in another window):



Note that there is no item description. This can be generated each time from the three source files: item categories, materials and sizes. However it would be a forgivable denormalisation if you decided to generate the description once (and only once) as you added the new item and stored it in the item table.

If you wish to take this further I can suggest a query for extracting the full item data. Don't carry the impression that forms for 'real life things' have to be based on a table; more often than not they are based on an aggregated view of your data, i.e. a query.

[jammerculture]

thanks for the replies guys. when i saw the first one, i thought it was close to what I wanted, but there was still the redundancies in tblitemvarieties. my first thought was size and material needed their own tables, which rod confirmed in his design. the system of measurement in tbl size answers what was going to be one of my next questions.

I will work on this tomorrow and get back. what I want is actually much more complex, for example there is other classes of material that aren't rated by size, and different jobs require different types of material, but this is a great place to start.

I apreciate the help and hope you stick with me.

rod, one question, what did you mean by "unless they indicate a family" in reference to size?

[Rod]

... unless they indicate a family ...

What was going through my mind was best answered by example. Let's take piping. In Imperial measure it is supplied in 2", 4", etc. sizes. You may order a 2" elbow or a 4" tee, and so on. The measurements represent families of pipework. There are no intermediate sizes in the continuum. In this case it is probably worthwhile setting up a size table so that all 2" members of the family are consistently and conveniently grouped.

If however size is simply the measurement of a dimension then it is silly if not impossible to set up a size table. This one is 2", the next one is 2.4", the next 2.3", and so on. As an example, think of the sizes of rooms in houses for sale at an estate agent; it's impossible to group them by exact size although area ranges might be useful.

[jammerculture]

ok so now I have a table for each attribute of an item which is described in tblItem, which is "what's on the shelf". I have filled in the attributes in each of tblSize, tblMaterial and tblItemCategory. Is there a way to populate tblItem with all the combinations of these attributes or do I have to manually enter each one? Also, although it makes sense to store the data in tblItem as the Id fields from the other tables, it might not be the best way to enter it. How would I create a form that would allow me to enter "3 inch EMT conduit" and have it populate the tblItem awith the ID #s? <P> also, before we get too far ahead of ourselves, it might be important for me to bring up that for simplicity sakes I have been discussing conduit and accessories, which is described by size, material and category. However there are many other material types that I use, cable, switches etc. These material types have different attributes from each other. Cable for example is described by gauge, number of conductors and type. In the eventual job takeoff form the first thing you would do after naming the job is determine wiring method, say conduit or cable. Depending on your choice you would then have different parameters to fill out. Right of hand I think I would need a tblItemClass, and then a table for each Item class that would define it's attributes, and then a table for each of the potential attributes. Am I on the right track with this line of thinking? Is there any type of limit to number of tables that I can or should use? Am I shooting myself in the foot trying to design something this complex in this fashion? I need to learn how to use Access and how to Design a database and my thinking is that by learning through working on my project I can do both at once, however this may not be the best course.

[jammerculture]

I'm having trouble with this site, I can't save once I edit a post and for some reason it won't let me create paragraphs. Weird, anyway another question. We have a tblSize with unit of measurement as a field. Since conduits are measured in inches whereas enclosures are measured in square inches ie, 4x4, 6x6, would I store this data in the same table or rather have a tblCondSize and tblEncSize?

[Rod]

In no particular order.

I'm having trouble with this site ...

Report your problems via the 'Contact Us' link at the foot of each page.

We have a tblSize with unit of measurement as a field. Since conduits are measured in inches whereas enclosures are measured in square inches ie, 4x4, 6x6, would I store this data in the same table or rather have a tblCondSize and tblEncSize?

In my experience I have only encountered a discrete field for a dimension UOM when conversion from one system of measure to another was wanted. I suggested a Size field where you would enter the symbolic size that is used and familiar within your organisation or industry. In my mock up I made it a text data type so you could enter 2", 2 inches, 4x4, 16 sq inches, etc. - whatever is the common parlance. I suggest you don't try to formalise the dimension too much unless you have to. So store all the size classifications in one table.

Is there a way to populate tblItem with all the combinations of these attributes ...

Is this a one time, quick and dirty set up to be run at system implementation? Yes, this can be done through one or more append queries that you would probably throw away afterwards.

Is this an application feature you want for adding new items whereby you choose a size, a material and an item category and have the system generate the new item for you? Yes, this also can be done but should be developed as a more formal routine. I envisage it being done via combo box drop down lists and a small VBA procedure.

... although it makes sense to store the data in tblItem as the Id fields from the other tables, it might not be the best way to enter it. How would I create a form that would allow me to enter "3 inch EMT conduit" ...

I don't have the space here for a complete tutorial. This can be done and is very common. Basically this would be done through three combo boxes: one for size, one for material and one for item category. The value of a combo box is not necessarily what you see and select. For example, selecting 3 inch from the size combo box would set the value of that box to the autonumber PK (ID) for 3 inch.

However there are many other material types that I use, cable, switches etc. These material types have different attributes from each other ...

I don't want to be too glib with my answer here. If you can fit these other items into the three (now more generic) attributes of size, material and category, then do so. Otherwise it may be necessary to complicate the db design to provide the different and additional data. It is not uncommon to have 'extension tables' (my term) where for certain categories, and only certain categories, additional data is stored. This is an example of the legitimate use of 1:1 relationships. Alternatively the extra data is stored in the original table; modern database managers are very good at compressing unused space and therefore the db size hit is minimised; the rule is to keep all additional cable data together, all additional switch data together, etc.

I think you need to undertake a tedious task of listing all the attributes of cables, switches, etc. that uniquely identify the item and then see if you can formalise them. You speak of item class in the next quotation and it may be necessary to introduce this concept.

Right of hand I think I would need a tblItemClass, and then a table for each Item class that would define it's attributes, and then a table for each of the potential attributes. Am I on the right track with this line of thinking?

Quite possibly but beware of designing on the fly; do the analysis suggested above first.

Is there any type of limit to number of tables ...

No. (Well there is but you're not likely to reach it.)

I need to learn how to use Access and how to Design a database and my thinking is that by learning through working on my project I can do both at once, however this may not be the best course.

Believe me, it's the very best! I'll try to sort out some links for you - Orange is very good with these. My advice is that time spent at the start in designing what you want to do and designing your db is repaid ten times over during development. Resist the temptation to dive straight into Access. However by all means experiment with Access; prototype ideas but be prepared to throw them away. My machine is littered with prototypes for ideas and solutions, most of which yet have to see the light of day.

Adopt a naming convention. Look up Reddick on the Web. Avoid special characters in names; yes, I know it's possible to use spaces and so on but you'll soon get tired of typing those brackets; I don't even like underscores as after Access has added prefixes and suffices with underscores, the extended name is unwieldy and messy, use capitalisation to break up a name for the eye.

It's worth having a look at VBA and the concept of event driven object orien(ta)ted programming - just the object class/object instance concept for now. I like many other experienced Access practitioners do not use macros for many valid reasons, so use any time you have on VBA rather than macros.

Am I shooting myself in the foot ...

Why? Does it hurt?

[Rod]

Here are some links:

http://www.accessmvp.com/strive4peace/

A very good introduction to it all. I suggest you spend two or three days with this before reading anything else.

http://allenbrowne.com/tips.html

Allen Browne is the cornucopia of all things Access.

http://phlonx.com/resources/nf3/

Another tutorial for designing an entity relationship diagram (not to be confused with a physical db design). This site offers a PDF download of the tutorial.

http://blogs.office.com/b/microsoft-...07-limits.aspx

A summary of the size and number limits of Access. No need to remember this; I include it simply for interest.

[jammerculture]

so this is probably going to be a stupid question but I am having a hard time wrapping my brain around a few things. so i have these tables in which I define attributes of material I want to order, and I will at some point create a form in which these attributes can be chosen, and the combination of these choices determines what I am ordering but what happens next? do my choices get filled in a table? how is this structured.? Does each item create a record in a table, each order has a seperate table? I am sure I am thinking about this all wrong. I have this feeling that something is fundamentally missing or wrong about my perception here. What is the relationship between the tables I currently have, an order form that I will create and a physical order that I can process and get material?
ll

[Rod]

I am having a hard time wrapping my brain around a few things

Welcome to Access! There is a considerable learning curve for any beginner; more so for those who have trained themselves to think in an Excel fashion. Access may be positioned at the small end of the spectrum but it is a serious IT tool and not the 'Noddy' application that some would have us believe or the 'Paint by Numbers' application that Microsoft's sales pitch promises.

Answering your questions:

... each order has a seperate table?

No. There will be one order table; each row in the table is a separate order. The usual structure found here is that there is another subordinate (child) table for order lines; each order has one or more order lines.

What is the relationship between the tables I currently have, an order form that I will create and a physical order that I can process and get material?

May I first propose a simple model (= way of thinking) of the situation consisting of layers.

The bottom layer consists of your tables where all your data is stored. (The physical data storage on magnetic media is probably different but we'll let JET RDMS and the OS and the BIOS worry about that. As long as these systems can retrieve and present your data in the familiar tabular format then there is no harm in thinking about tables as the ultimate bottom level.) In a relational database, tables are related to each other. These relationships serve no purpose other than to allow the RDMS to check the integrity of any changes you make to your data. For example, with properly defined relationships, the RDMS will not allow you to enter an order line without the parent order being present or delete the order if there are order lines. Usually programs check all this beforehand since receiving an uncontrolled data integrity violation from the RDMS triggers a run-time error and halts execution of the program. There is however a large comprehension benefit in defining relationships: they help you understand how all your data fits together. Using the relationships you can 'talk through' how to perform certain tasks.

The design of your tables and relationships according to normalisation techniques may produce a database that is awkward to use for 'real life' purposes; people are quite happy with denormalised entities and use them frequently. These 'real life' denormalised entities may be considered as views of your data. This is where the second level comes in: you communicate with your tables via views which are implemented as queries. Access uses SQL as its query language and queries can become very, very complex. The point I would like to stress here in this brief overview is that tables may be joined in SQL queries but these joins are not the same as the database relationships. Yes, Access will suggest the db relationship as a join for SQL but you may ignore it or change it. It is important to differentiate between SQL joins and db relationships.

The third, and top, level is the user interface consisting of forms and reports (plus, these days, Office Automation and eBusiness). Think of a form as the window into your data that uses a view of your data provided by the query on which the form is based (we actually say, 'To which the form is bound"). Forms can be bound to a table without the intervening query and in some cases this is sufficient.

OK, there's too little room here to describe it all, that's what text books are for. Has it helped answer your question? The order form is the 'window to your data' where you manage orders. Any change to a bound value/field/attribute is automatically changed in the physical database - eventually - Access updates record by record. The physical order may be a report (sent by mail), or a fax, or an email (eBusiness - sort of), or a direct transmission to your supplier (eBusiness proper). All of these occur at the top level of my model. Any changes you make in your form (additions, deletions, modifications) will be 'communicated' via the bindings (underlying query) to the RDMS and stored in the physical db. Sometimes - well, more often than not actually - it is necessary to write some code (at the top level) to make this mechanism do exactly what you want as a standard generic system cannot do all things for all men at all times.

... do my choices get filled in a table? how is this structured?

I hope I've answered this is a roundabout way.

I shall extent my mock up with a typical order structure and republish it for you.

How's the foot?

[jammerculture]

lol, foots fine.

first off, I wanted to thank you for all the time you are putting in helping me. I am always amazed by people like yourself who volunteer time and effort on forums like these, at no gain to themselves. very informative reply, which I will read many times and digest. I think you really hit on what I was looking for with the one order table with subordinate tables for order lines section. I will reply further once I see wether or not your next posting shines more light.

[Rod]

Above is the extended db mock up.

I don't know enough about your requirements. If you back order against specific jobs then tblOrderLine and tblItemUse may be the same table.

[jammerculture]

ok so if i order ten items, then tblorderline will get ten records each with a different item id, but the same order id? then i order again, another ten items, this time another ten records with a deifferent order id, etc etc? wont this table fill up super fast, and does it matter?

[jammerculture]

also, I took your advice and listed out all the material that I could possibly order. a few things became clear. I am prety sure that I will be able to force my data to conform to the descriptive forms we have laid out, (size, material and type) I might just have to change the wording around. I also realized that there is a large group of items that don't fit into this form but are singular, ie I only order one type so there is no need to normalize the data. for example felt pens have no size, type or material to distinguish them from anyting else. how do I deal with these items that need nothing to differentiate them? do they just get a record in tblItem with null in size, material and type? will i need another field in tis table like itemdescription?