Greyowlsl
Mlak Mlak
- Local time
- Today, 16:55
- Joined
- Oct 4, 2006
- Messages
- 204
Hi,
Sorry i wasn't sure where to put this thread...
I have a table that has rows/records of parts. It contains fields for description, part no., quantity etc. However it also has fields headings of product names that the parts are used in, in these fields there is a number, these numbers show how many of a part is used in that product.
I also have another table which lists every product sold, with a serial no. being the unique key. Here the product names are listed in the rows/records, not in the field headings. I have created a query from this table which shows each product uniquely and the total amount sold.
I have a form which is linked to the parts table, it has a combo box where the part is selected and then shows the details of that part in text boxes on the form; This also includes a box for each product, and how many of the part is used in it. There is also a text box showing the total amount ordered since the part was created.
So basically i need to get the total amount sold of each product multiplied by the the amount the selected part is used in the product, then have that sum taken away from the total amount of ordered of that part.
Part total amount - (amount of product sold X amount of part used in product) = Stock of part
The problem as you may have already guessed is that i can't get the rows from the products table to identify with the columns from the parts table.
My idea was to have a subform which lists each product and the amount sold of each, then do the calculations in code on the form... however i can't get individual text boxes showing the amounts of each product, since they can only be linked to fields. I've tried to select a specific product in the default value property but it doesn't work. All i can get is a combo or list box which when a product is selected it shows the amount sold in a text box.
What should i do?
I run access 97
Thanks for your time and help
Kind Regards,
Leon
Sorry i wasn't sure where to put this thread...
I have a table that has rows/records of parts. It contains fields for description, part no., quantity etc. However it also has fields headings of product names that the parts are used in, in these fields there is a number, these numbers show how many of a part is used in that product.
I also have another table which lists every product sold, with a serial no. being the unique key. Here the product names are listed in the rows/records, not in the field headings. I have created a query from this table which shows each product uniquely and the total amount sold.
I have a form which is linked to the parts table, it has a combo box where the part is selected and then shows the details of that part in text boxes on the form; This also includes a box for each product, and how many of the part is used in it. There is also a text box showing the total amount ordered since the part was created.
So basically i need to get the total amount sold of each product multiplied by the the amount the selected part is used in the product, then have that sum taken away from the total amount of ordered of that part.
Part total amount - (amount of product sold X amount of part used in product) = Stock of part
The problem as you may have already guessed is that i can't get the rows from the products table to identify with the columns from the parts table.
My idea was to have a subform which lists each product and the amount sold of each, then do the calculations in code on the form... however i can't get individual text boxes showing the amounts of each product, since they can only be linked to fields. I've tried to select a specific product in the default value property but it doesn't work. All i can get is a combo or list box which when a product is selected it shows the amount sold in a text box.
What should i do?
I run access 97
Thanks for your time and help
Kind Regards,
Leon
