User selection to exclude records not required when compiling product quote

[Macawac1]

I am trying to produce a quote where the user can select the supplier with the part pertinent to that particular quote.

The tables I have for this are:



tblProducts
- Product ID - PK
Customer, drawing reference etc..

tblProductBom
- ProductBOMID - PK
- ProductID - FK
- PartID - FK
Qty required etc..

tblParts
- PartID - PK
Manufacturers part details, min stock qty etc..

tblSuppliers
- SupplierPartID - PK
- PartID - FK
Supplier part details, price, discount etc..


One part can have only one manufacturer but many suppliers. I can retrieve all the possible suppliers for each part in the BOM but I cannot figure out how to allow the user to select which one they want to use for the quote. If it was purely based upon lowest line cost I could figure it out with a query using "min" (I think anyway!!) However I would like the user to have the ability to select which supplier they would like to use for this particular quote. It then needs to become a static record based upon the user's selection (haven't figured this bit out yet either - but am thinking I need to look at reports and save the final selection as such - or could I do all the complicated bit I can't figure out in a report anyway? I haven't really looked at reports yet!)

Apologies for what may seem like simple questions but I am still learning. My vision and wants on this seem to overtake my brain and thinking and learning capabilities now!

Regards,

Karen.

[CJ_London]

what does your form look like? I would expect a combobox listing the suppliers for that particular part

[Macawac1]

Hi Ajax,

At the moment I have a main form with the product details and a subform with all the supplier/manufacturer parts which does show the info I need - however I cannot figure out how to allow a selection and then transpose this into a static record of the selection made by the user. I have tried a Quote table with a yes/no field and incorporated this but it is too messy I think and open to error.

[CJ_London]

I really need to see the form, suggest upload a screenshot. And if it is not obvious, what is the form supposed to do and why does a user need to select a particular supplier? is it for an order for example? And if so how is your order systems work? e.g. order many parts from one supplier? order a single part from a supplier?

[Macawac1]

I am not sure the form I have is right to be honest but here goes:



The subform is really not the way I think it should be, it was my old idea of making a quote table, which needs to be updated, that then feeds the subform in order to give the selection option - but I don't think that is right at all.

However the screenshot does show what I am trying to do in a way:

Example: Parts 1, 2 and 9 have the option of being purchased from two different suppliers (simplistic - may be more with real data). Parts 15 and 20 can only be bought from one supplier.

I would like the user to be able to select the preferred supplier for parts 1, 2 and 9, leaving parts 15 and 20 as they are (as there are no further supplier options for those) and return this information as a static quote.

[orange]

Just saw this post.
You say you want the user to leave 15 and 20 alone because there is only 1 supplier for each Part.
Would it be easier for user(s)/less chance of changing things if --you only showed users those parts where there was more than 1 Supplier;
then verify they have selected 1 Supplier for each part with multiple possible Suppliers of each Part;
then piece together - the user selected data (Parts and Supplier) with the known required parts that are single Supplier only to create the quote for the ProductID.

Just a thought for consideration. I'm thinking of the logic and presentation (haven't considered How it could be done)

[CJ_London]

hmm, yes you do need to rethink the subform, although I suspect you need to show all that data because the user needs to see it to be able to make a decision. I presumed the subform recordsouce was a multi table source from your part number table for reference, quantity and a FK to link back to the product record, plus manufacturer and supplier tables and therefore not updateable. However I can see you can add new records, which makes me suspect your tables are not organised correctly.

However, assuming everthing in that area is OK then in principle to create your order, you need code to parse through the recordset and where a 'Select' is true, create an order

Something like this behind a 'create order' button

with me.subformname.form.recordset
    .movefirst
    while not .eof
        if !Select then 
            currentdb.execute "INSERT INTO tblOrderLines (OrderFK, PartFK, SupplierFK.....) VALUES(" & me.OrderPK & ", " & !ParkPK & ...... & ")"
        end if
        .movenext
    wend
end with

