Solved Need expert advice regarding the table structure of Subtypes and supertypes (1 Viewer)

[wikihow]

Hello,

This is my second post here and I need help in regard of arranging my table structures. I have basic knowledge to Access and learning more at the moment.
I have a basic table (Super-types) of products in which we have common attributes and various additional attributes need to be divided into (Sub types).

I saw one video where this same problem was illustrated with a different data. in this video, two types of customer types were divided in supertypes for additional attributes.( attaching image)

I have created one table strucutre and relationship. (Attaching screenshot of my table structure)
I want to know how to auto enter the PartITemID in subtype tables, when i am entering a record for any specific type of category in supertype table.
For example, if I enter a record for doors category in supertype table, I need to enter related details in subtype table (Doors) with manually typing partitemID_FK to relate it with main table. How can i make it automatic so that I can enter a record in subtypes depending on whatever product category i choose and typing the partitemID_FK automatically somehow. (I have around 10-11 subtype Items, so there may be 10+ sub type tables needed). Any tip to get it working better or in a different way would be appreciated

Also if you have some examples of such situations, i would request the demo files. So i can view and learn from them.

Thank you in Advance.

 

[Pat Hartman]

Take a step back and ask yourself - Does Amazon have a separate table for each type of product they sell? Let me help you out. The answer is no. Does Sikorsky have a different table for each item that goes into a helicopter? No.

If you proceed down this path, you will bury yourself in tables and never be able to dig out. Analyze your characteristics. You should find a couple of patterns and you could consider creating tables organized that way.

You also don't want to end up with multiple Customer tables. There should be very few differences in attributes and you can also keep a type field so if it is important, you can tell a Wholesale customer from a Retail customer. You can use LastName to hold either Last Name or Company name depending on the type. Although, I don't know why Retail customers can't be companies which is all the more reason to use a single table and use the LastName field to hold the most important part of a name.

 

[mike60smart]

Hi Can you upload a zipped copy of the db?

 

[wikihow]

Hello Pat and Mike,
Thanks for the reply. The detail is quite complex as there are more details to be added to each category later on. I am not very good in Access and learning at the moment and trying to achieve some results from my work.

The customer screenshot is just an example from a video i watched. I am attaching the copy of sample database, what i am working on. It is basically a system to prepare multiple quotations for client and save them in system. After approval, there may be more documentation to be worked out.

I am trying to imitate a working online database, which is quite advanced. The item Doors & Internals etc you are seeing, they are the category and further each type have many type of sub items and the attributes or specs/ or details for these products will be different. its hard to make one big table for all items as in this case all unrelated fields of any item will have to be ignored and left blank.

Each category of items will have their own set of properties, stats. Like doors will have length, width, door count in a unit, price will be calculated length wise, their unique freight structure.

On the other hand a glass item will have length width and thickness, price in sqmt unlike doors. if i keep these two in same table, many fields will not relate to both and those uncommon fields I am trying to setup in different tables with linked to main product ID.

I am yet trying to figure out, what is best for my database.

 

[Pat Hartman]

Do you thing the databases that manage the configurations of airplanes are advanced? It looks like your schema is based on paper forms designed specifically for one task. Stand back and think about this in a more generic way. I don't know your specific business but I've worked in many different manufacturing environments that manage hundreds of thousands of parts.

Your schema should be simpler and more generic. You should not be creating separate tables for different types of components.

 

[mike60smart]

Hi

Your database currently has quite a few issues.

You should not use Lookup Lists in table fields.

You should definitely not use Lookup's to obtain the related Foreign Key.

You should remove all spaces from Field Names

Can you explain in simple English a typical Clients Project so that we can understand the Data Input Requirements.

For example a Customer on a specific date will Order a number of Items.

 

[wikihow]

Thank you Pat and Mike for your suggestions. I will look into it again as you have advised and will try to get back.

 

[wikihow]

I have removed the few extra tables. Removed spaces from the table fields and removed lookup queries from tables. Can I keep typed list of look up items instead? will that be fine?

 

[mike60smart]

Hi

If you need to refer to a list of items then this list would always be placed in a separate table.

Then on your Form for Data Input you would create a Combobox to lookup the list which then allows you to select an item from the List.

Access would then store the PrimaryKey for the Item selected rather than the actual name of the Item.

 

[Pat Hartman]

There is a difference between using Lookup fields on tables and using combos on forms. The former is bad, the latter is good. For lists that never change, you can put the options in the combo itself (I still use a table but that's because I have a mini-app to manage lists so I don't have to create dozens of forms and tables). If the lists could change, then it is better to keep them in a table. When I use combos on forms, I do not allow additions to the list on the fly. I find that allowing users to do this just lets them add typos so I use a more formal procedure that only "admin" users can update.

I attached my code tables maintenance mini-app. If you have more than a couple of lists to maintain, you will find it helpful. The key is that these are simple lists. If there are other data items, the list would need it's own table. An example would be Customers. We have lots of different attributes for Customers in addition to their name and ID so Customers would be its own separate table with its own maintenance form and your combo would use tblCustomer or query if you need selection criteria as the RowSource in your combo.

 

[wikihow]

Hello Part/Mike
Hope you are doing good.
I need some advice in adding a pricing structure for the items in database.

