I need to be able to produce a pivot table that will allow the results to be used in VB.
Background:
I am doing this in an Access 2000 project using a MSDE SQL server manager. I have two tables to focus on.
One is a raw material table that includes all the specific information for each lot of raw material. Example:
RMLot#, DateReceived, ExpirationDate, Status
The second table is a product table that includes multiple fields for lots of raw material used to make the product. Example:
ProductLot#, RM1, RM2, RM3, RM4, RM5
When the product information is entered (by form) I want to verify information on the materials used, for example experation dates are past, material status is approved, etc. I can do what I need if the second table is in the format of; ProductLot#, RM Lots Used; with each RM as a single record. I'm just not sure how to get to that point. A pivot table form arranges the information the way I need it, but the information is unusable for my needs.
Redesigning the tables is a possibility. The current design was chosen for ease of data entry and editing. Since projects only have views and not queries to enter and edit data, one table with both the product and raw materials used was needed. I am not quite confidant in my skills yet to rely on SQL for sources of forms and updating multiple tables from one form.
Any suggestions for performing these functions are appreciated.
Thanks
sTeven
Background:
I am doing this in an Access 2000 project using a MSDE SQL server manager. I have two tables to focus on.
One is a raw material table that includes all the specific information for each lot of raw material. Example:
RMLot#, DateReceived, ExpirationDate, Status
The second table is a product table that includes multiple fields for lots of raw material used to make the product. Example:
ProductLot#, RM1, RM2, RM3, RM4, RM5
When the product information is entered (by form) I want to verify information on the materials used, for example experation dates are past, material status is approved, etc. I can do what I need if the second table is in the format of; ProductLot#, RM Lots Used; with each RM as a single record. I'm just not sure how to get to that point. A pivot table form arranges the information the way I need it, but the information is unusable for my needs.
Redesigning the tables is a possibility. The current design was chosen for ease of data entry and editing. Since projects only have views and not queries to enter and edit data, one table with both the product and raw materials used was needed. I am not quite confidant in my skills yet to rely on SQL for sources of forms and updating multiple tables from one form.
Any suggestions for performing these functions are appreciated.
Thanks
sTeven
