Simple ordering form

[Rod]

ok so if i order ten items, then tblorderline will get ten records each with a different item id, but the same order id?

Yes.

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, ...

Not necessarily. Only if you write an encyclopedia against each order line.

... and does it matter?

No.

Please note that the way it is structured each order is for one supplier and only one supplier.

[Rod]

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?

If you're happy that you can constrain all your current items into the three classifications without overdue compromise, then do so. Your approach with the nulls is one I would suggest except that I would introduce an arbitrary business rule that every item has to have an item category. So why not create an item category for felt tip pens? Size and material would certainly be null. Further you could, for processing reasons, include an indication on tblItemCategory as to whether size and/or material need be specified for each category. We're now introducing physical database design attributes that do not occur in the logical ERD - all perfectly legitimate.

If the current scheme means that item categories are too numerous - and I'm speaking about thousands - then it may be prudent to introduce your idea of item class before item category. The class for felt tip pens might be 'stationery.' I'm just thinking ahead to the user interface and how easy it will be to find the item you want.

[jammerculture]

the more i think about it the more I think i need to break the classification of material down into the smallest most manageable sizes creating a need for a class attribute. the reason is that my end user experience will be forms with drop down menus and nobody wants to scroll through one hundred items to order pens. They want to choose stationary from a group of ten choices, then pens from another group of six. I want to be creating a form with cascading options; certain job tasks will only use certain material, so that each choice limits the field of available choice. will need to create class system for job tasks too, for similar reasons and link categories of tasks to categories of items. This is at the heart of what I am trying to do, relate jobs to material. all the tables on each side are just descriptions of my two main objects. I am going to spend a little more time listing and breaking down the two sets of data, then I will attempt to create the basic table structure and post it. In the mean time, I will attempt to limit my curiosity in how the magic happens.

[Rod]

Class will be a good place to keep information about whether size and/or material is required.

[HMEpartsmanager]

Am I shooting myself in the foot trying to design something this complex in this fashion?

I think so. I don't see the need for the 'descriptors'. I think you will make it to complex and not all items you may need now, or in the future, will be able to fit all category descriptors you have. That is one opinion.

I tried to standardize my 'part descriptions' for easy ordering, like....

Conduit EMT 1"
Conduit EMT 2"
Conduit PVC 1"
Conduit PVC 2"
Wire #6
Wire #8
Wire 12/3
Wire 14/2

So when I am entering a purchase order, I enter have a listbox that displays all items available to order, sorted alphabetically. After I choose the item I want, the line item vendor's part number is automatically extracted, yet retain my internal part description.

I have about 15,000 items available in my database, and scrolling to whatever item I want is pretty easy. A trick I use is that all vendor files have a primary key that I use to my advantage. If I know 'gloves' are vendor Fastenal, and I know from years of experience with this database that it is vendor item #395; it equates to a vendor item id of FAS-395. By entering this instead of scrolling through my list of 15,000 parts, I get taken right to the gloves section. Or in this case, I was just trying to get to the "G" area in the database as I want to order some "Lamp 200W Incandescent", and that gets me in the vicinity instead of scrolling from the first part in the database.

The point is, evaluate if you really want to define 'multiple' categories for an entire database, where the categories may only be pertinent to 'some' items in the database. In my view, a field of 64 characters for a description, alphabetized as a query, and used to enter purchase orders; just seems like the right idea. It has been a few years since I wrote mine, but I can't think of how multiple category descriptors will help. Not only now that you are building the database, but how it 'might' pidgeon hole you in the future when adding other line items to your database.

Of course, all this is for not if I am misunderstanding how/why you need those descriptors.

Tim

ADDED AFTER READING PAGE 2; concerning 'nobody wants to scroll through 100 items to order pens....

I think you will be surprised at the speed. I am running on 5 year old computers (AMD 64X2 Dual Core 2.20 GHz with only 1gb of ram). All 4 computers on the network are the same. I can touch the database from my computer (a good couple of hundred feet away via cat5 cable) and I have no issues cruising (scrolling) through 15,000 line items to order each line item I want to order. I write a few purchase orders daily, do receiving on incoming, daily maintenance, etc... and I find the speed acceptable. So unless the computers in your network are real slow, I think you will be ok. Just another opinion. :-) And as mentioned above, once you get using the database daily, you start to remember key part numbers in your head if you do it all the time. I can scroll from the first item right away, enter FAS-395 to get me to the "G" area, or HSC and get to the "L" section in the alphabet, or HSC-002 and get to the "W" section of the list. 15,000 isn't really that bad, and having parts remembered to areas of the databse is also handy. But that is just me.