making use of conditional formatting you can work out some code which will highlight or disable those selections you do not want the user to make (i.e. where there is only one supplier) - use the dcount function, and if it returns 1, highlight or disable those records - and/or ignore the user selection.

That leaves those where the user has a choice, once they have made a selection, you want to disable the other choices, only reenabling them if the user deselects their choice.

[Macawac1]

Oh crikey - will need to look and think more tomorrow!

I could not work out how to gather the required information and have a selection field so my subform is based upon a table called Quote details that I was hoping would be populated from an append query that gathers the info I need and gives me the ability to have the supplier selection field. I haven't actually worked out yet how this will really work because I know I am on the wrong track with this. Apologies - I am a beginner! This table mirrors all the fields I already have which I know is so wrong but I could not see how else to do it. The quote itself has to be static - a snapshot of pricing, supplier choice etc... so I thought a table would record this. I just cannot figure it out at the moment!

Ajax, Yes the "user" will need to see all the options - in order to make a considered decision about the final result of the quote.

Orange, the "user" will need to see all the parts and suppliers - because the decision to choose one supplier over another may have further considerations - eg. delivery charges for qty, lead times per part - or even just loyalty to a particular supplier. (Haven't figured that one out yet but will come after I can work this one out).

I need to think more and look at your advice.

Kind regards,
Karen.

[orange]

For what its worth-- it's easier to work these "logic" issues out on paper than in a physical database.
You could use pencil and paper, some mock data and some "test scenarios" to work through.
It's much easier to erase a line or box or particular data flow and retest (again and again as necessary) than
to modify forms, subforms, controls, event procedures etc. etc.

Good luck with your project.

[Macawac1]

Ah bless you Orange,

I had reams of A4 covered in ineligible scrawl and one day my husband turned up with some huge A3 pads of paper - perfect! I think he only did it because he got fed up of me saying "Don't touch my papers!". The problem arises because I don't know enough yet and having found a structure that I thought followed the process flow and adhered to the normalisation rules I now think that maybe I need to start again! I have played with some practice ones but not through to the end because this is the one that I really wanted to do. I think I have the normalisation nailed (hey - but maybe not) but am struggling with pulling some of it together in the way I want to at the moment. It is rather like a little baby - you start to see it grow and grow and start feeling proud of it - but when you hit a sticky wicket it is very difficult to go back to the baby stage!

Thank you
Regards,
Karen.

[orange]

I recall a time at university (many years ago) in one of the comp science courses we were tasked with a database and logic assignment. A few of us worked together to deal with the basic set up and then DFD etc to get a conceptual/logical model. Then we went off in teams and developed the application (PL/1 as I remember) BUT the key point I wanted to make was that we had a number of blackboards(actually green) on a series of tracks so there may have been 3 deep and 3 or 4 wide (9 or 10 all together) that we used for the data model/data flow diagram. We could erase anything with 1 swoop of the brush --and we did. Ran some test cases through it and adjusted and retested till we were satisfied, then copied it to paper and away each group went to do the coding and development.
Still a very powerful method.

I'm attaching 2 links for your consideration:

1- Link to Info on Database Planning and Design.
2 - stump the model

I hope you find them useful.

A couple of phrases to help with focus. (these are just things you think about when someone is jumping the gun to get to a physical
database without normalization, analysis....)

If you don't have time now to do it right, what makes you think you'll have time to redo it later.

and a favourite (those trying to use their excel skills to design an access database)

I'm too busy chopping wood, to stop and sharpen the ax.

Good luck with your project

[Macawac1]

I remember having to tell a robot how to get up from a chair, walk round it and then sit back down again - this took about 30 different individual instructions - or so it seemed - maybe more it was many years ago now. That was sadly as far as I ever got!

Must tell my husband to bring me home some blackboards! Apologies for the flippancy - and I will look at your links - but tomorrow - very very tired now - I want to understand but the older I get the more impatient I seem to become.

Again thank you,

Karen.