When to lookup values

[Cowlers]

Hi all,

I am trying to design my database according to the rules I see in these forums as I'm hoping that by doing so I can avoid the pitfalls that you far more experienced developers have already discovered!

My question is when is the best time to have a "lookup" function for the user to select from a list?

Prior to looking at these forums I was using the lookup wizard within the table and found this to work fairly well because it would populate my table with ID numbers but would display the "Description" field from the linked table meaning that the tables were fairly easy to follow.

If I do not use the wizard then my tables become populated with ID numbers from all around my database which is not a problem from a "functionality" point of view but doesn't make the tables very easy to understand because you end up with column after column of integers.

The other benefit of using the lookup wizard was that when converting a table to a form a lookup wizard column would automatically generate a combobox populated with the values from the source table and would negate the need for me to play with it at all! If I follow the advice of the forum users and do not use the lookup wizard I simply get a textbox. Also because the source table is in a subform and is displayed as a datasheet there is not list against any of the fields in the subform either.

Forum user WaiveIt very kindly helped me to come up with the following format for my tables and I can see that it works in terms of the relationships between each field and how they link together.

The format of my tables is as follows:

Extrusions "Individual components making up an assembly - indicates the assembly they are part of and the 'Type' of component they are"
PK - ID - AutoNumber
FK - Ass_Profile_ID - Number
FK - Ext_Type_ID - Number
ProfileCode - Text

ExtrusionType "List of the 'Types' of component available"
PK - ID - AutoNumber
Description - Text

AssembledProfiles "List of assemblies available"
PK - ID - AutoNumber
ProfileCode - Text

Please could you advise if I am approaching this in the correct manner and what I should do to counteract the issues I've mentioned above?

My intention is that I can store the assembly information via a form the user fills in to select the various components and the part of the assembly they are. I also need to use this information to allow me to "convert" stock from components to assemblies so that stock is downdated as an assembly is manufactured and updated with the completed assembly.

Many thanks,

Dan

 

[CJ_London]

First off - as I am sure you are aware, using lookups in table design is a bad idea - but at least you seem to be aware of the problems.

Also, my preference is to avoid underlines (and spaces or non standard characters) in table and field names, just use a lower/uppercase structure such a camel - i.e. myTable, AssProfileID.

With regards your table design, it looks like it is missing some stuff to me
I appreciate these are extrusions but some thoughts.

Your AssembledProfile table is not right and you need another table- I think it should be

AssembledProfiles "List of assemblies available"
PK - ID - AutoNumber
APCode - Text

AssemblyContains "what the assembly is made up from"
PK - ID - Autonumber
FK - APID - long - link to Assembled Profile ID
FK - ExtID - long - link to Extrusions ID

The new table enables you to construct your assemblies

Quantity - just confirm length or volume does not need to be specified - thinking volume so you can calculate amount of material to make extrusion and length so you can calculate wastage

Cost - so you can calculate costs

Price - so you can calculate prices

Also, do you have sub assemblies which are used in more than one assembly or kept in stock? If so, you may need to reconsider your design to incorporate this

 

[Cowlers]

Thank you very much for your reply CJ_London,

I understand, by using the these two tables does this mean you can avoid the repetition in the Extrusion Table meaning you don't run into issue when updating fields? I did notice when populating with some sample data that the same extrusion had to appear several times for the different assemblies so this would make sense... (Forgive my ignorance, I'm very new to this so just want to make sure I've grasped it correctly!)

As for the other fields you suggest you are correct that I need to specify a qty as in some assemblies there are multiples of one of the components. I will add this into the table as this prevents me having to put the same item in multiple times to achieve the same thing.

Cost becomes a little bit complex because we are dealing with aluminium extrusions. the cost of aluminium varies greatly so I want to be able to have an aluminium figure that I keep up to date and then work out the cost of my profiles based upon their weight because otherwise it will be a full-time job to keep the costs of each profile up-to-date. I do need to be able to take a "snapshot" of this cost for when I am raising orders with the extruder because I need to ensure that I quote the correct cost but am also able to refer back to it when I look back at the orders we have placed.