Tim

[jammerculture]

I appreciate the reply to my post especially as someone with real world experience using a system similar to what I am looking to create. The idea I am pursuing came about through trying to create solutions to a variety of problems that we experience day to day. I think our applications are a little different. The system I want to create is for use by the foremen on site in an electrical contracting company that specialises in high rise and commercial. Our job sites are big. We require a lot of material for a variety of applications spread out over a large area. Many application use some of the same material in different areas. Our current system of communicating our material needs with our office involves the use of spreadsheets where we manually type out 3" EMT connector, 3" EMT coupling etc. The spreadsheets are emailed to the office. What they do with it I am unsure. The redundancy in the data entry was the inspiration for the classification system in a database, an effort to save time by entering information once and then choosing it when needed. Once I got thinking about it, I realized that moving the material handling to a database could solve some other issues, such as the ability to standardize material, and track its use between jobs, ease of aggregating orders of the same material, a two-in-one process for designing and ordering, and if I also moved the time and payroll to the system, the ability to monitor time and experience of the various workers between jobs. All of these goals are stated desires of the ownership, only problem is the company is mostly made up of "old school" electricians, including ownership. Getting them to use spreadsheeting instead of faxing in paper orders was hard enough! My suspicions were confirmed when I brought my idea to my foreman. Basically if these guys can type 3" EMT connector faster then they can find it in a list, they aren't going to use the system, and all the extra benefits that the system had come to be about would be lost. For the system to be adopted properly it has to be very very simple to use. I am even going to go so far as design the forms to look like the old spreadsheets that the guys use, only instead of spaces for typing, there will be lists of a small number of choices. The only way I know how to do this is by breaking the information down into the smallest forms.

[Rod]

To HMEpartsmanager

The points you make are valid. The practice found in most organisations is to follow a system of standard descriptions with, maybe, a category or class indication implemented either with an attribute value, or a separate table if there is additional data to be recorded about the category/class. The auto expand feature of combo boxes helps find an item very quickly in lists of many thousands provided that the standard for descriptions is followed accurately and faithfully in all cases.

However humans are not infallible: we both know that non standard descriptions creep in from time to time, especially when more than one person or agent is responsible for maintaining that data. As humans we can cope with this by inspection: '8 in,' '8"' and '8 "' are all readily recognisable and are interpreted correctly. Not so, unfortunately with computers. If you had to write a query to retrieve all eight inch parts then you would need to cater for all possible variants of recording the size.

It seemed to me from the beginning that it was important to jammerculture to be able to access his data by families of items; hence the more cumbersome design. However this design does 'enforce' the standard naming convention. Moreover it does not rely on descriptions to retrieve families of items. With adroit use of queries the fact that the system has this categorised structure may be transparent to the user.

Of course, at the end of the day, it is jammerculture's decision. I'm not promoting one way or the other but following what has been put forward as requirements.

To jammerculture

For the system to be adopted properly it has to be very very simple to use.

Yes, this can be a design objective. Remember however that someone has to 'maintain' the system and this may not be so simple, whatever choice you make about categories.

I am even going to go so far as design the forms to look like the old spreadsheets that the guys use, only instead of spaces for typing, there will be lists of a small number of choices. The only way I know how to do this is by breaking the information down into the smallest forms.

This does seem to suggest a need to be able to accurately categorise your data. You don't want switches appearing in the cable section of the 'spreadsheet' and you also want a complete and exhaustive list of relevant cables, no omissions.

[jammerculture]

right and I don't want conduit sizing showing up when ordering cables. I also want to be prompted for the connectors that fit the chosen cable, use electrical code rules to automate the ordering of supports, this sort of thing. I'm pretty sure for this level of functionality I will need to fully define every item. I want the function be something like "New>cable(conduit/floor package/whatever), Length>400', # of straps, #of connectors, save to>job ID. Order,> jobID 1,3,14,9. I guess the title of this thread is a little misleading, as what I am really after is not just a simple ordering system, rather a computerized version of what I as a foreman need to do in my job, let the computer do some of the work lol. I started the thread to focus on just one aspect to get my feet wet. This is also why my first post was "is Access right for me" because I'm not even sure I can use it to do what I want. But I think it's a good start. As far as maintaining the system, well I don't wont to work on site forever...