Scenario is we have say approx 10 basic product category. For example Product 1, Product 2, .... up to Product 10. Then we have 7 variant of each Product and that makes it. Product 1A, Product 1B, Product 1C and so on. and this goes same with other 9 products. Lets say total is 10 x 7 = 70 total variants.

Now we need to price each Variant. Each variant will have its own pricing slab. For example Product 1A ( if it is <500mm then $100, if <1000mm then $150, if <1500mm then $200 .. can go up to 5000mm.

Each Variant have its own pricing slab. As above example had <500mm, <1000mm, <1500mm, <2000mm, Other may have <700mm, <1200mm, <1700mm.

Please note that these are pricing slabs, product may not have round off size. it can be 586mm, 2156mm and like so. if a product is 586mm then it will be charged as <1000mm slab, if a product is 2156 mm then it will be charged as the next slab ahead of 2156mm depending on the sizes on price slab or price milestone.

So it is like its own pricing chart for one variant and we have 70.

I have attached an excel screenshot. We used a big chart and each cell have a big formula with conditions. That works, but still Excel is slow since there is much to calculate.

But now to make this work in Access, I wanted to know what can be the best strategy. Would it be good if we do it with VBA? Can VBA be used to write big codes. if yes, can you provide me a small code, so that i can use it to replicate for all my items.

OR I think, i can do it with macros with If code but I was told that VBA is faster than Macros. In macros, i would thinking to do it by if condition, where i would target a field to get its Part/variant ID and size field and then based on to those value and conditions I can give results in final field.
Or I create non embedded macro first and use access to convert them in VBA?

Or may be you can suggest a better method for this scenario. We have the item selection and can manually enter size (height x width) and then depending on the width price needs to be auto filled or calculated.

Please suggest as this may need professional experience rather than me doing excel like stuff.

Thanks in Advance

 

[mike60smart]

Hi

I would recommend a Products table that lists All of the actual Products together with sizes and the current Price.

I would imagine that the majority of Products would fall into the standard size bracket.

If the required Product does not fit the standard Size then you can enter the Price for that specific Product as needed.

 

[Minty]

Put your slab bandings sizes into a table. You can then include that in your pricing lookups.

You need to stop thinking of your data horizontally and think vertically.

 

[wikihow]

Hi

I would recommend a Products table that lists All of the actual Products together with sizes and the current Price.

I would imagine that the majority of Products would fall into the standard size bracket.

If the required Product does not fit the standard Size then you can enter the Price for that specific Product as needed.

Oh there is no Standard size or so. Size of an item can be from 500mm to 5000mm. I need the price to appear or calculate automatically depending upon whatever size we fill in manually. I already have a product table all i need is how i setup pricing according to the slab.
I am actually unable to make up mind on how to setup the structure of pricing table which is based on conditions depending upon size.

IF you see a product Mirror Door in list. there are approx 9-10 pricing slab for this one item. price differs with each 500mm gap i.e. 500mm, 1000mm, 1500mm, 2000mm ... the real product size is not specifically rounded off as per the slab. Real size can be any random size from 500 to 5000mm.

Are you suggesting something like below? Slabwise price table. Still need to understand how to setup conditions how it will fetch price from particular field for given size.

Any example would be appreciated.

Thank you.

 

[Minty]

That is sort of what I was suggesting.
But go vertical. You are still thinking spreadsheet.

If you need to add another slab or another measurement value you have to add a new field to your table.
By using a properly designed table even if it needs a lot of rows you have a simple solution.

Price_ID, PartItemID_FK, FromSize_mm, UptoSize_mm , Cost

Then you simply look up the price where the slab size is >= FromSize_mm and < UptoSize_mm

You can always produce a spreadsheet-like output using a crosstab query of you want to publish a grid-style price list.

 

[Cronk]

@Minty, I agree with mike60smart

I see Product as an entity with as many properties as required to be attributed to it including type, size, shape and price.

A hardware store sells amongst others products, screws. One screw product will be say countersunk, a certain length, gauge, type of metal, price and will have an SKU code. The only reason I see for a separate price table is for keeping a record of historical prices for a particular product.

 

[wikihow]

Hello Guys,
I need some help in one form.

This Add item parts form is linked to above subform. I need to enter data in Add item parts by selecting items. There are 3 cbo for Itemmain, Itemsub and item. This is basically to categorize the items.

If I individually choose record source as a select query for each cbo, Everything works fine. 2nd and 3rd Cbo gives me a long list even if they are not related to first Cbo.

If i filter the second cbo with the select query criteria with Main Cbo then it does not work properly. It does filter the list in second cbo but it applies same filter for all 3 rows, while i need different list as each item in main cbo is different and sub combo should result in related filter list.

Can anyone suggest any solution here? I am attaching the copy of DB. It is frm03 > 2nd tab (add item parts).

 

[mike60smart]

Hi

I would recommend that you use Cascading Combobox's for this process

 

[Minty]

@mike60smart I believe the OP is trying to use cascading combo's, the problem is they don't work well on continuous forms unless you set them up really craftily.

 

[mike60smart]

Hi Minty

Yes but I don't believe he has set up his tables to deal with the correct cascading