I'm not concerned about prices at this moment because we work this as a multiple of the cost and it is customer specific so I can apply this in my separate customer table.

Just one question, you have specified that APID and ExtID should be "Long" as opposed to "Number" is there a reason for this? You've also said "Linked to ..." do you mean to create a relationship between the two manually within Access or should this link be formed in some other way?

Many thanks for your time, you've no idea what a help this forum is to someone who is as clueless as me!!

Best regards,

Dan

 

[Cowlers]

Sorry I didn't answer your final question, we do not store sub assemblies so at least that's one way that my db is simpler than it could be!! ;-)

Best Regards,

Dan

 

[CJ_London]

does this mean you can avoid the repetition in the Extrusion Table meaning you don't run into issue when updating fields?

Yes

I will add this into the table as this prevents me having to put the same item in multiple times to achieve the same thing.

From what you describe, add it to the AssemblyContain table

Cost becomes a little bit complex because we are dealing with aluminium extrusions. the cost of aluminium varies greatly so I want to be able to have an aluminium figure that I keep up to date

In that case I would have a separate table for costs

tblCosts
CostID autonumber PK
Cost - currency
CostFrom - date - date cost applies from

and add a weight figure in your extrusions table

In this way you keep a record of your costs. When you run your queries you can then set a filter on the date for the date of the quotation, todays date or whatever.

If there a different types of aluminium, with different costs, you'll need to add another table for aluminium types and add ATID as a foreign key into the above table

APID and ExtID should be "Long" as opposed to "Number"

Number can be one of a number of types - integer, long, double etc - it needs to be a long type - in table design, look at the properties at the bottom

Linked to ..." do you mean to create a relationship between the two manually

Yes, in the relationships table to 'document' it - then when you create your queries with those tables the link will be there automatically - however within a query and depending on what you want to do you may modify or even remove the link. Read up on join types - you have inner, outer, left and right.

 

[Cowlers]

This is brilliant, thank you so much CJ_London,

I see, so the cost can always be calculated for a given point in time because you always know what the cost was at that point. Is this so you can avoid saving calculated values within the tables as I see that is a big no-no too?

I'll take a look at Join types as I think this may be causing me some confusion as well.

In terms of the forms etc. that I will use for the user to enter their information are these best formed from Queries or from Tables or can the two be interchangeable? My guess is that because the table contains the columns of figures and not the actual "Names" of the choices (due to the fact these link to other tables) that the Forms would be better based on queries that I can use to return the names, is this correct or would you use the rowsource of a combobox to show the choices and then bind the column to the ID so that the correct value is returned to the table?

Many thanks,

Dan

 

[CJ_London]

Is this so you can avoid saving calculated values within the tables as I see that is a big no-no too?

Yup!

In terms of the forms etc. that I will use for the user to enter their information are these best formed from Queries or from Tables or can the two be interchangeable?

