Pick out correct blank from a list of blanks 0D , ID & Lenght (1 Viewer)

[J_oggy07]

I have a pricing tool I use in excel, works well but would be better in access.

I have set some tables up and one is Blank sizes, OD , ID and length.

My components are all round rings

On my form I wish to query this blank list by putting my part sizes in OD, ID and length, then search my blank table to bring by best blank and costs etc.

I thought Dlookup might do it, but this could return a blank which is not the ideal blank, i.e much too big on OD.

 

[The_Doc_Man]

We have a potential ambiguity and before we go off the deep end I would like to clarify it.

In manufacturing, there is such a thing as a blank that is a piece of material pre-shaping or pre-stamping or some other alteration process. Is this what you are discussing?

The next part of this is to explore this sentence: "I wish to query this blank list by putting my part sizes in OD, ID and length, then search my blank table to bring by best blank and costs etc."

"To bring .. best blank and costs" requires that you have a decent method of manually deciding "best" before you could ever hope to automate it. So discuss what rules you believe would apply to achieve this optimum choice. How would you make this choice by hand? This is not a frivolous question - it is the first step in designing a proper query. If you can't answer this question, we won't be able to answer your question.

 

[J_oggy07]

Yes you are correct the blank is a tube Outer Diameter , Inner Diameter & length. The component has to be able to come from this blank, IE OD smaller than OD of blank, ID bigger than ID of blank and obviously length smaller than length of blank.


If you look in the attached data base you will see the table blank_lists to find correct blank, first have to match grade, then search down OD to pick best OD and then search ID to pick out best ID and then lastly length. Most blank sizes only have one length.

Hope this makes it clearer.

 

[mike60smart]

Yes you are correct the blank is a tube Outer Diameter , Inner Diameter & length. The component has to be able to come from this blank, IE OD smaller than OD of blank, ID bigger than ID of blank and obviously length smaller than length of blank.


If you look in the attached data base you will see the table blank_lists to find correct blank, first have to match grade, then search down OD to pick best OD and then search ID to pick out best ID and then lastly length. Most blank sizes only have one length.

Hope this makes it clearer.

Well where to start with so many things wrong.

1. Customer List should be named "tblCustomerList"
Your current PK named Customer is a text datatype and this should just be called CustomerNumber
All tables should have an Autonumber LongInteger Primary Key in this table it would be "CustomerID"
All spaces need to be removed from field names
Date is a reserved word and should not be used as a field name.

2. Quote Sheet should be named "tblQuotes"
This needs a PK named QuoteID
You are using a Lookup Field named "Name1" to link to the field "Name1" from "CustomerList"
Instead of using a Lookup this field should be a LongInteger - Number - Datatype and linked to the PK from "CustomerList"
The field named "Date" should be given a new name as "Date" is a Reserved word in Access.
Remove all spaces in field names

3. BlankList I believe this table needs to be normalised.
I take it the a specific Grade can have multiple specific Material Numbers associated ?
If this is the case then you need a table for the Specific Grades with a related table for GradePartNumbers

 

[J_oggy07]

Hello,
Thanks for your reply, I have made the corrections.
In the PricingForm I have entered some data in record one ideal blank I wish to populate field BlankMaterialCode is 10081614 from table tblBlankList, how do I do it

 

[mike60smart]

Hi
In the attached study how the tables are now related correctly.

 

[J_oggy07]

Hi,
Thanks for your reply.
I think you have misunderstood what I am trying to do. I wish to use the sizes on the form to return this blank in the field BlankMaterialCode from this I can get cost of blank and work out raw material cost. Field where you have put combo box is part number for component
Thanks again John.

 

[mike60smart]

Ok Which field from tblBlankList do you want to lookup?

 

[J_oggy07]

MaterialNumber based on grade OD , ID and length ideal blank for the sizes is

 

[J_oggy07]

Sorry, MaterialNumber based on grade OD , ID and length ideal blank for sizes in form is 10081614

 

[mike60smart]

MaterialNumber based on grade OD , ID and length ideal blank for the sizes is

OK so we select Material Number 10081614 and then do you want to populate BlankOD, BlankID & BlankLength
in the Quotes ??

 

[J_oggy07]

Exactly and price so I can work out raw material cost

 

[mike60smart]

Hi

See the amended db attached

 

[J_oggy07]

Hello,

Thanks, problem with this is you have to look down the long list of blanks and pick correct blank, prone to errors. I used VB in excel to do it, so I am sure this can be automated either using sql or vba but my knowledge is not good enough.
Thanks again for trying to help. John.

 

[mike60smart]

You don't need to look down a long List - you just type in the Material Number which will be autopupulated in the Combobox.

I don't believe there is another way but I could be proved wrong.

 

[J_oggy07]

OK, thanks again