DWUTKA at marlow.com
DWUTKA at marlow.com
Mon Jul 31 10:48:50 CDT 2006
Hopefully some of the other experts can chime in here. I tend to write code centric interfaces. For example, in my products database, I have .dll's that retrieve and organize the data. The actual interface are .asp pages. In access, I would have the same classes that I have in the .dll, but everything would be unbound. Not sure if this is how you want to go... Drew -----Original Message----- From: Richard Lavsa [mailto:rlavsa at tigg.com] Sent: Monday, July 31, 2006 9:31 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Quotation Database structure question Drew thanks for the screenshot.. I had the following structure in mind when I saw yours structure it reassured me that this might be the way to go. I have something similar to what you have, or at least what I believe to be a similar method to handle this issue. Basically I have tables (Components, ComponentTypes, ComponentTypeTemplate, ComponentValues) ComponentType (Define a new type ex.(FAN, PUMP, Blower, etc) -------------- CompTypeId Type TypeDesc ComponentTypeTemplate (Build specs for each type) --------------------- ComponentTypeTempId CompTypeId TypeSpecification ComponentValues (store a value for each Type and Spec) --------------- ComponentID ComponentTypeTempID CompTypeID (don't need this but did it for quicker queries) SpecValue Components (Stores the basic info for the new component which includes, Type, Price, QuoteDate came in from Vendor, and vendor) ---------- ComponentID CompTypeId ComponentDesc VendorPartNo UnitPrice VendorID DateOfPrice Relationships.. Components 1 to Many ComponentValues (field: ComponentID) ComponentType 1 to Many Components (field: CompTypeID) ComponentType 1 to Many ComponentTypeTemplate (field: compTypeID) ComponentType 1 to Many ComponentValues (field: CompTypeID) ComponentTypeTemplate 1 to Many ComponentValues (field: compTypeTempID) Ok, so now all the data entry forms are built, and this all appears to be working correctly. Now I'm down to building the Estimate itself. So I have Estimate_Header with your basic header information, and then the Estimate_Details. The Details will have Components, the quantity, Price,..... So my new delima is how do I display this data driven metadata type system so that I can show the TypeSpec field from the ComponentTypeTemplate as RowHeaders in a ComponentSelection form for the Estimate Details section. Feasable I know how to do this in a Crosstab query, however I cannot display a raw query in a form (or can I and I just don't know how). I tried the PivotTable view in a form, but it adds totals for every level you enter in the row Data portion of the grid.. Any tips/tricks would be greatly appreciated on how to display this data that is in a table, and show it as row header data. Currently I have a listbox, that works pretty good, however there is no sorting or column resizing capability within it or else it would be perfect for this situation as it dynamically rebuilds when a new Type is selected. Thanks in advance.. Rich -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com Sent: Monday, July 24, 2006 12:54 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Quotation Database structure question I created a product database for our company. Our website uses it. We have several 'types' of products, and I didn't want things hard coded, so I created a database where the people that control the data can create new products and put it in the system. Here is a screen shot of the relationships: http://www.marlow.com/productdbrelations.bmp there are a few tables that are off on the side, but those are strictly for the shopping cart itself. A little explanation: tblProducts is the main table, the ProductID is an Autonumber in that table. It has fields that are specific to every product, no matter what it is. The key field (in importance) is the GenericType field. I goofed when designing this, because I didn't use that term throughout the whole project, and didn't catch that I wasn't doing that until I was pretty deep into it. What does the GenericType field do? It tells the system what kind of product it is. GenericTypes are defined in tblSpecificationHeader (with a name, the GenericType field is a number field that matches the ListID (AutoNumber)). The specs for that product are then listed in tblSpecificationDetails. Each spec has the ListID (to relate it to a 'generic type'). Then have a name, units, (Our website has a glossary in it, so the Glossary ID matches the glossary terms to appropriate specs (for instance, some of our products are TECs (Thermo Electric Coolers, and DeltaT is a spec, and a glossary term, so when the website displays a product, if it has a matching glossary term for the spec, it shows up as a tool tip on the website. Then there are product types, and sub types. In our particular case, our subtypes are categories based on our customer base (DSP (Defense Space and Photonics), Commercial, Medical, etc.), and the types are more specific product types. For example, our Generic Types are 'standard coolers', 'accessories', and 'TTRS'. Our product types are identical for accessories and ttrs, but standard coolers have many product types (MI coolers, AT coolers, DT coolers, XLT coolers, etc.) I'll try to explain this in a little less technical terms. Each product has a 'generic type'. For instance, a centrifugal pump and a positive displacement pump are both pumps. So they would both have a GenericType 'Pump' (which would have it's own ID (ListID in my database)). The specs for a pump might be GPM, Type, PowerUsage, etc. Each spec for a pump would be listed in the SpecDetails table. Ooops, almost forgot. There are a few tables missing from the relationships. tblDateData, tblLongData, tblDoubleData, tblTextData and tblCurrencyData. They are identical tables, with SpecID (Long Integer), ProductID (Long Integer) and Data (data type matching the table, ie, Long Integer for tblLongData. Now, you might want to make a few modifications to my design. For example, my Product Specs are hard linked to a generic type. You may want to put this link in a many to many table, ie, a ListID and SpecID table, so that the same spec may relate to multiple generic types. (So if you have Manufacturer in many generic types, you can list all products, regardless of generic type, for a specific manufacturer. That may apply to all products though (so it may be put in tblProducts), but something like PowerUsage may only apply to certain generic types, so that might be a better example). Hope this helps. Drew -----Original Message----- From: Richard Lavsa [mailto:rlavsa at tigg.com] Sent: Monday, July 24, 2006 7:44 AM To: accessd at databaseadvisors.com Subject: [AccessD] Quotation Database structure question Hello all, I have a question on how to build a piece of my application. The piece I am having trouble trying to put together is as follows. Our engineers call out and get prices on material (aluminum, steel, Stainless), as well as components (Fans, Blowers, Pumps, Pipe, Angle Iron, etc.. ) So what they ask me to put together is a database that they can enter all this information into, and pull out information as needed. It sounded like an easy task at first. I started to lay it out and realized that even though these are all "components" to whatever it is they are building, they may have different characteristics to maintain in the database.. This is my biggest hang-up... how do I make a Component table be able to take into account all the various characteristics for such different pieces. For instance... FAN =3D (CFM, HP, RPM, Make, Type, Model, Cost, Vendor) Pumps =3D = (Style, Make, Model, GPM, Head, Type, Size, HP, Phase, Motor, Cost, Vendor) Starters =3D (HP, NEMA Rating, Voltage, Cost, Vendor) Then you have your Pipe and Pipe Fittings Pipe can be made from various materials, lengths, and thicknesses Pipe fittings can as well not to mention there are elbows, T's, Y's, etc etc... ... So in summary.. What I'm trying to build at this point is a way to record what the engineers received from various vendors and date it. Organize it in such a fashion they can pick and choose based on previous information gathered or based on updated data entered into the system and put together a quick quote for a customer. This will end up as a collaboration tool as well since some of the engineers may call the same vendor asking for the same if not similar items. I simply don't know what the best approach is in organizing all these Parts. They start off as a part, but they all have such different characteristics to record, I don't know where to begin. Any help would be greatly appreciated. TIA, Rich -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com