they are interchangeable within reason but personally I always use queries in the rowsource (i.e. I don't write a query then use that in the rowsource, I use the query builder in the rowsource), I have the philosopy of only giving the user what they need to cut down on 'traffic' so only providing the fields required and the records required.

In addition if you have a query which refers to a control on the form, you can refer to it directly this way rather than going through the forms collection. For example, a separate query might be like this

SELECT * FROM myTable WHERE ID=Forms!myForm!ID

Whereas if the SQL i in the recordsource you would use

SELECT * FROM myTable WHERE ID=[ID]

Note:It is at this point you will start to experience problems using the lookup in table design - lets say you have a tblCustomer with two fields, ID and CustomerName. You've used this table as a lookup for a field called CustID in another table. So what do you see? A field called CustID but showing a customer name - so you change the fieldname to 'customer name' because that is what you are seeing - and because when you create a form based on this table, you want the associated label to say 'customer name' and not CustID - and just for good measure, you have probably introduced a space as well (FYI you should use the caption option in table design to display 'Customer Name' but this will still be confusing.

Next in another query you want to link to this field - because you called it customer name (and even if you haven't, what you see is text) so you try to link on the name, and not surprisingly, it doesn't work, but it is not text, it is a number.

Another problem is you create your form and great - you have a combobox created automatically (saved you all of 10 seconds), but you'll find half the time you don't want a combo box because you don't want the user to be able to change the customer. So what do you do, you change it to a text box (you can lock the combo box, but why show the down arrow? doesn't look professional and is misleading for the user). But now you are seeing a number and you actually want to see the customer name - so what do you do? Now if only you had learned how to do it without these shortcuts, it would be obvious.

 

[Cowlers]

I understand exactly what you mean, this was my experience in a couple of the initial tables and forms I played around with previously so I will definitely take the approach of using the SQL statement and building the comboboxes on the form myself. I like the fact that the Query isn't just sat in your database for a specific form and instead is built on-the-fly for the form it is used in, much more intuitive and easier to manage!

There are two processes involved in this database, one is surface coating and I think this is quite simple from a stock tracking point of view because x no. Extrusions or Assembled profiles go in and the same number come out just of a different colour so I can create and IN and and OUT record in my stock transactions table to do this (unless you think there is an easier method?!). The other process is the assembly process where the various profiles that make up the assembly are combined. The surface coating can either be applied to the extrusion prior to the assembly process or the assembly process can take place prior to the surface coating. The assembly process is made a bit more difficult by this because the user has the choice of assembling from pre-coated components or from non-coated components so the stock side of things is a bit more tricky. Do you have any suggestions for handling this please?

Many thanks,

Dan

 

[CJ_London]

re precoating - this is effectively a sub assembly with presumably a choice of finishes - had a similar situation for a Tshirt print company when they added the print - or engraved a trophy.

Before I can suggest a solution, I need to know how you treat your labour cost:

Is it ignored completely? Or is it built into your cost?

If the latter how do you balance out setup/setdown costs? i.e. the time (and waste material) it takes to setup the die and be ready for production and again to take the machine down again.

 

[Cowlers]

Fortunately the coating is done by a subcontractor and we simply pay a value based on a calculation with the perimeter (I plan to have a column for this against the individual extrusions and against the assembled profiles so I can work out the cost when assemblies or components are coated - the perimeter will be different) and the length of the profile so this can be calculated fairly simply as we do not need to worry about labour. The choice of finishes is quite large, there are around 300 standard colours broken down into several groups and there will be a different cost for each group of colours from our subcontractor. I plan to treat these in a similar way to the cost for the extrusion where I will have a table that shows a "DateFrom" cost so that if the cost changes over time I can track it back in the records of previous orders. The concern is more about stock qty as opposed to stock valuation at this point,

Regards,

Dan

 

[CJ_London]

In that case I'd be inclined to:

• add your coatings to the extrusions table (perhaps rename the table to be more 'inclusive') and add another field for 'type' to mean extrusion or coating (note type is a reserved word so don't use that actual word) - you may need a number of different coating types to indicate which price
• add another field to your price table to indicate type as above
• add another field to your assemblycontains table to link to the coating record in the extrusions table - used when individual components are coated and left blank otherwise
• add another field to your assembled profiles table to link to the coating record in the extrusions table - used when the entire assembly is coated and otherwise left blank

 

[Cowlers]

Thank you once again CJ_London,

I understand how this will allow me to specify when each component is painted, the only thing I'm a bit unsure about is how this allows me to keep stock of the various parts.

As I see it I need to have a set of data which allows me to specify how each assembly is made and what it is made of which the answers you have given me above show very well but these are a product range, i.e. we will order these assemblies repeatedly and therefore the components making up those assemblies on an ongoing basis. These will be the same components making up the same assemblies over and over again.

I am a little unsure how I will be able to both store the information about how the components make up the assemblies and also be able to say "We have x stock of this component in this warehouse" without separating out the coating details from the component and assembly library because once a component is coated that record is fundamentally altered if the field is part of the component information? Is that right or am I reading this the wrong way?

I think it is this "double-use" concept that I am a bit confused by. It looks to me like the same tables are used to store the library of parts - which would need to act like a template to explain how each assembly is made up - and also the records of the orders and stock. Or am I just tying myself in knots??

Many thanks,

Dan

 

[CJ_London]

In that case you need another table for stock

Since you don't stock subassemblies it would look something like:

tblStock
StockID autonumber PK
ExtrusionID long FK
CoatingiD - long FK
TranType text - stock in/stock out/returns etc
TranQuant long/Double - depending on whether you can have decimal quantities i.e. 5.4kg positive for stock reciepts, negative for stock issues
TranRef - text - might by your PO number, customer order number, conversion
TranDate - date - date of transaction

To determine current stock for a extrusion, simply sum the TranQuant column by extrusionID.

If you coat an extrusion and put back into stock you would create two of the above records, one to remove it from the uncoated extrusion and one to add it to the coated extrusion.

 

[Cowlers]

That makes sense, thank you CJ, will do some tinkering and come back if I need any more assistance!

Cheers for all your help!

regards,

Dan

 

[Cowlers]

Sorry, one more question which loops back to the original thread heading.

When creating Forms to allow me to enter this data there will be subforms, a good example would be the customer order. There is a Customer Order Header which contains the general order information such as the customer, date of order, order status etc. and this is entered using comboboxes which as discussed previously I have created myself using the rowsource queries etc. I'm more than comfortable with this part now.

The subform of this will be the Order Detail which contains the actual lines of the order such as the ProfileID, LengthID, Qty, etc. etc.. I still want my users to be restricted so that they can only enter data from the lists I've created but I because the subform tends to be formed from the table I do not get the drop-down options. Due to the way my tables are structured the datasheet needs to be filled in with the IDs of various fields but to the user these are going to mean nothing, what is the best way in this situation for me to structure this so that I avoid the pitfalls we have already discussed and the users can still select values from a list?

Many thanks,

Dan

 

[CJ_London]

right click on a control that you want to be a lookup and select Change - Combo.

Then populate the rowsource with your lookup which will be something like

Select ID, Description FROM myTable ORDER BY Description

Set the bound column to 1 (which is the ID), column count to 2 and column widths to 0;3cm

 

[Cowlers]

Makes perfect sense, thank you once again!

I'm definitely going to owe you a big drink after this!!

Best regards,

Dan

 

[Cowlers]

I am just considering the StockTbl model you proposed and the ExtrusionID field on the stock transactions. Because I can have stock of assemblies or of the individual extrusions do I need a further field i.e. "PartType" where I can specify if the record refers to a component or an assembly so that the IDs do not get confused being that they come from two different tables?

Many thanks,

Dan

 

[CJ_London]

In that case I would drop the AssembledProfiles table and add those records to the extrusions table

then modify the AssemblyContains table for APID to link back to the Extrusions table
PK - ID - Autonumber
FK - APID - long - link to Extrusions ID "for assembled name"
FK - ExtID - long - link to Extrusions ID "for assembled component"

To determine if an 'extrusion' is an assembly you just need to see if there are any records in the AssemblyContains table APID field match the ExtrusionID in Extrusions table

 

[Cowlers]

So now I would have 2 tables, one with the list of Extrusions and Assembly Names in it and then the AssemblyContains table which has the two fields in it, APID and ExtID. APID would link to the name of the assembly in the Extrusions table and ExtID would point to the component part in the Extrusions table and if I want to indicate that a component is an assembly then I would use the same ID in both fields?

Is that correct?

Thanks,

Dan

Edit: actually I see what you mean in your post, I need to check if the ID in the Extrusion table is in the ExtID field of the AssemblyContains table to identify if a particular profile is an assembly or a single extrusion.. thanks!