[HMEpartsmanager]

I appreciate the reply to my post especially as someone with real world experience using a system similar to what I am looking to create.

You are welcome. Just giving another perspective to your well thought execution of what you want to do.

Once I got thinking about it, I realized that moving the material handling to a database could solve some other issues, such as the ability to standardize material, and track its use between jobs, ease of aggregating orders of the same material, a two-in-one process for designing and ordering, and if I also moved the time and payroll to the system, the ability to monitor time and experience of the various workers between jobs.

Yes, I do something similar. By having the ordering and tasks assigned at purchasing of what these units will be used for, and by who, and then having a time spent on each task; information reports can be drawn from this data. It really helps with project managment and scheduling of when something will be done. Especially when you are dealing with something that can take a year to build. So you will be able to use your data in the same way and see how long it takes to do different tasks/jobs.


A suggestion, is to have a couple of category/descriptors labled in some form of MISCELLANEOUS/SUPPLY or similar. Meaning, when you 'create' the SKU's in your system and categorize them, you might have fields with ITEM, MATERIAL, SIZE (Conduit, PVC, 2"), but if you are using this database as all-ecompassing for everything you purchase (PENS/STATIONARY), then you will need a catch all category for those type of things. You are 'probably' going to run into situations where you are trying to enter a new SKU, but the strict categories just don't line up. You are going to need a way to categorize those items in this type of scenario. An example is if you do have someone buying PENS, the item may be STATIONARY, and the material category could be MISC, and the size could be N/A.

I guess what I am getting at is that be aware when creating these categories, that some items do not have these type of attributes, so if you are using the database for all things, you need a way to enter these into the system.

Tim

[HMEpartsmanager]

However humans are not infallible: we both know that non standard descriptions creep in from time to time,

Oh yes. :-)

I still go through my SKU's to re-arrange data that did not make it in as the rest of the data. Such as the beginning when the database was made; (I have categories I assign my SKU's also, I just don't use it for selection basis), when I was using FIT as a first category, and PVC as a secondary category. I later decided to make the choices more distinguishable with FIT and P40 for schedule 40, and P80 for schedule 80. That leaves the PVC category as the catch all for the plastic fittings like Poly, UHMW, Plexiglass, etc...

So yes, data fitting into categories does need some massaging occasionally. I do like categories, but in my case, I just 'chose' one direction of having all parts loaded in the query to choose what to order. This could be that I do 'all' the ordering and data entry, so for me, it would be ok. For many others to use this database, I too might have chosen the direction that the OP is going. :-)

That old saying, Keep it simple.

Tim

[burrina]

Here is a good place to structure your db. It will give you a plan.Then you can begin ti build your foundation and this IS the most important part of ANY database.
http://www.databaseanswers.org/data_models/

[jammerculture]

HME- Trying to fit everything into specific categories can be tough, but crucial to my desired end user experience. I have been trying to think of terms that are more generic then "size" for example to define a number code that is used to describe something, but haven't been able to come up with something. I think I will be ditching the "material" for "type" as it works for more applications. Example #12 Red Wire, 3" EMT conduit, 4x4 wrap box, 1/2-3/4 Slab box, are common terms for items we order, this is what we tell the apprentices to get off the shelf. They all start with a number, that isn't necessarily a size. The second term is sometimes material, but always "type" and then the last term is what it is. A felt pen is a chisel-tip sharpie pen, still the same three descriptors, but this time no number. It's kind of maddening lol. I don't want to go with "descriptor 1, descriptor 2, descriptor 3.

[burrina]

Just a couple of thought's, document everything in your database. Also give some thought to sub-categories and try as best as you can to keep it simple. EMT is EMT no matter how you slice it even if the size changes after 2" if memory serves. I am a retired Master Electrician and wrote a db for a electrical company. But I have slept since then so ? Inventory can be as simple or as complicated as you want it to be. What are your REAL needs?

One approach would be to associate all items with a letter, i.e. L for lamp,Lead Electronics, etc... Write Descriptions in your tables, for your queries,tables, etc.. as well as your code.

DoCmd.Open Report ' Opens Report Some Report for example. So you can remember later on.

Let me know if I can be of help.