Simple issue, Complicated scenario.

[Greyowlsl]

Hi guys,

Thought I'd try this again, in a different forum topic.

For reference an inverter is an electronic device used in solar systems.

First...
I have a form (PARTS FORM) with a combo box (PART) and a bunch of text boxes for each type of Inverter. When a part is selected, each inverter shows how many of that part is used in it. The form's source is a table that has a column for every inverter, and a row for each part.
This all works fine for the moment.

Next...
I have a query (INVERTERS SOLD), which has two columns, invertergroup by (inverters) and inverter count (amount sold).

Now...
What i need is that when i select a PART in the PARTS FORM, that the INVERTER text boxes on the PARTS FORM get multiplied by the corresponding INVERTER in the INVERTER SOLD query. This must be done for each inverter, and then added up (well call this TOTAL PARTS USED). Once added, the data needs to be subtracted off the TOTAL PARTS BOUGHT text box on the parts form.

So...
I was thinking this could be done in code somehow, but I'm not good with coding. The main problem is how to get a number in a text box to multiply with a number in a query based on what the text box is called.

Any kind of help would be great.

Thanks for your time.

Kind Regards,
Leon

 

[MarkK]

This is not how a database table works...

... a table that has a column for every inverter, and a row for each part.

What I strongly recommend is that you read up on the principles of 'normalization' in database design. Database tools take for granted that data are structure consistent with certain norms. You can do things other ways but then you defeat the power of a database and you might as well use Excel.
Cheers,
Mark

 

[Greyowlsl]

I understand, but i really can't change the design of these main tables. For reasons: Too much data, too many other databases linked to the table, not sure i would be authorized to spend days changing the database... as we are updating our whole database in around 5-6 months, with professional help... we even run access 97 at the moment.
I have a solution, its just inefficient.