Help

[connerlowen]

Hi,

I need help understanding something. My database will be creating quotes based on employees entries. Each quote will be unique because of the part. Each part has the potential to have Precious Metals, and Base Metals included. The employee must select the number Precious Metals and Base Metals that a particular part has. The problem lies with what happens after this selection is made. There are three fields that need to be defined pertaining to each metal. I need the number selected to determine how many fields to make available. If there are two precious metals, I need to make the three fields available twice; one for both metals. I have the possibility for up to 5 Precious Metals and 10 Base Metals per part. How can I make the selection from a combo box populate that number of Metals Fields?

Thanks,

Conner

 

[Ranman256]

I use a form so the user can pick items. Then after all items are picked, the user runs a macro to update the data by joining the 'picked' items to the base tables.

I'm sure you can work it for METALS.

 

[connerlowen]

I am not sure if that will work for what I need. I will look into it though. thank you

 

[Ranman256]

Yours would have 2 list boxes. 1 precious, 1 base.
Everytime a user added one, the other list can change or apply rules BEFORE it
adds it.

 

[BlueIshDan]

Is this a database design question or a form design.

I'm thinking both.

 

[connerlowen]

Maybe it is Both. I am honestly not sure how to accomplish this. That's what I need help with.

 

[BlueIshDan]

I'll be back tomorrow to check and see how you're making out. I'm too tired out to even think of a db design and front end solution to this right now. Time to go home in 10 minutes!

 

[connerlowen]

I look forward to hearing from you tomorrow. Thank you.

 

[spikepl]

When posting, use a title descriptive of your issue. It makes it much easier to find your post and for the supporters to see if they can contribute.

Check out the "Similar Threads" at the bottom of the page - really useful, unless poeple used a nondescriptive pointless title like Help or Access or something like that.

 

[connerlowen]

I honestly did not know how to phrase my problem in a title. Nothing I could come up with made any sense, or it was too long to be a title. Do you think I should post again under a different title?

 

[spikepl]

Your issue is "multiple selection". But don't bother with a new thread now - this one is too long for that. Just make the effort in the future, for the benefit of yourself and others.

 

[connerlowen]

Thank you for the advice

 

[BlueIshDan]

I don't know much about your field or project. But from what I've taken this far. The following db design should work and provide the possibility for an easy to use form design:

TABLE			FIELDS
-------------		-------------------------------------
[quotes]		id[, quote info]
[quote_parts]		id, quote_id, part_id, count
[quote_part_materials]	quote_part_id, material_id, count
[parts]	  		id[, spec fields]
[materials]		id[, spec fields]

 

[connerlowen]

BlueIshDan,

Thank you for your reply. My problem is how to start at the table level because of how complex these quotes can be, and how much information there is. I have a plan on what I want the database to do, I just don't know how to start and how many tables and fields I need and where they need to go.

 

[spikepl]

You are becoming a victim of paralysis by analysis.

Do not linger. Just do it. Get cracking. You'll probably have to redo your tables a few times, but it doesn't matter - you'll quickly develop judgment. Some will tell you you should design the entire structure first, but that advice fits only those who can see their entire system in advance.

Just know that renaming things - objects, fields and controls is a real PITA , which is why this part is worth doing properly from the outset.

Now do tables, play with some forms, redo your tables and forms, ask for advice when you have your structure set up a bit.

 

[connerlowen]

I have two questions.

1. When I have fields within a table that are based on other fields do I need separate tables or just one big table. For example a quote can be created based on part or on assembly. once part or assembly is determined there are more fields specific to that choice.

2. When I have fields that may or may not be used, depending on a value in another field, do I need to create a field for every possible outcome? For example, a quote may have many parts and within each part there may be many different raw materials (precious metals and base metals). If there is a possibility for 5 precious metals, do I need to go ahead and create 5 fields PM1, PM2, and so on?